## Exact word in string without using vba in excel

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me to create an excel formula doing the same thing as the vba function.

In short, from the above blog post

*The function, modified the first argument (Text) and replaces all non-alpha characters with a space character. It then adds a leading and trailing space to both arguments. Finally, it uses the Instr function to determine if the modified Word argument is present in the modified Text argument*

*The ExactWordInString function looks for a complete word -- not text that might be **part of a different word. *

**Edit: Read Rick Rothstein (MVP - Excel) comments at the bottom of this blog post.**

Here is the formula I created in C2:

copied down as far as necessary.

Download excel sample file for this tutorial.

exact-word-in-string-using-excel-functions.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**IF(**logical_test,[value_if:true],[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**FIND(**find_text;within_text;[start_num]**)** Returns the starting position of one text string within another text string. FIND is case-sensitive

**COUNT(**value1;[value2]**)
**Counts the number of cells in a range that contain numbers

**CODE**(text)

Returns a numeric code for the first character in a string, in the character set used by the computer

**MID(**text, start_num, num_chars)

Returns the characters in the middle of a text string, given a starting postion and length

Thank you so much for this formula! You helped me so much. Please don't stop helping others. You certainly have a gift. God Bless!

Thanks Michael!

There seems to be an error in the above formula. If cell A11 contains the text value Million and B11 contains the text value Millio the formula returns TRUE.

(Thanks Niranjan)

Here is a formula that produces the same output as the one you posted but which uses less than half the number of function calls, is almost half the size (length-wise) and which produces the same output...

=AND(NOT(ISNUMBER(FIND(UPPER(MID("="&A2&"=", SEARCH(B2, A2), 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ"))), NOT(ISNUMBER(FIND(UPPER(MID("="&A2&"=", FIND(UPPER(B2), UPPER(A2))+LEN(B2)+1, 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))

Note: I used the FIND/UPPER construction on purpose; if you used SEARCH then "exact words" lying next to an asterisk or question mark could make the formula produce incorrect results because SEARCH would assume they were wildcards.

By the way, if you missed it, I think you might be interested in the update John posted to his blog entry showing the one-liner VB function that I sent him which does the same thing as his 12-line function does.

CORRECTION...

Damn, I posted the wrong formula (it is missing the IF function housing. Here is the correct formula...

=IF(ISNUMBER(SEARCH(B2, A2)), AND(NOT(ISNUMBER(FIND(UPPER(MID("="&A2&"=", SEARCH(B2, A2), 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ"))), NOT(ISNUMBER(FIND(UPPER(MID("="&A2&"=", SEARCH(B2, A2)+LEN(B2)+1, 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")))))

The comparison numbers change with this (one, because I had left some function calls out, but two, because I left the separating spaces in the function you posted)... my formula is about two-thirds the length of yours (215 characters versus 318 characters) and it uses just a little more than half the function calls (17 versus 31).

Amazing! Your formula works perfectly! Thank you for commenting!

You are quite welcome. When I first saw the question in John Walkenbach's blog, he had posted a VBA solution so I gave him back my one-liner VBA solution as a response. It did not even occur to me to try and solve the problem via a worksheet formula. When I saw your posted formula solution, it prompted me to try my hand at one as well. It was a fun exercise, so I thank you for thinking of it.

Now, back to your "Your formula works perfectly" comment. I just want to point out one difference between my formula and yours... as written, my formula is case **insensitive** where as yours is not. Walkenbach's original code was case insensitive, so I made my response to him the same... and it made sense to me for it to be case insensitive to cover the possibility that the word might be located at the beginning of a sentence. However, if you want to change my formula to be case sensitive, you can do that by simply changing the very first SEARCH function call to a FIND function call (leaving the rest of the formula alone).

I would like to use the "original" formula which is used in the example file. However it's case sensitive and I need it case insensitive as some of the words I'm looking for is the first letter and therefore Capital letter. Can you help with that.

Hi, this is a great formula. How can it be changed to detect whole numbers? Example String:

132, 98, 198, 200 - 222 AXT 78

Where if I search for 98 it returns TRUE but 22 returns FALSE.

Thanks for any help!

@Mario,

It is unclear if you are referring to my formula or the formula that Oscar included in his original blog article. Assuming you meant my function, I believe this modification of it will work correct to search for the specified number (and ONLY for numbers, not non-digits, even if coupled with digits) within a string of characters...

=IF(ISNUMBER(SEARCH(B2, A2)), AND(NOT(ISNUMBER(FIND(MID("="&A2&"=", SEARCH(B2, A2), 1), "0123456789"))), NOT(ISNUMBER(FIND(MID("="&A2&"=", SEARCH(B2, A2)+LEN(B2)+1, 1), "0123456789")))))

Rick Rothstein - you have seriously saved my day.

@Mattias Olsson,

I'm not sure which of my two formulas you were referring to, unless you meant my macro (which I alluded to in my first response to Oscar) that John Walkenbach posted in his blog; but I just wanted to say that you are quite welcome, I am glad that something I posted turned out to be useful to you.

Thank you all for commenting!

Oscar, do you know of any macro or free download to calculate the processing speed of Excel formulas or functions?

I know you can use "Timer" to calculate user defined functions and macros. John Walkenbach has an example on his blog: How Fast Is Your System?

gr8..really thanksful Rick Rothstein

@tricky...

Thanks for commenting on my formulas; I am glad you found them useful to you in some way. If you are into VB code, you should check out my response to John Walkenbach... my one-liner VB code is even more compact than the formula version. See the *UPDATE* section on this webpage...

http://spreadsheetpage.com/index.php/tip/is_a_particular_word_contained_in_a_text_sring/

Thank you very much. The function works perfectly, but only is case the letter before or after the "exact word" is that of the English ABC. In cases where a special Hungarian letter (like "ö", "ú") stands right after the word, it says "TRUE", not "FALSE". Can you help me?

I guess it has something to do with the "code" part of the function where the numbers indicate the position of the code number of the letters in the character table, don't they? Sorry for my silly questions.

SOLVED. With Rick Rothstein's function (thanks a lot!!!).

I realised it did not work for the first time because I did not pay attention to the extra spaces and the commas in the function. Now it works. All I had to do was translating the function elements, deleting the spaces, changing the commas to semicolons and adding the extra letters of the Hungarian alphabet. Thanks again!!!

Is there a way to set something like this up with a SUMIF formula? I need to add up revenue related to a list of companies. I don't want Kia's revenue to incluce Nokia's! This has to be done through referencing cells.

Pablo,

if there is a blank before and after the search string, you can use this formula:

Thank you Oscar! I wish it was that easy. The company names are placed in many different positions. If a cell only has the word Kia in it, or if it is in the beginning, then it wouldn't be recognized with that formula.

I have found two solutions! Happy to share with you:

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&G2&" "," "&$B$2:$B$10&" ")),$C$2:$C$10)

G2 = Key word

$B$2:$B$10 = List of companies

$C$2:$C$10 = Revenue list

Someone else posted: I assume you are looking for "Ford" which is case-sensitive, right? So that you won't have something like "afFordable health care", "AshFord" or even "Texas ford" in your list. If that's the case, you may try:

=SUM(IF(ISNUMBER(FIND(G2,$B$2:$B$417)),$C$2:$C$417,0))

CTRL SHIFT ENTER

Pablo,

thank you for posting solutions. Very appreciated!