Hi Excellers another #formulafriday Excel tip for you. Today let’s look at just how useful the SEARCH Function is. Yes that’s right SEARCH. Most Excel users are aware if the FIND Function, but what about SEARCH?.
Search allows us to find the location of text within a text string, and will return the number representing the location of the text.
Or in plainer words…..find the location of my text or text string within a cell containing text or a text string starting at the position I specify. A couple of great features it has is it’s NOT case sensitive and you can use wildcards with it. (Unlike the FIND Function)
Phew, lot’s of words but it really is a useful Function.
Lets’ take a look at the basics then move on and see how useful it really can be. Here are the basics, with the formula showing in the result cell.
….and the result of Excel’s calculation…
That’s cool right?, but let’s turn up the volume on this and use SEARCH to highlight any cells in my worksheet that contain the word “Store”.
Let’s say I have a data source I need to cleanse, and 0ne of the issues I have is that there are many combinations of the word “Store” and also they maybe in differing cases, both upper and lower of even a combination of them both in my data source.
Being able to use Formulas within Conditional Formatting gives us infinite possibilities and flexibility. So all we need to do is get the formula to return TRUE when a cell contains “Store”.
- Highlight the cells that you want to condtionally format
- Home Tab
- Conditional Formatting
- New Rule
- Use A Formula To Determine Which Cells To Format
- In the formula dialog box type the following formula
- Choose how you want to format or highlight your cells, in my case I have chosen green
- Hit Apply
- Job Done
The formula uses SEARCH to find the position of the word Store and if that exists then it returns the position of it, andalso generates an error if it does not exist. But by encasing this in the ISNUMBER Function we can ignore the error and get Excel to return TRUE when a number is found, and then use this TRUE as a trigger to conditonally format the cell that contains Store.
When you apply conditional formatting using a formula, that formula is calculated relative to the active cell in that selection, and therefore the A2 will change to the address of the cell being evaluated, since A2 is relative. I have used $C$2 as an absolute reference to supply the Excel formula with the string I want to search for.
Success! No issues here. Job has been done.
If you want to download the Example workbook to try out the formulas then you can do so below
- Create A Micro Chart In Excel
- How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
- Create A Timestamp In Excel With Formulas
- Use TRIM for a speedy data clean up