A SharePoint Calculated Column for all 50 States (workarounds for nested IF limits!)

Just in case you might ever need a formula to convert state abbreviations into state names…

• You will need 50 nested IFs,
• but SharePoint 2007 and 2010 only allows 7, and 2013 and later only allows 19.
• You will need a little more than 1300 characters in the formula,
• but SharePoint 2007 and 2010 only allow 1024. (2013 and later are around one billion!)

The trick for the IFs is to only nest 19 at a time and return a state name, or an empty string (""), and then concatenate another 19 nested IFs that return a state name, or an empty string… repeat until done! If you are using 2007 or 2010, then nest 7 at a time, and then concatenate another 7.

But what about the 2007 and 2010 1024 character limit? Renaming the "state" column to just one letter brings the formula down to 1111 characters, but that's still more than the 1024 allowed. Solution? Three Calculated columns. The first has the formulas for the first 25 states (in multiple IF nestings of 7 or less) that returns a state name or an empty string, The second has the next 25 states and returns a state name or an empty string. The third just concatenates the first two columns.

Here's the formula for SharePoint 2013 and later for a column named "State":

if(State="AL","Alabama",
if(State="AK","Alaska",
if(State="AZ","Arizona",
if(State="AR","Arkansas",
if(State="CA","California",
if(State="CO","Colorado",
if(State="CT","Connecticut",
if(State="DE","Delaware",
if(State="FL","Florida",
if(State="GA","Georgia",
if(State="HI","Hawaii",
if(State="ID","Idaho",
if(State="IL","Illinois",
if(State="IN","Indiana",
if(State="IA","Iowa",
if(State="KS","Kansas",
if(State="KY","Kentucky",
if(State="LA","Louisiana",
if(State="ME","Maine",""))))))))))))))))))) &
if(State="MD","Maryland",
if(State="MA","Massachusetts",
if(State="MI","Michigan",
if(State="MN","Minnesota",
if(State="MS","Mississippi",
if(State="MO","Missouri",
if(State="MT","Montana",
if(State="NE","Nebraska",
if(State="NV","Nevada",
if(State="NH","New Hampshire",
if(State="NJ","New Jersey",
if(State="NM","New Mexico",
if(State="NY","New York",
if(State="NC","North Carolina",
if(State="ND","North Dakota",
if(State="OH","Ohio",
if(State="OK","Oklahoma",
if(State="OR","Oregon",
if(State="PA","Pennsylvania",""))))))))))))))))))) &
if(State="RI","Rhode Island",
if(State="SC","South Carolina",
if(State="SD","South Dakota",
if(State="TN","Tennessee",
if(State="TX","Texas",
if(State="UT","Utah",
if(State="VT","Vermont",
if(State="VA","Virginia",
if(State="WA","Washington",
if(State="WV","West Virginia",
if(State="WI","Wisconsin","")))))))))))