How to Convert Full State Names to Abbreviations in Excel?


Have you ever tried to get the abbreviated form of any string in Excel? We can fill out the abbreviated form for the state names using the process mentioned in this article. If we need the values, they have to be on the same sheet. We can complete this process by using two methods: the first is by using the VLOOKUP function, and the other is by using the index function. In this tutorial, we will learn about how we can convert full state names to abbreviations in Excel.

Converting Full State Names to Abbreviations Using VLOOKUP

Here we will first create the list of all the state names along with abbreviations on the sheet, then use the VLOOKUP formula to get any one of the results, then use the auto-fill handle to get all the results. Let us see a simple process to know how we can convert a full-stack name to an abbreviation in Excel using the VLOOKUP function.

Step 1

Let us consider an Excel sheet that contains a list of state names along with their abbreviations, similar to the below image.

In the above sheet, A2:A30 and B2:B30 represent the names of states and their abbreviations, respectively. And the D2:D10 represents the values for which we need the abbreviation.

Now click on the cell E2 and enter the formula as =VLOOKUP(D2,$A$2:$B$30,2,0) and click enter to get our first result, as shown in the below image.

Step 2

Now drag down from the first result using the auto-fill handle, and our final result will look the one shown below.

Converting Abbreviations to Full State Names Using INDEX

Here we will first create the list of all the state names along with abbreviations on the sheet, then use the INDEX formula to get any one of the results, then use the auto-fill handle to get all the results. Let us look at a simple procedure for converting abbreviations to full state names in Excel using the index.

 

Step 1

Let us consider the same data that we used in the above example, and now to start our process, click on an empty cell and enter the formula as

=INDEX($A$2:$A$30,MATCH(D2,$B$2:$B$30,0)) and click Enter to get our first result.

Step 2

Now drag down from the first result using the auto-fill handle to get all results, as shown in the below image.

Conclusion

In this tutorial, we used a simple example to demonstrate how you can convert full state names to abbreviations in Excel.

Updated on: 08-Feb-2023

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements