WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Search For Text in Excel

Search For Text in Excel

How to Search For Text in Excel?

When we work with excel, we see so many situations that are peculiar in nature, and one of those situations is searching for the particular text in the cell. The first thing that comes to mind when we say we want to search for a particular text in the worksheet is the “Find & Replace” method in excel, and that is the most popular one too. But Ctrl + F can find you the text you are looking for but cannot go beyond that. Let’s say if the cell contains certain words, you may want the result in the next cell as “TRUE” or else FALSE. So Ctrl + F stops there.

Search For Text in Excel

Here we will take you through the formulas which can search for the particular text in the cell value and arrive at the result.

You can download this Search For Text Excel Template here – Search For Text Excel Template

Which Formula Can Tell Us A Cell Contains Specific Text?

This is the question I have seen many times in excel forums. The very first formula that came to my mind was to “FIND” function.

Find function can return the position of the supplied text values in the string. So if the FIND method returns any number, then we can consider the cell as it has the text or else not.

For example, look at the below data.

Search For Text Excel Example 1

In the above data, we have three sentences in three different rows. Now in each cell, we need to search for the text “Best.” So apply the FIND function.

Search For Text Excel Example 1-1

In the Find Text argument, mention the text that we need to find.

Search For Text Excel Example 1-2

Within Text, select the full sentence, i.e., cell reference.

Search For Text Excel Example 1-3

The last parameter is not required to close the bracket and hit the enter key.

Search For Text Excel Example 1-4

So, in two sentences, we have the word “best.” We can see the error value of #VALUE! in the cell B2 that shows that the cell A2 doesn’t have the text value “best.”

Instead of numbers, we can enter the result in our own words as well. For this, we need to use the IF condition.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Search For Text Excel Example 1-5

So, in the IF condition, I have supplied the result as “Text Found” if the value “best” found, or else I have supplied the result to show as “Not Found.”

But here we have a problem, even though I have supplied the result as “Not Found,” if the text was not found still we are getting the error value as #VALUE!.

So, nobody wants to have an error value in their excel sheet. To overcome this error value, we need to enclose the formula with the ISNUMERIC function.

Search For Text Excel Example 1-6

ISNUMERIC function evaluates whether the FIND function returns the number or not. If the FIND function returns the number, then it will supply TRUE to IF condition or else FALSE condition. Based on the result supplied by the ISNUMERIC function, the IF condition will return the result accordingly.

We can also use the IFERROR function in excel to deal with error values instead of ISNUMERIC. The below formula will also return “not Found” if the error value returned by the FIND function.

Search For Text Excel Example 1-7

Alternatives to FIND Function

Alternative #1 – Excel Search Function

Instead of the FIND function, we can also use the SEARCH function in excel to search the particular text in the string. The syntax of the SEARCH function is the same as the FIND function.

Alternative to FIND Function 1

Supply the find text as “Best.”

Alternative to FIND Function 1-1

Within Text is our cell reference.

Alternative to FIND Function 1-2

Even the SEARCH function returns an error value as #VALUE! if the finding text “best” is not found. As we have seen above, we need to enclose the formula with either ISNUMERIC or IFERROR function.

Alternative #2 – Excel Countif Function

Another way of searching for a particular text is by using the COUNTIF function. This function works without any error.

Alternative to FIND Function 1-3

In the range, the argument selects the cell reference.

Alternative to FIND Function 1-4

In the criteria column, we need to use a wildcard in excel because we are just finding the part of the string value, so enclose the word “best” with an asterisk (*) wildcard.

Alternative to FIND Function 1-5

This formula will return the count of the word “best” in the selected cell value. Since we have only one “best” value, we will get only 1 as the count.

Alternative to FIND Function 1-6

Now we can apply only the IF condition to get the result without any error.

Alternative to FIND Function 1-7

Highlight the Cell which has Particular Text Value

If you are not a fan of formulas, then you can highlight the cell that has a particular word. To highlight the cell which has the word “best,” we need to use conditional formatting in excel.

For this first, select the data cells and click on Conditional Formatting > New Rule.

Alternative to FIND Function 2

Unser new Rule select “Format Only Cells that contain.”

Alternative to FIND Function 2-1

From the first drop-down, select “Specific Text.”

Alternative to FIND Function 2-2

In the formula, section enters the text that we are searching for in double-quotes with the equal sign. =”best.”

Alternative to FIND Function 2-3

Then click on FORMAT and choose the formatting style.

Alternative to FIND Function 2-4

Click on OK. It will highlight all the cells which have the word “best.”

Alternative to FIND Function 2-5

Like this using various techniques, we can search the particular text in excel.

Recommended Articles

This has been a guide to Search For Text in Excel. Here we discuss the top three methods that can search the cell value for a specific text and arrive at the result along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • Find Links in Excel
  • Using Find and Select in Excel
  • Search Box in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Search For Text Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More