+ Start a Discussion
Patrick Maxwell AppfolioPatrick Maxwell Appfolio 

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!
Eli Flores, SFDC DevEli Flores, SFDC Dev
This would be much cleaner and more efficient with the CASE formula. Try the below and you'll see the compiled size is much smaller

CASE(Field,
'AL','Alabama',
'AK', 'Alaska',
..
'WY', 'Wyoming',
'Other')
Patrick Maxwell AppfolioPatrick Maxwell Appfolio
Hi Eli, 

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')
nitin sharmanitin sharma
I know this is out of context.Howeever,case can be used to create bucket fields for standatd reports .As of now bucketing is allowed only for summary and matric report.But we can have it for stabndard reports using case ().
sfdcChi2sfdcChi2
thanks for making this availabe. very useful
Troy SearcyTroy Searcy
Here is the formula going in the opposite direction (Full State -> Abbreviation):

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')
Claire Lewis 1Claire Lewis 1
I copied this but got this syntax error: 
 Error: Syntax error. Missing ')'

Any thoughts?
Tabor LawsTabor Laws
Are you all putting this into a formula field? How are you getting around the character limit? 
Thanks!
Donny DavisDonny Davis

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'))
)

Donny DavisDonny Davis

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'