You need to sign in to do that
Don't have an account?
Formula for State by Abbreviation
Hello,
I just made this formula and thought it could save someone in the future some time. Entering this into a formula field will give you the full state name based on abbreviation, just do a simple find and replace for "Field" in excel to edit the formula for your specific need.
if(Field='AL','Alabama',if(Field='AK','Alaska',if(Field='AZ','Arizona',if(Field='AR','Arkansas',if(Field='CA','California',if(Field='CO','Colorado',if(Field='CT','Connecticut',if(Field='DE','Delaware',if(Field='FL','Florida',if(Field='GA','Georgia',if(Field='HI','Hawaii',if(Field='ID','Idaho',if(Field='IL','Illinois',if(Field='IN','Indiana',if(Field='IA','Iowa',if(Field='KS','Kansas',if(Field='KY','Kentucky',if(Field='LA','Louisiana',if(Field='ME','Maine',if(Field='MD','Maryland',if(Field='MA','Massachusetts',if(Field='MI','Michigan',if(Field='MN','Minnesota',if(Field='MS','Mississippi',if(Field='MO','Missouri',if(Field='MT','Montana',if(Field='NE','Nebraska',if(Field='NV','Nevada',if(Field='NH','New Hampshire',if(Field='NJ','New Jersey',if(Field='NM','New Mexico',if(Field='NY','New York',if(Field='NC','North Carolina',if(Field='ND','North Dakota',if(Field='OH','Ohio',if(Field='OK','Oklahoma',if(Field='OR','Oregon',if(Field='PA','Pennsylvania',if(Field='RI','Rhode Island',if(Field='SC','South Carolina',if(Field='SD','South Dakota',if(Field='TN','Tennessee',if(Field='TX','Texas',if(Field='UT','Utah',if(Field='VT','Vermont',if(Field='VA','Virginia',if(Field='WA','Washington',if(Field='WV','West Virginia',if(Field='WI','Wisconsin',if(Field='WY','Wyoming','Other'))))))))))))))))))))))))))))))))))))))))))))))))))
Have a great day!
I just made this formula and thought it could save someone in the future some time. Entering this into a formula field will give you the full state name based on abbreviation, just do a simple find and replace for "Field" in excel to edit the formula for your specific need.
if(Field='AL','Alabama',if(Field='AK','Alaska',if(Field='AZ','Arizona',if(Field='AR','Arkansas',if(Field='CA','California',if(Field='CO','Colorado',if(Field='CT','Connecticut',if(Field='DE','Delaware',if(Field='FL','Florida',if(Field='GA','Georgia',if(Field='HI','Hawaii',if(Field='ID','Idaho',if(Field='IL','Illinois',if(Field='IN','Indiana',if(Field='IA','Iowa',if(Field='KS','Kansas',if(Field='KY','Kentucky',if(Field='LA','Louisiana',if(Field='ME','Maine',if(Field='MD','Maryland',if(Field='MA','Massachusetts',if(Field='MI','Michigan',if(Field='MN','Minnesota',if(Field='MS','Mississippi',if(Field='MO','Missouri',if(Field='MT','Montana',if(Field='NE','Nebraska',if(Field='NV','Nevada',if(Field='NH','New Hampshire',if(Field='NJ','New Jersey',if(Field='NM','New Mexico',if(Field='NY','New York',if(Field='NC','North Carolina',if(Field='ND','North Dakota',if(Field='OH','Ohio',if(Field='OK','Oklahoma',if(Field='OR','Oregon',if(Field='PA','Pennsylvania',if(Field='RI','Rhode Island',if(Field='SC','South Carolina',if(Field='SD','South Dakota',if(Field='TN','Tennessee',if(Field='TX','Texas',if(Field='UT','Utah',if(Field='VT','Vermont',if(Field='VA','Virginia',if(Field='WA','Washington',if(Field='WV','West Virginia',if(Field='WI','Wisconsin',if(Field='WY','Wyoming','Other'))))))))))))))))))))))))))))))))))))))))))))))))))
Have a great day!
CASE(Field,
'AL','Alabama',
'AK', 'Alaska',
..
'WY', 'Wyoming',
'Other')
Good call on that one, here is the new formula
CASE(Field,'AL','Alabama',
'AK','Alaska',
'AZ','Arizona',
'AR','Arkansas',
'CA','California',
'CO','Colorado',
'CT','Connecticut',
'DE','Delaware',
'FL','Florida',
'GA','Georgia',
'HI','Hawaii',
'ID','Idaho',
'IL','Illinois',
'IN','Indiana',
'IA','Iowa',
'KS','Kansas',
'KY','Kentucky',
'LA','Louisiana',
'ME','Maine',
'MD','Maryland',
'MA','Massachusetts',
'MI','Michigan',
'MN','Minnesota',
'MS','Mississippi',
'MO','Missouri',
'MT','Montana',
'NE','Nebraska',
'NV','Nevada',
'NH','New Hampshire',
'NJ','New Jersey',
'NM','New Mexico',
'NY','New York',
'NC','North Carolina',
'ND','North Dakota',
'OH','Ohio',
'OK','Oklahoma',
'OR','Oregon',
'PA','Pennsylvania',
'RI','Rhode Island',
'SC','South Carolina',
'SD','South Dakota',
'TN','Tennessee',
'TX','Texas',
'UT','Utah',
'VT','Vermont',
'VA','Virginia',
'WA','Washington',
'WV','West Virginia',
'WI','Wisconsin',
'WY','Wyoming',
'Other')
CASE(Field,'Alabama','AL',
'Alaska','AK',
'Arizona','AZ',
'Arkansas','AR',
'California','CA',
'Colorado','CO',
'Connecticut','CT',
'Delaware','DE',
'Florida','FL',
'Georgia','GA',
'Hawaii','HI',
'Idaho','ID',
'Illinois','IL',
'Indiana','IN',
'Iowa','IA',
'Kansas','KS',
'Kentucky','KY',
'Louisiana','LA',
'Maine','ME',
'Maryland','MD',
'Massachusetts','MA',
'Michigan','MI',
'Minnesota','MN',
'Mississippi','MS',
'Missouri','MO',
'Montana','MT',
'Nebraska','NE',
'Nevada','NV',
'New Hampshire','NH',
'New Jersey','NJ',
'New Mexico','NM',
'New York','NY',
'North Carolina','NC',
'North Dakota','ND',
'Ohio','OH',
'Oklahoma','OK',
'Oregon','OR',
'Pennsylvania','PA',
'Rhode Island','RI',
'South Carolina','SC',
'South Dakota','SD',
'Tennessee','TN',
'Texas','TX',
'Utah','UT',
'Vermont','VT',
'Virginia','VA',
'Washington','WA',
'West Virginia','WV',
'Wisconsin','WI',
'Wyoming','WY',
'Other')
Error: Syntax error. Missing ')'
Any thoughts?
Thanks!
Yes its within limits of the formula field.
I wanted to standarize both ways, I have two forumulas for Leads and two more for Account.
For State Abrevivation I check if its alerady 2 characters long, if so just UPPERcase it.
Otherwise I use the case forumla provided from above to check it, however i added a LOWER to make sure any change of capitilzation from States. "New york" "north Dakota" will be caught.
And then it defaults to "OTHER" if its not 2 charcters long, and not matching the state case.
IF(
LEN(State)==2,UPPER(State),
CASE(
LOWER(State),
'alabama','AL',
'alaska','AK',
'arizona','AZ',
'arkansas','AR',
'california','CA',
'colorado','CO',
'connecticut','CT',
'delaware','DE',
'florida','FL',
'georgia','GA',
'hawaii','HI',
'idaho','ID',
'Iillinois','IL',
'indiana','IN',
'iowa','IA',
'kansas','KS',
'kentucky','KY',
'louisiana','LA',
'maine','ME',
'maryland','MD',
'massachusetts','MA',
'michigan','MI',
'minnesota','MN',
'mississippi','MS',
'missouri','MO',
'montana','MT',
'nebraska','NE',
'nevada','NV',
'new hampshire','NH',
'new jersey','NJ',
'new mexico','NM',
'new york','NY',
'north carolina','NC',
'north dakota','ND',
'ohio','OH',
'oklahoma','OK',
'oregon','OR',
'pennsylvania','PA',
'rhode island','RI',
'south carolina','SC',
'south dakota','SD',
'tennessee','TN',
'texas','TX',
'utah','UT',
'vermont','VT',
'virginia','VA',
'washington','WA',
'west virginia','WV',
'wisconsin','WI',
'wyoming','WY',
'OTHER')
)
I also wanted it to go the other way. to check full name, but capitilzation caused the same issue. so its all UPPER. Default 'OTHER" if unknown.
I tried to call my Abbreviation Formula to map Full name formula, but that will cause them to run inside each other, increasing the size of forumla greater than allowed characterr limit. This formula I use to check if the state field is 2 characters long, then assume state abr and do a CASE mapping. wrapped with an UPPER to make the case all big. it will default to "OTHER" if it state is 2characters long and its unknown. (example another country province).
Otherwise if state is longer than 2 character, && state is not null && country == United States, then just print UPPER case of the state.
if its not United States then default 'OTHER'.
IF(LEN(State) == 2,
UPPER(
CASE( UPPER(State),
'AL','Alabama',
'AK','Alaska',
'AZ','Arizona',
'AR','Arkansas',
'CA','California',
'CO','Colorado',
'CT','Connecticut',
'DE','Delaware',
'FL','Florida',
'GA','Georgia',
'HI','Hawaii',
'ID','Idaho',
'IL','Illinois',
'IN','Indiana',
'IA','Iowa',
'KS','Kansas',
'KY','Kentucky',
'LA','Louisiana',
'ME','Maine',
'MD','Maryland',
'MA','Massachusetts',
'MI','Michigan',
'MN','Minnesota',
'MS','Mississippi',
'MO','Missouri',
'MT','Montana',
'NE','Nebraska',
'NV','Nevada',
'NH','New Hampshire',
'NJ','New Jersey',
'NM','New Mexico',
'NY','New York',
'NC','North Carolina',
'ND','North Dakota',
'OH','Ohio',
'OK','Oklahoma',
'OR','Oregon',
'PA','Pennsylvania',
'RI','Rhode Island',
'SC','South Carolina',
'SD','South Dakota',
'TN','Tennessee',
'TX','Texas',
'UT','Utah',
'VT','Vermont',
'VA','Virginia',
'WA','Washington',
'WV','West Virginia',
'WI','Wisconsin',
'WY','Wyoming',
'other'))
,UPPER(IF(State <> null && Country == 'United States',State,'OTHER'))
)
You will have to check the field input, if yours differs, and the "United States" if you put USA strinig in the database a differnt method. this will not catch other forms of United States: "USA" US", "usa" "united states"....
If the syntax error is there, check the 'State' field and 'Country'
‘Newfoundland and Labrador’,’NL’,
‘Prince Edward Island', ‘PE’,
‘Nova Scotia’, ‘NS’,
‘New Brunswick’,’NB’,
‘Quebec’,’QC’,
‘Ontario’,’ON’,
‘Manitoba’,’MB’,
‘Saskatchewan’,’SK’,
‘Alberta’,’AB’,
‘British Columbia’,’BC’,
‘Yukon’,’YT’,
‘Northwest Territories’,’NT’,
‘Nunavut’,’NU’,
OR
‘NL’,‘Newfoundland and Labrador’,
‘PE’,‘Prince Edward Island’,
‘NS’,‘Nova Scotia’,
‘NB’,‘New Brunswick’,
‘QC’,‘Quebec’,
‘ON’,‘Ontario’,
‘MB’,‘Manitoba’,
‘SK’,‘Saskatchewan’,
‘AB’,‘Alberta’,
‘BC’,‘British Columbia’,
‘YT’,‘Yukon’,
‘NT’,‘Northwest Territories’,
‘NU’,‘Nunavut’,