Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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:
Solution examples
I have a pivot table in which per order (on the rows) stands how much products they ordered per size (on the columns). I want to determine which combinations of quantities of sizes people order. And I want to count these combinations.
Solved by E. W. in 60 mins
I have 500 numbers in column A with aproximatly 15digits. In column B I have 5 numbers with 6 digits Can a find a formula wich can transfer all numbers from A column wich start with some 6 digitsa from B column
Solved by V. L. in 32 mins
I have a sheet with 3 columns. First column is a code for records in column B (has 563 records). What I need is a formula to show me if what is in column C (has 4400 records) is in column B and if it is to take the code for that record. Example: Column A(code corresponding to column B): 12, 14, 15, 19 Column B(names): asd, adf, ade, aqw Column C(names): akd, adf, ade, anb, wgs I need something that would say, if record in column C (for example adf) is matching record in column B (adf) return the code from column A (the code corresponding to that record in column B),if not return blank.
Solved by C. J. in 8 mins
If the data in A matches the data in C, then I need the data in E to be placed in B
Solved by X. W. in 40 mins
I have a workbook with 2 sheets. First sheet has column A which is a device name, then columns B - infinity that have numeric "tags" for that device. Each device can have one or 20 "tags", tags are all numerical. Worksheet 2 has column A which is numeric value and B which is a human readable value of what the tag is. Question is, how do I replace all the tag numbers in worksheet 1 with the actual human readable values is sheet 2?
Solved by T. H. in 16 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc