How to Convert Full State Names to Abbreviations or Vice Versa in Excel

When working with addresses, customer records, shipping lists, or geographic data in Excel, you may need to convert full U.S. state names such as California into abbreviations such as CA. You may also need to perform the reverse conversion.

Excel formulas can handle these conversions, but they require a complete reference table containing every state name and abbreviation. If you want a faster solution without creating formulas or maintaining a lookup table, Kutools for Excel provides a dedicated conversion tool.

• Method Comparison
• Convert state names to abbreviations or vice versa with VLOOKUP
• Convert in either direction directly with Kutools for Excel
• FAQ
• Conclusion

Comparison of the methods

VLOOKUP is suitable when you want formula-based results that update automatically after the source data changes. Kutools is more convenient when you need static results and do not want to create or maintain a state reference table.

Convert full state names to abbreviations or vice versa with VLOOKUP

The VLOOKUP function can convert full state names to their corresponding abbreviations or convert abbreviations back to full state names. Because VLOOKUP searches the first column of a reference table and returns a value from a column to its right, the column order of the reference table must match the direction of the conversion.

Step 1: Choose and create the appropriate reference table

Choose one of the following reference tables based on the conversion you want to perform. You can copy and paste the appropriate table into a separate worksheet named State Reference.

• To convert full state names to abbreviations, use the State Name | Abbrev table.
• To convert abbreviations to full state names, use the Abbrev | State Name table.

Step 2: Apply the VLOOKUP formula

After pasting the selected reference table into cells A1:B51 of the State Reference worksheet, follow these steps:

1. Enter the following formula in a blank cell next to the first value you want to convert:

   =VLOOKUP(C2,'State Reference'!$A$1:$B$51,2,FALSE)

2. Press Enter, and then drag the fill handle down to apply the formula to the remaining cells.

The formula searches for the value in C2 in the first column of the selected reference table and returns the corresponding value from its second column.

Formula explanation

• C2 is the cell containing the full state name or abbreviation you want to convert.
• 'State Reference'!$A$1:$B$51 is the reference table on the State Reference worksheet.
• 2 returns the matching value from the second column of the reference table.
• FALSE requires an exact match.

Convert state names and abbreviations directly with Kutools for Excel

If you frequently work with address or geographic data, creating reference tables and formulas for state name conversion can be inconvenient. With the Convert State Names and Abbreviations feature in Kutools for Excel, you can convert full state or province names to abbreviations—or convert abbreviations back to full names—without formulas or a separate reference table.

This feature supports U.S. states as well as Canadian provinces and territories.

Step 1: Select the cells you want to convert

Select the cells containing the full state or province names—or abbreviations—that you want to convert.

Step 2: Open the Kutools conversion tool

Click Kutools > Content > Convert State Names and Abbreviations to open the Convert State/Province Names and Abbreviations dialog box.

Step 3: Choose the conversion settings

In the Convert State/Province Names and Abbreviations dialog box:

1. Under Operation, select the required conversion direction:
   • Select Convert State/Province Names to Abbreviations to convert full names to abbreviations.
   • Select Convert State/Province Abbreviations to Full Names to convert abbreviations to full names.
2. Under Country, select United States or Canada, depending on your source data.
3. Check the Preview pane to confirm that the source data will be converted correctly.
4. Click OK to perform the conversion.

The selected cells are immediately converted based on the specified direction and country.

Frequently Asked Questions

Conclusion

VLOOKUP can convert full state names to abbreviations or convert abbreviations back to full names. However, it requires a complete reference table arranged in the correct column order for the desired conversion direction.

For a faster and more direct solution, the Convert State Names and Abbreviations feature in Kutools for Excel converts U.S. state and Canadian province or territory data in either direction without formulas or a reference table.