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
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.
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)
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.