How can I convert a state abbreviation to its full name in Excel?

By /

This tutorial will guide you through the process of converting a state abbreviation to its full name in Microsoft Excel. By following these steps, you will be able to easily and accurately transform your data into the desired format. This feature is useful for organizing and presenting information in a professional and organized manner. By utilizing this method, you will save time and effort in manually typing out state names, making your Excel spreadsheets more efficient and reliable.

Excel: Convert State Abbreviation to Full Name

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

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

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

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

Example: Convert State Abbreviation to Full Name in Excel

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

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

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

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

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

Excel convert state abbreviation to full name

Column B now displays the full state name for each state abbreviation 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, "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")

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 “AL” – if found then return Alabama
  • If not found, then look for “AK” – if found then return Alaska
  • If not found, then look for “AZ” – if found then return Arizona

And so on.

By using this logic, we’re able to return a full state name based on each state abbreviation.

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

Additional Resources

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

x

Related terms:

  1. How can I convert a state name to its corresponding abbreviation in Excel?
  2. Excel: Convert State Name to Abbreviation
  3. How can I convert a month name to its corresponding number in Excel?
  4. How can I extract the filename from a full path in Excel?
  5. DREAM STATE (D-STATE)
  6. How to extract filename from full path in Excel?
  7. How to Extract Last Name from Full Name in Excel?
  8. How can I display the full content of a column in PySpark?
  9. FULL INCLUSION
  10. What is the process for displaying full column content in PySpark?