Replace a long list of abbreviations with full words in Excel

J

jgundel

I am trying to replace about 1000 unique abreviations in about 4000 cells in
Excel with their full word equivalent. I have a table of abbreviations and
their longer versions. I want to find/replace without have to do 1000+
individual find/replace functions. The table looks like this.

LONG_NAME SHORT_NAME
VANILLA VAN
CITRUS CTRS
LAVENDER LVNDR
POTPOURRI POTPR
APPLE CINNAMON APLCN
ROSE RSE

How do I do a mass find/replace based on this table?

Thank you!
 
R

Richard O. Neville

This should not be too difficult with the Edit-Replace function, but you can
do only one replacement at a time. In "Find what" type VAN; in "Replace
with" type VANILLA. Click Find Next, then Replace. If it works on two or
three cells, click Replace All. Then repeat this for each of the changes. If
you have trouble, click "Options" on the Replace menu for more parameters.
Also, you may have to widen some of the columns.
 
S

SteveG

If your list of abbreviations are in A2:A1000 and your reference table
is Short name column I and Long name in column J then insert a helper
column so in B2,

=VLOOKUP(A2,I2:J7,2,FALSE)

Copy the formula down your list. Copy>Paste Special>Values the results
over the data in column A and delete the helper column.

HTH

Steve
 
Top