TEXTBEFORE and TEXTAFTER

Last Updated on 18/04/2024

TEXTBEFORE and TEXTAFTER are two new Excel functions to split and extract substrings easily.

Let's extract the names, first names, cities, zip codes, and addresses from this document.

Extract the first word of a text

The TEXTBEFORE function allows you to extract the first word of a text based on a delimiter, such as space.

Extract the first word from a text

To extract the first word from a string, simply write the following function

=TEXTBEFORE(A2," ")

The function is easy to understand; "We extract the first word BEFORE the first delimiter."

Extract the first two words

But the TEXTBEFORE has a third interesting argument that allows you to indicate the number of words (or occurrences) we want to return.

To extract the name and surname, we will write the following formula

=TEXTBEFORE(A2," ",2)

To get the same result with the LEFT and SEARCH functions, one would have had to write

=LEFT(A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

The TEXTAFTER function

Conversely, the TEXTAFTER function will return the rest of the characters' string from the nth delimiter.

So, to return the address, postal code, and city, we will write:

=TEXTAFTER(A2," ",2)

But what is much more enjoyable with the TEXTAFTER function it's to put a negative number in the 3rd argument.

Indeed, by putting -1, you extract the last word; by putting -2, you extract the last 2 words (easy 😀). In this situation, the delimiter is a comma

=TEXTAFTER(A2,",",-2)

I tried to build you an equivalent formula with the RIGHT function, but finding the penultimate space is impossible.

So this new function enriches the possibilities of text extraction in Excel 😀👍

How do you extract a subchain in the middle?

We have seen how to extract the first name, first name, and last name, but how to extract only the name? You must use these formulas twice to obtain this result.

• A first time extracting the first 2 words
• And we will deduce from this result the last word

=TEXTAFTER(TEXTBEFORE(A2," ",2)," ")

And finally, to extract the address only, we will

• Reduce the initial text of the first and last name on the space
• From this result, we remove the last 2 words on the comma

=TEXTBEFORE(TEXTAFTER(A2," ",2),",",-2)

In closing, the TEXTBEFORE and TEXTAFTER functions in Excel open up a world of possibilities for efficiently extracting specific text segments from your data. With these powerful tools at your disposal, you can streamline your workflow, enhance data analysis, and save valuable time.

TEXTBEFORE and TEXTAFTER

Last Updated on 18/04/2024

TEXTBEFORE and TEXTAFTER are two new Excel functions to split and extract substrings easily.

Let's extract the names, first names, cities, zip codes, and addresses from this document.

Extract the first word of a text

The TEXTBEFORE function allows you to extract the first word of a text based on a delimiter, such as space.

Extract the first word from a text

To extract the first word from a string, simply write the following function

=TEXTBEFORE(A2," ")

The function is easy to understand; "We extract the first word BEFORE the first delimiter."

Extract the first two words

But the TEXTBEFORE has a third interesting argument that allows you to indicate the number of words (or occurrences) we want to return.

To extract the name and surname, we will write the following formula

=TEXTBEFORE(A2," ",2)

To get the same result with the LEFT and SEARCH functions, one would have had to write

=LEFT(A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

The TEXTAFTER function

Conversely, the TEXTAFTER function will return the rest of the characters' string from the nth delimiter.

So, to return the address, postal code, and city, we will write:

=TEXTAFTER(A2," ",2)

But what is much more enjoyable with the TEXTAFTER function it's to put a negative number in the 3rd argument.

Indeed, by putting -1, you extract the last word; by putting -2, you extract the last 2 words (easy 😀). In this situation, the delimiter is a comma

=TEXTAFTER(A2,",",-2)

I tried to build you an equivalent formula with the RIGHT function, but finding the penultimate space is impossible.

So this new function enriches the possibilities of text extraction in Excel 😀👍

How do you extract a subchain in the middle?

We have seen how to extract the first name, first name, and last name, but how to extract only the name? You must use these formulas twice to obtain this result.

• A first time extracting the first 2 words
• And we will deduce from this result the last word

=TEXTAFTER(TEXTBEFORE(A2," ",2)," ")

And finally, to extract the address only, we will

• Reduce the initial text of the first and last name on the space
• From this result, we remove the last 2 words on the comma

=TEXTBEFORE(TEXTAFTER(A2," ",2),",",-2)

In closing, the TEXTBEFORE and TEXTAFTER functions in Excel open up a world of possibilities for efficiently extracting specific text segments from your data. With these powerful tools at your disposal, you can streamline your workflow, enhance data analysis, and save valuable time.