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
Related posts:
Separate numbers from a text string
Count number of times a string exist in multiple cells using excel formula
Recommended categories
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/