How to convert full state names to abbreviations in Excel?
If you have a column of full state names need to be converted to their corresponding abbreviations in the next to column as following screenshot shown, how could you solve this problem quickly and easily in Excel?
Convert full state names to abbreviations:
Kutools for Excel's Look for a value in list utility can help you to vlookup and return the corresponding abbreviations based on the full state names.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
There is no direct way for you to finish this job in Excel, but, you can list all of the state names and their relative abbreviations firstly, and then apply the Vlookup function to get any of the abbreviations.
First, please copy and paste the left state names and their abbreviations to your worksheet where you want to use.
Supposing, I paste them in cell A1:B51, and my data range in E1:F22, see screenshot:
Then enter this formula: =VLOOKUP(E2,$A$2:$B$51,2,0) into cell F2, see screenshot:
Note: In the above formula, E2 is the state name which you want to return its relative abbreviation, A2:B51 is the data range you want to look for, the number 2 indicates the column number that your matched value is returned.
At last, drag the fill handle down to the cells that you want to get the relative abbreviations, see screenshot:
Tips: If you usually use above state names and abbreviations, you can save them into the Auto Text pane of Kutools for Excel to reuse and insert them into anywhere of the workbook or worksheet anytime. Click to Download and free trial Kutools for Excel Now !
If you have Kutools for Excel, with its Look for a value in list utility, you can also solve this job as soon as possible, please do as follows:
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
After installing Kutools for Excel, please do as follows:
1. After copy and paste all the full state names and their corresponding abbreviation next to your data range, click a cell where you want to put the abbreviation result, cell F2, in this example.
2. Then click Kutools > Formulas > Look for a value in list, see screenshot:
3. In the Formula Helper dialog box:
(1.) Click button beside the Table_array to select the data range that you want to lookup from, and input the $ character to the cell reference to make the relative reference to absolute reference;
(2.) Click button beside the Look_value to select the looked up value you need;
(3.) Click button beside the Column to select the column will be returned the matching values.
4. And then click Ok button, you will get the first result, and then drag the fill handle down to the cells that you want the abbreviations, see screenshot:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 7 months agoIs it possible to do this conversion if there is a city listed beforehand? For example, (Richmond, Virginia) into (Richmond, VA) ?
- To post as a guest, your comment is unpublished.· 3 years agoThanks for the explanation, it was useful :)
- To post as a guest, your comment is unpublished.· 3 years agoHow to convert full state names to abbreviations in Excel?