The SEARCH formula in excel.
Is a very handy tool when it comes to text operations.
You can easily find text with it in a big data table.
What does the SEARCH formula in excel do?
It can SEARCH a piece of text in a cell’s content. If it finds something it will give us a number as an answer, marking where the searched text starts in the cell. If it doesn’t find anything it gives back an error.
What the search formula need?
=SEARCH(find_text,within_text,[start_number]) – What we want to search for inside of the text
=SEARCH(find_text,within_text,[start_number]) – Where we want to search
=SEARCH(find_text,within_text,[start_number]) – Start number – (optional) Which character we want to start the search.
A handy example below:
Here we will try to filter out proper email addresses in a small table.
Note: – At such a small table this is not very handy.
However, this would be very useful if you have thousands of rows.
- We use the SEARCH formula to search for the @ symbol in the text in Column A
- We “wrap” the search formula inside an ISNUMBER formula. This will turn our results to TRUE/FALSE
- TRUE/FALSE can be used by the Conditional Formatting formula, so we copy the formula in to a Conditional Formula. Where the @ symbol is present it will be a proper email address so we can make it green.
Come and visit us.
Come and visit excelangel on Facebook to find and join the growing community of true office workers.
Oh, I almost forgot.
Drop us a like while you there…