Partial Words Lookup and Replace

[dwgnome]

Looking for a better way to substitute correct words for incomplete spellings in a list. The following works, but if a word is not exactly like I have in the lookup, the replacement fails. In the next column over from C34 I have:

=CHOOSE(MATCH(C34,{"RESIN-","STRIPP","SEALAN","CLEANE","SOLVEN","ALCOHO","COMPOU","CATALY","ADHESI"},0),"RESIN","STRIPPER","SEALANT","CLEANER","SOLVENT","ALCOHOL","COMPOUND","CATALYST","ADHESIVE")

This fails when the word in C34 is RES or RESI, STR or STRIP.


I tried the following formula in D34 but just get #N/A:

=INDEX(WordList,MATCH(TRUE,ISNUMBER(SEARCH(WordList,"*"&C34&"*")),0))

Where WordList is a list of the correct spelled words. Also, if it is possible if there are other words in the column that is not in the WordList, then I want those words to pass through unchanged. I think I'm on the right track but can't quite get the results. Please help.

 

[Marcelo Branco]

Maybe

=INDEX(WordList,MATCH("*"&C34&"*",WordList,0))

M.

 

[Marcelo Branco]

oops

Hadn't seen this

Where WordList is a list of the correct spelled words. Also, if it is possible if there are other words in the column that is not in the WordList, then I want those words to pass through unchanged

Try (Excel 2007 or higher)
=IFERROR(INDEX(WordList,MATCH("*"&C34&"*",WordList,0)),C34)

M.

 

[dwgnome]

Marcelo,

Thanks for your solution. It worked. I use Excel 2003, so I just used the IF(ISERROR(formula),C34,formula) construct. The only problem I have is that RESIN- is so similar to the word RESIN in the WordList, but because of the extra character at the end it does change to RESIN. I could work around that by looking only at the first 3 characters to do the match. I tried the following but it doesn't work:

=INDEX(WordList,MATCH(LEFT("*"&C34&"*",3),WordList,0))

It just gives #N/A.

Would like to see if my idea would work but need help in refining the formula.

 

[Marcelo Branco]

Hi,

You can use SUBSTITUTE(C34,"-","")

Also i would like to suggest a different construct for Excel 2003 instead of
IF(ISERROR(formula),C34,formula)
that, i think, is more efficient.

Try this
=LOOKUP(REPT("z",255),CHOOSE({1;2},C34,INDEX(Wordlist,MATCH("*"&SUBSTITUTE(C34,"-","")&"*",Wordlist,0))))

M.

 

[dwgnome]

M,

Many thanks! Your solution is perfect and elegant.

I too do not like the ISERROR approach, I have seen and used the CHOOSE function before and I like it.

I added the TRIM function to the SUBSTITUTE portion to make it a bit more robust to allow spaces before and after the "-", or if there are spaces in front of the word.

=LOOKUP(REPT("z",255),CHOOSE({1;2},C35,INDEX(WordList,MATCH("*"&TRIM(SUBSTITUTE(C35,"-",""))&"*",WordList,0))))

Not sure how I can substitute/replace more than one symbol for instance if the original word had -RES+, instead of RESIN, the result is -RES+

Thank you again.