Excel: How to Convert State Name to Abbreviation


You can use the following formula to convert state names to abbreviations in Excel:

=SWITCH(A2, "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", "MI", "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")

This particular formula converts the state name in cell A2 to the state abbreviation.

The following example shows how to use this function in practice.

Related: How to Convert State Abbreviation to Full Name in Excel

Example: Convert State Name to Abbreviation in Excel

Suppose we have the following list of state names in Excel:

Suppose we would like to display the abbreviation for each state name in column B.

To do so, we can type the following formula into cell B2:

=SWITCH(A2, "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", "MI", "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")

We can then click and drag this formula down to each remaining cell in column B:

Excel convert state name to abbreviation

Column B now displays the state abbreviation for each state name in column A.

How This Formula Works

Recall the formula that we used to display the full state name for the state abbreviation:

=SWITCH(A2, "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", "MI", "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")

This formula uses the SWITCH function, which uses the following basic syntax:

SWITCH(value to switch, find1, return1, find2, return2, ...)

In our particular formula, we analyze cell A2 and do the following:

  • Look for “Alabama” – if found then return AL
  • If not found, then look for “Alaska” – if found then return AK
  • If not found, then look for “Arizona” – if found then return AZ

And so on.

Using this logic, we’re able to return an abbreviation based on the full state name.

Note: You can find the complete documentation for the SWITCH function in Excel here.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: Find First Occurrence of a Value in Column
Excel: How to Assign Number Value to Text
Excel: How to Convert Month Name to Number

2 Replies to “Excel: How to Convert State Name to Abbreviation”

  1. Hey, just a heads up, in “excel-convert-state-to-abbreviation”, Mississippi is incorrectly mapped to MI instead of MS. Found it trying to troubleshoot some lookup data I had.

  2. Thank you! I used this today and it was helpful.
    Just one issue I noticed, Mississippi you marked as MI which is Michigan’s. Michigan is correct at MI, but Mississippi needs to change.

    This saved me a decent chunk of time, I was just thankful I also had Michigan in my dataset otherwise I would’ve missed that.

Leave a Reply

Your email address will not be published. Required fields are marked *