Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Convert State Abbreviation to Name in Excel (3 Easy Ways)

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.

How to Convert State Abbreviation to Name in Excel

➤ 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.

Convert State Abbreviation to Name in Excel

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.

Convert State Abbreviation to Name in Excel(Vlookup)

➤ 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.

Convert State Abbreviation to Name in Excel(Vlookup)
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.

Convert State Abbreviation to Name in Excel(Vlookup)

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 INDEXMATCH 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.

Convert State Abbreviation to Name in Excel(INDEX-MATCH)

➤ 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.

Convert State Abbreviation to Name in Excel(INDEX-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.

Practice Section


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.


Related Articles

Sourav Kundu

Sourav Kundu

Bio: Hi, I am Sourav Kundu. I live in Adabor, Dhaka. I graduated in Naval Architecture and Marine Engineering from BUET. I am really excited to be a part of SOFTEKO family. I want to develop my research skill and find innovative solutions for the given problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo