In Microsoft Excel : How can I convert a column of US State Names ( Texas ) to 2 Letter Codes ( TX)?

Question

Thanks in the advance for the help.

Answer 1

http://www.usps.com/ncsc/lookups/usps_abbreviations.html

Go to the above link and copy and paste the table into your data or calculations page. You could also create it manually if you prefer.

On your Output page, in the cell that you wish to have the state abbreviation listed, type the following function.

=VLOOKUP(E1,A1:C59,3,FALSE)

Where

E1 = The data input page. Where you enter the state name at
A1:C59 = the columns that you enter the data in (Note: There are three columns here because I pasted straight from the above website. Column A and B automatically merged. If you paste special>values you will have A1:B59)
3 = This is the Column that you wish to have the data collected from. The first column is 1. (Note: see above note regarding 3 columns)
False = This will pick exact matches only. If you use this formula and your array has numbers, Excel will extrapolate numbers that are not listed.

Your output page will automatically have the state listed correctly in it.

Answer 2

There is no simple feature for doing that (wish there was).

You could write a VB program with a "case" statement that would do it, but you have to be able to program....

The easier way would be to use the "autofilter" and filter it for one state (say Texas). Change the first entry to "Tx", and then copy and paste it down the filtered column.

Repeat for each of the other 49 states.

Answer 3

u can write a macro. you need to get a text file of 2 state codes. everything is alphabetical order for alignment.

but yah, if you can't program macros, then you'd have to do it manually.