Sourav Kundu
You might know how to abbreviate state names. This article will show you how you can Convert State Abbreviation to Name in Excel.
Download Practice Workbook
You can download and practice this workbook.
3 Ways to Convert State Abbreviation to Name in Excel
There are many ways to convert state abbreviations to names in Excel. Out of those many ways, we will show you three easy and convenient ways which include using IF, VLOOKUP functions, and the INDEX-MATCH formula. So, let’s begin.
1. Use of IF Function to Convert State Abbreviation to Name
This method is convenient if you have a dataset containing only 3 or 4 variables.
We have taken a dataset of 4 state names.
➤ Write the following formula in C5 and press ENTER.
=IF(B5="AL","Alabama",IF(B5="FL","Florida",IF(B5="CA","California",IF(B5="IL","Illinois",""))))
The IF function has three parts
Logical_test:- B5=”AL”; B5=”FL”; B5=”CA”; B5=”IL”
[Value_if_true]:-“Alabama”;”Florida”;”California”;”Illinois”
[Value_if_false]: IF(B5=”FL”,”Florida”,IF(B5=”CA”,”California”,IF(B5=”IL”,”Illinois”,””))
Depending on the logic output we will get the final result.
So, we have a full state name in C5.
➤ Now, Hold and Drag the C5 cell downward.
Thus, we get the full state name for every other cell.
Read More: How to Find 2 Letter State Abbreviations in Excel (4 Ways)
2. Use of VLOOKUP Function for Converting State Abbreviation to Name
We have taken a dataset of abbreviations and full names of different states. On the right side, in the abbreviation box E5, we want to insert a 2 letter abbreviation and get its full form in F5.
➤ First, insert any of the abbreviations in E5. We have taken TX (Texas).
➤ Now, write the following formula in F5 and press ENTER.
=VLOOKUP(E5,$B$5:$C$10,2,FALSE)
In this equation,
E5:- look_up_value
$B$5:$C$10:-table_array
2:- column_index_number
False:- Exact match(True for approximate match)
Matching the lookup value it will provide the result.
So, we get the full name from its abbreviated form.
➤ Now, change the state abbreviation and press ENTER.
And, we get the full name of that state.
Read More: How to Use VLOOKUP to Find State Abbreviations
3. Use of INDEX-MATCH Formula to Convert State Abbreviation to Name
We have the same dataset for the INDEX–MATCH formula. On the right side, in the abbreviation box E5, we want to insert a 2-letter abbreviated state and get its full form in F5.
➤ First, insert any of the state abbreviations in E5.
➤ Now, write the following formula in F5 and press ENTER.
=INDEX(C5:C10,MATCH(E5,B5:B10,0))
In this equation,
C5:C10:- array
E5:- lookup_value
B5:B10:-lookup_array
0:- exact_value
MATCH will check the corresponding data with respect to the searched value. The INDEX will provide the final output with the help of the initial result from MATCH.
So, we get the full state name which is California.
You can try different state abbreviations and get their full names.
Read More: How to Convert Abbreviations to Words in Excel (2 Easy Methods)
Practice Section
You can download the workbook and practice these methods yourself.
Conclusion
Thank you for making it this far. We hope you find these methods useful. If you have any queries or suggestions regarding these procedures, feel free to leave a comment.
