Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
How to Search For Text in Excel?
When we work with excel we see so many situations which are peculiar in nature and one of those situations is searching for the particular text in the cell. The first thing 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 the certain words you may want the result in the next cell as “TRUE” or else FALSE. So Ctrl + F stops there.
Here we will take you through the formulas which can search for the particular text in the cell value and arrive at the result.
#1 – Formulas to Search Specific Text in Excel Cell
This is the question I have seen many times in excel forums. The very first formula 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.
Fo an example look at the below data.
In the above data, we have three sentences in three different rows. Now in each cell, we need to search for text in excel “Best”. So apply the FIND function.
In the Find Text argument mention the text that we need to find.
In Within Text select the full sentence i.e. cell reference.
The last parameter is not required to close the bracket and hit the enter key.
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.
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 ISNUMERIC function.
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, IF condition will return the result accordingly.
We can also use IFERROR function 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.
#2 – Alternatives to FIND Function
Alternative #1 – Instead of the FIND function, we can also use the SEARCH function to excel search the particular text in the string. Syntax of the SEARCH function is the same as the FIND function.
Supply the find text as “Best”.
Within Text is our cell reference.
Even 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 – Another way of searching for a particular text is by using the COUNTIF function. This function works without any error.
In the range, the argument selects the cell reference.
In the criteria column we need to use excel wildcard characters because we are just finding the part of the string value, so enclose the word “best” with an asterisk (*) wildcard.
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.
Now we can apply only IF condition to get the result without any error.
#3 – 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.
For this first select the data cells and click on Conditional Formatting > New Rule.
Unser new Rule select “Format Only Cells that contain”.
From the first drop-down select “Specific Text”.
In the formula, section enters the text that we are searching for in double-quotes with the equal sign. =”best”.
Then click on FORMAT and choose the formatting style.
Click on OK, it will highlight all the cells which have the word “best”.
Like this using various techniques in excel we can search the particular text in excel.
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 downloadable excel template. You may learn more about excel from the following articles –