TEXTBEFORE and TEXTAFTER

TEXTBEFORE and TEXTAFTER
Last Updated on 15/03/2023
Reading time: 2 minutes

Among the 14 new functions added to Excel 365 (version 2208), the function TEXTBEFORE and TEXTAFTER are crucial to help to extract sub-string on a delimiter (space, comma, dashes, ...).

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

Table clients and addresses

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," ")

Extract the first word of the cells

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)

Extract Name and Firstname

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)

TEXTAFTER the second space

Extract the last 2 words

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, and by putting -2, you extract the last 2 words (easy 😀). In this situation, the delimiter is a comma

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

Extract the last 2 words with TEXTAFTER

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 to 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 make double use of these formulas 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)," ")

Extract the name only

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)

Extract the address only

Leave a Reply

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

TEXTBEFORE and TEXTAFTER

Reading time: 2 minutes
Last Updated on 15/03/2023

Among the 14 new functions added to Excel 365 (version 2208), the function TEXTBEFORE and TEXTAFTER are crucial to help to extract sub-string on a delimiter (space, comma, dashes, ...).

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

Table clients and addresses

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," ")

Extract the first word of the cells

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)

Extract Name and Firstname

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)

TEXTAFTER the second space

Extract the last 2 words

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, and by putting -2, you extract the last 2 words (easy 😀). In this situation, the delimiter is a comma

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

Extract the last 2 words with TEXTAFTER

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 to 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 make double use of these formulas 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)," ")

Extract the name only

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)

Extract the address only

Leave a Reply

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