How can I match abbreviations in a cell to a list of words and return
their values if the text is various word lengths and I need to match
each word? Thanks.

The VLOOKUP function can do this. For example suppose you have your
abbreviations and their corresponding text values in cells C1:D20.
Then, you can use

=VLOOKUP(A1,C1:D20,2,FALSE)

This will look in C1:C20 for the value in A1 and, if found, returns
the corresponding value in D1:D20. If the abbreviation in A1 is not
found in C1:C20, the function will return an #N/A error. If you want
the formula to return an empty string (or something else) if the
abbreviation is not found, use something like

=IF(ISERROR(VLOOKUP(A1,C1:D20,2,FALSE)),"",VLOOKUP (A1,C1:D20,2,FALSE))

This is not terribly efficient because the VLOOKUP must be calculated
twice in most circumstances, first to test the result for an error and
again to get the value if it is not an error.

In Excel 2007, you can use the IFERROR function to cause the VLOOKUP
to be called only once:

=IFERROR( VLOOKUP(A1,C1:D20,2,FALSE),"")

IFERROR is new in Excel 2007 and cannot be used in Excel 2003 and
earlier versions.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 20 May 2009 11:45:39 -0700 (PDT), wrote:

How can I match abbreviations in a cell to a list of words and return
their values if the text is various word lengths and I need to match
each word? Thanks.

Post a sample of the abbreviations and the corresponding words.

But maybe a VLOOKUP formula would do the trick.

See help on VLOOKUP.

You will need a two column table of abbreviations and words.

Then a cell or range of cells as the lookup value.

Gord Dibben MS Excel MVP

On Wed, 20 May 2009 11:45:39 -0700 (PDT), wrote:

How can I match abbreviations in a cell to a list of words and return
their values if the text is various word lengths and I need to match
each word? Thanks.

On May 20, 4:43*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Post a sample of the abbreviations and the corresponding words.

But maybe a VLOOKUP formula would do the trick.

See help on VLOOKUP.

You will need a two column table of abbreviations and words.

Then a cell or range of cells as the lookup value.

Gord Dibben *MS Excel MVP



On Wed, 20 May 2009 11:45:39 -0700 (PDT), wrote:
How can I match abbreviations in a cell to a list of words and return
their values if the text is various word lengths and I need to match
each word? Thanks.- Hide quoted text -

- Show quoted text -

Thanks Gord. I am still having a problem because I have multiple words
in each cell and I want to keep the original surrounding text. I have
a description of 3/4 RD BASE NAIL GLIDE PLN that I need to make 3/4
ROUND BASE NAIL GLIDE PLAIN by having the cell refer to a list of
abbreviations, but as I said my problem is keeping the original text
intact while expanding the specific words. Is there any way to do
this?

How many of these would there be?

If it's a one-off you could use editreplace.

I'm not good enough with formulas to build you something that would replace
multiple abbreviations in a cell from a lookup list of abrreviations and
words.

Hopefully someone has an idea for you.


Gord

On Thu, 21 May 2009 06:43:41 -0700 (PDT), wrote:

Thanks Gord. I am still having a problem because I have multiple words
in each cell and I want to keep the original surrounding text. I have
a description of 3/4 RD BASE NAIL GLIDE PLN that I need to make 3/4
ROUND BASE NAIL GLIDE PLAIN by having the cell refer to a list of
abbreviations, but as I said my problem is keeping the original text
intact while expanding the specific words. Is there any way to do
this?