Changing State Abbreviations To Full Names in Excel

To change state abbreviations to their full names in Excel, you can use a combination of functions such as VLOOKUP or XLOOKUP to reference a lookup table containing state abbreviations and their corresponding full names.

Steps:

1. Create a Lookup Table:

• In a new sheet or next to your data, create a table with state abbreviations in one column and full state names in another.

   A       B
   ---     -------------------
   AL      Alabama
   AK      Alaska
   AZ      Arizona
   AR      Arkansas
   CA      California
   CO      Colorado
   CT      Connecticut
   DE      Delaware
   FL      Florida
   GA      Georgia
   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
   OH      Ohio
   OK      Oklahoma
   OR      Oregon
   PA      Pennsylvania
   RI      Rhode Island
   SC      South Carolina
   SD      South Dakota
   TN      Tennessee
   TX      Texas
   UT      Utah
   VT      Vermont
   VA      Virginia
   WA      Washington
   WV      West Virginia
   WI      Wisconsin
   WY      Wyoming

2. Use VLOOKUP Function:

• Assuming your state abbreviations are in column A starting from A2 and the lookup table is in the range D1:E51, you can use the VLOOKUP function to replace abbreviations with full names.

   A       B
   ---     -------------------
   AL      =VLOOKUP(A2, $D$1:$E$51, 2, FALSE)

3. Drag the Formula:

• Drag the formula down to apply it to other cells in column B.

Live Example:

Assume the state abbreviations are in column A starting from A2, and your lookup table is in columns D and E starting from D1 to E51.

1. Input Data and Lookup Table:

   Data:
   A       B
   ---     ---
   AL
   CA
   NY

   Lookup Table:
   D       E
   ---     -------------------
   AL      Alabama
   CA      California
   NY      New York
   ...     ...

2. Formula in Column B:

• In cell B2, enter the formula: =VLOOKUP(A2, $D$1:$E$51, 2, FALSE)
• Drag the formula down to cover the cells with state abbreviations.

2. Result:

   A       B
   ---     -------------------
   AL      Alabama
   CA      California
   NY      New York

Using XLOOKUP (Excel 2019 and Later)

If you have Excel 2019 or later, you can use the XLOOKUP function, which is more flexible and straightforward.

1. Formula in Column B:

• In cell B2, enter the formula: =XLOOKUP(A2, $D$1:$D$51, $E$1:$E$51, "Not Found")
• Drag the formula down to cover the cells with state abbreviations.

1. Result:

   A       B
   ---     -------------------
   AL      Alabama
   CA      California
   NY      New York

By using these methods, you can efficiently convert state abbreviations to their full names in Excel.