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
 
img1
 
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))
 
img2
 
If we change the Criteria Text say “application” then, following will be the result refer below snapshot
 
img3
 
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 *

*

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>