Exact word in string
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.
Explaining formula in cell C2
Step 1 - Check if string exists in cell
The FIND function returns the position of a string in cell value, if not found an error is returned. The ISNUMBER function returns TRUE for all numerical values and FALSE for all else even error values.
The IF function uses the boolean value to determine if the first argument or second argument is going to be returned.
IF(ISNUMBER(FIND(B2, A2)), formula, FALSE)
becomes
IF(ISNUMBER(FIND("trap", "Million of innocent rats are trapped--quartely!")), formula, FALSE)
becomes
IF(ISNUMBER(30), formula, FALSE)
becomes
IF(TRUE, formula, FALSE)
The IF function now continues the calculation with the first argument formula.
Step 2 - Check if the character before the string is larger than ansi code 122
The CODE function converts a character into ansi equivalent. Character "A" is 65 and "z" is 122.
COUNT((IF(CODE(MID(A2,FIND(B2,A2)-1,1))>122,1,""))
becomes
COUNT((IF(CODE(MID(A2,FIND(("trap","Million of innocent rats are trapped--quartely!")-1,1))>122,1,""))
becomes
COUNT((IF(CODE(MID(A2,30-1,1))>122,1,""))
becomes
COUNT((IF(CODE(" ")>122,1,""))
becomes
COUNT((IF(32>122,1,""))
becomes
COUNT((IF(FALSE,1,""))
becomes
COUNT(("")
and returns 0.
Step 3 -Â Check if character before the string is less than ansi code 65
The MID function extracts a part of a string based on a start character and the length.
IF(CODE(MID(A2,FIND(B2,A2)-1,1))<65,1,"")
becomes
IF(CODE(MID(A2,30-1,1))<65,1,"")
becomes
IF(CODE(" ")<65,1,"")
becomes
IF(32<65,1,"")
and returns 1.
Step 4 - Check if string is at the very beginning
IF(FIND(B2,A2)-1<1,1,"")
becomes
IF(30-1<1,1,"")
becomes
IF(FALSE,1,"")
and returns "".
Step 5 - Check if the character after the string is larger than ansi code 122
The LEN function counts characters in a cell.
COUNT((IF(CODE(MID(A2,FIND(B2,A2)+LEN(B2),1))>122,1,""))
becomes
COUNT((IF(CODE("p")>122,1,""))
becomes
COUNT((IF(112>122,1,""))
becomes
COUNT((IF(112>122,1,""))
becomes
COUNT("")
and returns 0.
Step 6 -Â Check if character after the string is less than ansi code 65
The IF function returns a value determined by the logical expression in the first argument. If TRUE then the second argument is returned, FALSE returns the third argument.
IF(CODE(MID(A2,FIND(B2,A2)+LEN(B2),1))<65,1,"")
becomes
IF(CODE("p")<65,1,"")
becomes
IF(112<65,1,"")
and returns "".
Step 7 -Â Check if string is at the very end
IF((FIND(B2,A2)+LEN(B2)+1)>LEN(A2),1,"")
becomes
IF(35>LEN(A2),1,"")
becomes
IF(35>47,1,"")
and returns "".
Step 8 - Nested IFs
All these IF functions are nested.
Get Excel *.xlsx file
Misc category
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]
Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: […]
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]
Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]
This formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters. Excel 365 […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
25 Responses to “Exact word in string”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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 files 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...
https://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!
Re: Exact word in string without VBA. - Referring to first solution posted
Thanks so much but I found a bug in your code. I was comparing the contents of two cells as originally suggested. First cell contains a script like this:
A2
"I dont like the brand"
B2
"Options : I don`t like the brand;I have not been approached by the sales team;The commission is low;The stock runs out quickly;Not allowed to have competition;The network coverage for the MSP is poor in the region;Repetitive technical incidents undermining my airtime sales;Availability of products at the level of sub-dealers / dealers;Bad experience with sales team;Bad experience with customer support;Stock shortage i.e. isn`t available;Demand is low"
Now the code is supposed to check if the exact same string in A2 can be found in B2...if not return false. The code passed all but 1 test i conducted. I took out the "I" from A2 but it still said the formula was true. Hows that possible?