Abbreviate state names

Many times, we need to write abbreviations of names, places, and things. This can be done easily by using inbuilt functions of Excel. In order to determine two-letter abbreviations from full state names (i.e. Los Angeles to LA), you can use a simple formula using VLOOKUP.

VLOOKUP function

VLOOKUP is an excel function that is used to look up and retrieve data from a specific lookup column in a table. It is also categorized as a lookup/reference function.

Syntax

=VLOOKUP(value, table,index number,[range_lookup])

In this case, there is no direct way for you to finish this job in Excel. But, you can list all the state names and their relative abbreviations firstly in a table and then apply the VLOOKUP function to get any of the required abbreviations.

In this case, we’ll use the formula stated below:

Generic formula

=VLOOKUP(name,states,2,0)

Example

In the example shown, we create a lookup table with two columns where we list all the names of the states in one column and their relative abbreviations in another. Then, we create another table consisting of three columns where we mention state name in one column, their population in second and in the third column, we’ll get their abbreviation displayed.

We apply the formula in E5

=VLOOKUP(C5,states,2,0)

Here, ‘states’ is a named range/lookup table G5:H11

Figure 1: Illustration of VLOOKUP formula

Once we apply this formula, we’ll get the abbreviation NW for New York in E5. Similarly, on dragging the formula to other cells, we’ll get the abbreviation of other states as shown.

Figure 2: VLOOKUP for abbreviating state name

VLOOKUP will locate the matching entry in the “states” table and in return, it will give the corresponding two-letter abbreviations in column E.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar