How to Find if a Text String Contains Specific Text in Excel

Published on March 8, 2017
image_pdfimage_print

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

=ISNUMBER(SEARCH(substring,string)

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.

=IF(ISNUMBER(SEARCH(B2,A2)),A2,A2&” “&B2)

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.

challenge4solution

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.

Facebook

Twitter

YouTube

Have a great day! Chow!

GET MY EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE

Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide for FREE!

Thank you for subscribing.

Something went wrong.

HUGE LIMITED TIME DISCOUNT ON THE BEST ADVANCED EXCEL COURSE ENROLL NOW!!
excel_look_professional
Making Excel Worksheets Look Professional – A Case Study
14 Excel tips
14 Excel Tips – Make Yourself Super-Efficient in Excel
DATA CLEANING EXCEL
Ultimate Guide to Data Cleaning in Excel – 11 Super-Powerful Data Cleaning Techniques
RANDOMIZE LIST RANDBETWEEN
Randomize a List in Excel Using RANDBETWEEN Function
FREEZE PANES
Freeze Panes in Excel – The Complete Guide
HOW TO LEARN EXCEL
How to Learn Excel and Be a Spreadsheet Champion
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
BECOME AN EXCEL NINJA COMPLETE REVIEW
Become An Excel Ninja – Course Review
BEST EXCEL COURSES
10 Best Online Courses to Make You an Excel Wizard
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
How to Sort Excel Worksheet in Alphabetical Order
How to Sort Excel Worksheets in Alphabetical Order
VBAPOST1
How to Add And Name A New Sheet At The Same Time Using Excel VBA
ROUNDING NUMBERS IN EXCEL
Rounding Numbers in Excel – The Complete Guide
number_of_words
How to Find the Number of Words in a Text String in Excel
HOW TO FIND IF A TEXT STRING CONTAINS SPECIFIC TEXT IN EXCEL
How to Find if a Text String Contains Specific Text in Excel
How To Indentify ULP case in a text string
How To Identify If A Text String Is In Upper, Lower Or Proper Case
Pivot Tables
How To Convert Pivot Table Into Frequency Distribution
How to Convert Pivot Table into a Frequency Distribution in Excel

GET MY EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE

Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide for FREE!

Thank you for subscribing.

Something went wrong.

Share
Tweet
+1
Pin
Share
Stumble
Enjoyed this video?
HOW TO FIND IF A TEXT STRING CONTAINS SPECIFIC TEXT IN EXCEL
"No Thanks. Please Close This Box!"