Go Back

How to Abbreviate State Names in Excel

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 to abbreviate state names in Excel

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 of VLOOKUP

=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 first 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 the 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.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Write a procedure that displays the state name and headquarters in a message box. States are one sheet listed in a column and headquarters are listed in separate sheets with the state names as the sheet names.
Solved by T. L. in 28 mins
I am dealing with some sales tax reports in which I base my calculations off the two letter state abbreviation. However, sometimes my report will spit out the full state name. Currently my column B is where my two letter state abbreviations and full state names appear. How do I tell Excel to convert the full state name into a two letter abbreviation? Do I need a new column? If so, can I make the state abbreviation copy over if it is correct? Would I need a reference table of all state names and abbreviations? I'm not sure if this is just formulas or a combo between formulas and vlookup. I'll upload an example file.
Solved by X. F. in 18 mins
I have a list with Names of some therapists, I want to count how many therapists belong in the same State.
Solved by Z. Y. in 19 mins

Leave a Comment

avatar