Finding if a Text String Contains Specific Text in Excel
Today I want to discuss the method to determine whether a text string contains another text string.
So let’s dive in!
The basic formula to determine whether one text string contains another text string (hereinafter referred to as ‘substring) is
The ‘substring’ in the formula is the text you want to locate, and the ‘string’ is the text that you want to look into. Both of these can be references to cells or hard coded.
The roles of the different functions in the above formula are given below:
SEARCH: The SEARCH function locates one text string within a second text string, and returns starting position of the first text string from the first character of the second text string. It will return a #VALUE error if the second text string does not contain the first one. You can click here for more information on how the SEARCH function works.
ISNUMBER: The ISNUMBER function returns TRUE for numbers and FALSE for non-numerical values. So, if the SEARCH function finds the substring, it returns the position, and ISNUMBER returns TRUE (because the position is a number). On the other hand, if the SEARCH function doesn’t find the substring, it returns a #VALUE! error, which is not a number and hence the ISNUMBER function returns FALSE.
Note: If you want this function to be case-sensitive, just replace the SEARCH function with the FIND function.
Let’s understand this better by the way of using it practically. Click here to download an example file.
What we need to do here is to find whether the text strings in Column A contain the respective text strings in Column B, and if the former does not contain the latter, we need to add the latter to the former.
So we need to type in the following formula in cell C2.
The part – ISNUMBER(SEARCH(B2,A2) should be easy to understand now, once you understand the discussion above on the formula. Just to recap, this part of the formula would return TRUE if the text in cell B2 is present in cell A2.
Now, on to the IF part. The IF formula inserted in the formula adds the text in cell B2 to the text in cell A2, if the latter does not contain it already. In other words, IF the result of the =ISNUMBER(SEARCH(B2,A2) part is TRUE (i.e. cell A2 contains the text in cell B2), then the formula will return the same text as present in Cell A2. And, IF the result is FALSE (i.e. cell A2 does not contain the text in cell B2), then ‘A2&” “&B2’ part of the formula will add the text in cell B2 to the text in cell A2 and that would be the result that it will return.
To know more about the IF function, click here.
Lastly, we just need to pull the formula till cell C242 to get the results for all the text strings.
I really hope you found this article interesting. If you did I encourage you to follow us on social media to get updates on all my new posts. The links are given below.
Have a great day! Chow!
Get my ULTIMATE EXCEL SHORTCUTS COURSE
worth $40 for FREE!!
Subscribe to our newsletter and get interesting stuff and updates to your email inbox.
Thank you for subscribing.
Something went wrong.