## 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.

#### Download Excel *.xlsx file

The CODE function returns a specific number for the first character of the text argument, determined by your computers character set. […]

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Find a sequence of values – wildcard search

In my last post I showed you how to find a sequence of values. The array formula extracted the row […]

The MID function returns a substring from a string based on the starting position and the number of characters you want […]

Extract k-th word in cell value

The formula displayed above in cell range D3:D9 extracts a word based its position in a cell value. For example, […]

Convert array formula to a regular formula

Kamran Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?; […]

Find positive and negative amounts that net to zero

I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]

### 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.

**Contact Oscar**

You can contact me through this contact form

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...

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?