0

Is there a MS Excel add-on or something that will Change U.S. State Abbreviation to Full State Name?

Change:

GA to Georgia
CA to California
FL to Florida

This is brutal doing Find and Replace because:

Find: ID
Replace: Idaho 

Which then changes Florida to Floridahoa.

So i'm constantly going back and forth fixing that mess...

Thanks

8
  • 1
    What version of Excel? In my Find and Replace menu, I can check Options -> "Match entire cell contents" and then it only will replace an exact match to what you typed.
    – Sam Forbis
    Commented Apr 20, 2020 at 15:20
  • OMG You just saved my day! Ty man. I had no idea...
    – Steve
    Commented Apr 20, 2020 at 15:22
  • 1
    No problem! I wish there was an easier way than to have to go through that for every state. There still may be a better answer that automatically converts the abbreviation to the full name but I am unaware of it.
    – Sam Forbis
    Commented Apr 20, 2020 at 15:24
  • 3
    On second thought, you could also create another "lookup" table that pulls the state's full name from one column where its abbreviation is in another. This would take some time, but may take just about the same amount of time as replacing each name manually... Something like this extendoffice.com/documents/excel/…
    – Sam Forbis
    Commented Apr 20, 2020 at 15:26
  • 1
    The first thing I thought was to create a table on a separate worksheet and setup a VLOOKUP referencing this table.
    – BradR
    Commented Apr 20, 2020 at 16:01

2 Answers 2

0

Go to DATA and then select Geography. I then select the cells, go to DATA and Convert to Text otherwise it does a lookup constantly.

It's not perfect but will get most of them done. I've had issues where it converts IL to Israel and MO to Moldova, go figure.

1
  • 1
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Jun 1, 2023 at 21:45
0

You can do an IF formula (messy but effective) to do the replace.

Where column B contains the abbreviated state ID, use this in column C (or some other column):

=IF(B2="AL","Alabama",IF(B2="AK","Alaska",IF(B2="AZ","Arizona",IF(B2="AR","Arkansas",IF(B2="AS","American Samoa",IF(B2="CA","California",IF(B2="CO","Colorado",IF(B2="CT","Connecticut",IF(B2="DE","Delaware",IF(B2="DC","District of Columbia",IF(B2="FL","Florida",IF(B2="GA","Georgia",IF(B2="GU","Guam",IF(B2="HI","Hawaii",IF(B2="ID","Idaho",IF(B2="IL","Illinois",IF(B2="IN","Indiana",IF(B2="IA","Iowa",IF(B2="KS","Kansas",IF(B2="KY","Kentucky",IF(B2="LA","Louisiana",IF(B2="ME","Maine",IF(B2="MD","Maryland",IF(B2="MA","Massachusetts",IF(B2="MI","Michigan",IF(B2="MN","Minnesota",IF(B2="MS","Mississippi",IF(B2="MO","Missouri",IF(B2="MT","Montana",IF(B2="NE","Nebraska",IF(B2="NV","Nevada",IF(B2="NH","New Hampshire",IF(B2="NJ","New Jersey",IF(B2="NM","New Mexico",IF(B2="NY","New York",IF(B2="NC","North Carolina",IF(B2="ND","North Dakota",IF(B2="MP","Northern Mariana Islands",IF(B2="OH","Ohio",IF(B2="OK","Oklahoma",IF(B2="OR","Oregon",IF(B2="PA","Pennsylvania",IF(B2="PR","Puerto Rico",IF(B2="RI","Rhode Island",IF(B2="SC","South Carolina",IF(B2="SD","South Dakota",IF(B2="TN","Tennessee",IF(B2="TX","Texas",IF(B2="TT","Trust Territories",IF(B2="UT","Utah",IF(B2="VT","Vermont",IF(B2="VA","Virginia",IF(B2="VI","Virgin Islands",IF(B2="WA","Washington",IF(B2="WV","West Virginia",IF(B2="WI","Wisconsin",IF(B2="WY","Wyoming")))))))))))))))))))))))))))))))))))))))))))))))))))))))))

This will simply run through each state using an IF until it reaches the match and then replace it with the name.

If you want it in a shorter formula you can create a new sheet/table for the State IDs and Name index:

AL  Alabama
AL  Alabama
AK  Alaska
AZ  Arizona
AR  Arkansas
AS  American Samoa
CA  California
CO  Colorado
CT  Connecticut
DE  Delaware
DC  District of Columbia
FL  Florida
GA  Georgia
GU  Guam
HI  Hawaii
ID  Idaho
IL  Illinois
IN  Indiana
IA  Iowa
KS  Kansas
KY  Kentucky
LA  Louisiana
ME  Maine
MD  Maryland
MA  Massachusetts
MI  Michigan
MN  Minnesota
MS  Mississippi
MO  Missouri
MT  Montana
NE  Nebraska
NV  Nevada
NH  New Hampshire
NJ  New Jersey
NM  New Mexico
NY  New York
NC  North Carolina
ND  North Dakota
MP  Northern Mariana Islands
OH  Ohio
OK  Oklahoma
OR  Oregon
PA  Pennsylvania
PR  Puerto Rico
RI  Rhode Island
SC  South Carolina
SD  South Dakota
TN  Tennessee
TX  Texas
TT  Trust Territories
UT  Utah
VT  Vermont
VA  Virginia
VI  Virgin Islands
WA  Washington
WV  West Virginia
WI  Wisconsin
WY  Wyoming

And then use a VLOOKUP to get the result (where the state index is in columns F:G):

=VLOOKUP(B2,$F:$G,2,FALSE)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .