How to Retrieve Text Before Specific Character in a String in Microsoft Excel 2010

In this article, you will learn how to extract all text strings before a specific text.

If there is a requirement to retrieve the text from a column before a specific text, we can use a combination of TRIM, LEFT, SEARCH functions to get the output.


TRIM: Removes all spaces from a text string except for single spaces between words

Syntax: =TRIM(text)

text: It is a string from which you want to remove extra spaces.


LEFT: Returns the specified number of characters from the start of a text string.

Syntax: =LEFT(text,num_chars)

text: It is the text string that contains character that you want to extract

num_chars: It specifies the number of characters from left you want to extract.


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

Syntax =SEARCH(find_text,within_text,start_num)

find_text: The text that you want to find.

within_text: Itis the text in which you want to search.

start_num: This is optional. It is the number in the string from which you want to extract data.

Let us understand with an example:


We have text in column A, and in column B, we want to get the result & cell D2 contains the criteria text. Refer below shown snapshot
Let us enter word “tool” in Criteria Text cell D2

The formula in cell B2 would be =TRIM(LEFT(A2,SEARCH($D$2,A2)-1))
If we change the Criteria Text say “application” then, following will be the result refer below snapshot
In this way, you can get the text strings before a specific text string.

Leave a Reply

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

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.