Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replace a long list of abbreviations with full words in Excel
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replace a long list of abbreviations with full words in Excel
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. "jgundel" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replace a long list of abbreviations with full words in Excel
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. CopyPaste SpecialValues the results over the data in column A and delete the helper column. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=509458 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replace a long list of abbreviations with full words in Excel
that's it. Thanks.
"SteveG" wrote: 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. CopyPaste SpecialValues the results over the data in column A and delete the helper column. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=509458 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you display numbers as words in excel (eg. 10 as ten)? | Excel Discussion (Misc queries) | |||
Get excel to list values that occur within raw data | Excel Discussion (Misc queries) | |||
more than 3 conditions in conditional formatting - possible? | Excel Discussion (Misc queries) | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |