Formula to find text string within another string in MS Excel

If you want to search for a specific string within another string in any version of Microsoft excel, this can be done by combining a  number of Excel formulas . Microsoft excel contains number of useful function to perform string related operations. For example you may want to find a string “esp” in string “yespakto”. Suppose this string is in cell D7. To solve this problem use following formula

=IF(ISNUMBER(SEARCH(“esp“,D7)),MID(D7,SEARCH(“esp“,D7),LEN(“esp“)),”")

note bold face “esp” in the string, this is the string which will be searched in cell D7.

after applying you will get result as shown in figure below. Here cell “E” contains your desired results.

search string in excel

If you feel any problem in formula after copy paste. Try typing formula manually.

Formula used to solve this problem contains following formulas,

1- ISNUMBER(value)

This function accepts one argument, it return TRUE if the passed argument is valid number and return FALSE if the passed argument is not a nubmer.

2- SEARCH(find_text,within_text,[start_num])

This function can be passed two or three arguments. It searches first argument in the a string which is passed as second argument. If the third argument is passed, it starts searching from that index(third argument). This function returns the index number at which the string is found. First character in the string has index value equal to 1.

3- MID(text,start_num,num_chars)

MID function extracts charters from the string passed in first argument. The extraction start from index passed in second argument and third parameter is used to specify that how many characters shall be extracted.

4- LEN(text)

The LEN function is used to get the length of  string passed as argument. It return the number of character in the passed string.

MS Excel | RSS 2.0 | Respond | Trackback |