Find Exact Match Text String Using Formula

[sweetrevelation]

My question
In the attached spreadsheet, I have highlighted an example that illustrates my problem. "Bow" windows are a type of window. However, keywords that contain the text string "bowie" (which is a city in maryland) are being labeled as "Bow".

How do I modify my formula to look up EXACT MATCH text strings only?

Keyword Labeling Formula.xlsx

I have this crazy formula that looks for values in a list of keywords. Where it finds a given value, it will label it by assigning the header label to the keyword. If no value is found, it calls the label "Uncategorized".

Please Login or Register  to view this content.

I did not come up with this awesome formula, user NBVC did here:
http://www.excelforum.com/excel-form...57#post2914657

[tigeravatar]

sweetrevelation,

Surround the text you're searching, and the text you're searching for, with spaces within the formula, like so:

Please Login or Register  to view this content.

[sweetrevelation]

Thanks tigeravatar.

I'm trying to understand how this formula works. The questions I have are:

1. What does this part of the formula do? "||" It's just a text string with two pipe characters. Does that have a special meaning in Excel or is that literally just a text string with two pipe symbols?
2. I see your modification was to add empty spaces " " at the beginning and ending. How does your formula work for keywords that begin with or end with the text strings? For example, if the keyword was "replacement windows michigan", doesn't your formula look for " "replacement windows" " and therefore, since "replacement windows michigan" BEGINS with "replacement windows" and not " "replacement windows" " (with the space in front of this) how does your modification to the formula work?

Thanks for any clarification you can provide. I hate bothering people for help but your help in understanding is so much appreciated!

[tigeravatar]

Yes, its just 2 pipes. That is so that the formula cannot accidentally find a keword that includes a pipe symbol (if you had any) since there's no reason to have a keyword with 2 consecutive pipes. a keyword with 1 pipe is plausible, so by itself it won't do.

As for the spaces, in the formula, it adds spaces around the keywords:
" "&$E$2:$AG$100&" "

And then it also adds spaces around the text being searched:
" "&A10&" "

So the text "replacement windows michigan" becomes " replacement windows michigan " so that it can find " replacement windows ".

[sweetrevelation]

Yes, its just 2 pipes. That is so that the formula cannot accidentally find a keword that includes a pipe symbol (if you had any) since there's no reason to have a keyword with 2 consecutive pipes. a keyword with 1 pipe is plausible, so by itself it won't do.

As for the spaces, in the formula, it adds spaces around the keywords:
" "&$E$2:$AG$100&" "

And then it also adds spaces around the text being searched:
" "&A10&" "

So the text "replacement windows michigan" becomes " replacement windows michigan " so that it can find " replacement windows ".

That's an incredibly smart way that I never thought of. Thanks so much for your explanation and expertise.

[NBVC]

Yes, its just 2 pipes. That is so that the formula cannot accidentally find a keword that includes a pipe symbol (if you had any) since there's no reason to have a keyword with 2 consecutive pipes. a keyword with 1 pipe is plausible, so by itself it won't do.

Actually in this case, the || was meant to "virtually" fill blank cells in the keyword table with that symbol. Since the number of keywords beneath each category are not necessarily equal, it is necessary to do this virtual replacement... other the search formula will assume a blank is a legitimate "keyword" and every cell in column B that does not have a keyword in the first column of keywords, will take on the category that contains the first blank cell adjacent to a cell that is not blank to the left of it.

As a test, try this formula:

Please Login or Register  to view this content.

CSE confirmed and copied down.... if your E1:AG100 list has uneven filled rows, then you should see some cells with incorrect categories...

using Tigeravatar's version with the " " will yield 0 in those "non matching cells" if you were to take out the || substitution.

[tigeravatar]

Ah cool, I should have tried it without the pipes to be sure. Thanks for the clarification, NBVC