Written by:

Written by:

Thomas Szigeti

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.

  1. We use the SEARCH formula to search for the @ symbol in the text in Column A
  2. We “wrap” the search formula inside an ISNUMBER formula. This will turn our results to TRUE/FALSE
  3. 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.
  • The difference between SEARCH and FIND formula is that the SEARCH formula accepts wildcards.
  • The SEARCH formula accepts (?) question mark, and (*) asterisk as wildcard
  • The ? can match any single character in the text
  • The * can match more than one characters. ie.: substitute parts of a word.

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…

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]