Searching a String for a Specific Substring in Excel

In this article, we will learn how to search a substring from a given string in Excel.

In excel, substring is a part of another string. It can be a single character or a whole paragraph. To search a string for a specific substring, we will use the ISNUMBER function along with the FIND function in Excel. Instead of FIND you can always use the excel SEARCH function for non-case sensitive searches.

ISNUMBER function is used to check the cell, if it contains a number or not.

Syntax of ISNUMBER:   

=ISNUMBER (value)

FIND function returns the location number of the character at which a specific character or text string is first found, reading left to right (case-sensitive).

Syntax of Find:

=FIND(find_text,within_text,[start_num])

pasted image 0 (34)

Here we have two columns. Substring in Column B and Given string in Column A.

Write the formula in C2 cell

Formula:

=ISNUMBER(FIND(B2,A2))

Explanation:

Find function takes the substring from the B2 cell of Column B and it then matches it with the given string in the A2 cell of Column A.

ISNUMBER checks if the string matches, it returns True else it returns False.
pasted image 0 (35)

Copy the formula in other cells, select the cells taking the first cell where the formula is already applied, use shortcut key Ctrl+ D.

As you can see the output in column C shows True and False representing whether substring is there or not.

Now, what if you want to do a non-case sensitive search in excel?

How to search in excel cells for non-case sensitive substrings?

It’s simple. Replace the find function with the excel SEARCH function in the formula above.  Since SEARCH is case-insensitive.

There are more articles on FIND and SEARCH function to find strings or value. Hope you learned how to find substring in a stringfrom the article. You can perform these tasks in Excel 2016, 2013 and 2010. If you have any unresolved query, please do comment below in the comment box. We will help you.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube