Skip to main content
Excel

Discover TEXTBEFORE, TEXTAFTER and TEXTSPLIT, the new text functions in Excel

By March 16, 2023No Comments

Microsoft continues to develop new features to make Excel even more convenient for users. Among the newest additions? TEXTAFTER, TEXTBEFORE and TEXTSPLIT. In this blog, we briefly explain what these features do and how you can use them to your advantage.

TEXTAFTER: what is it and how do you use it?

TEXTAFTER is a function in Excel that helps you find the text that follows a particular character or string of characters in a cell. This can be useful if, for example, you have a list of names and e-mail addresses, and you want to find only the domain names of the e-mail addresses.

Let’s zoom in on this example:

Suppose you have a list of email addresses in column A, and you want to extract only the domain names of these addresses. Here’s how to do that:

  1. 1. Select the cell in which you want to place the domain names
  2. 2. Type the formula: =TEXTAFTER(A2,”@”)
  3. 3. Press enter

The formula will now find the text after the “@” sign in cell A2 and display it in the cell in which you typed the formula (B2). Note that we can also put text in the sixth field of the function to replace the error message when there is no “@” in it (see cell B10).

TEXTBEFORE: what is it and how do you use it?

TEXTBEFORE is a function in Excel that helps you find the text preceding a particular character or string of characters in a cell. For example, use this function if you have a list of names and initials, and you want to find only the initials.

Let’s zoom in on this example:

Suppose you have a list of names in column A, and you want to find only the initials of these names. Here’s how to do that: Here’s how to do that:

  1. 1. Select the cell in which you want to place the initials
  2. 2. Type the formula: =TEXTBEFORE(A2,” “)
  3. 3. Press enter

The formula will now find the text before the first space in cell A2 and display it in the cell in which you typed the formula (B2). Note that we can also place text in the sixth field of the function to replace the error message when there is no space in it (see cell B10).

TEXTSPLIT: what is it and how do you use it?

The new Excel feature TEXTSPLIT makes it easier to separate and organize text into separate cells. This allows you to split data on a particular separator and place the results in separate cells/columns.

Let’s zoom in on this example:

Suppose you have a column of e-mail addresses in the format firstname.lastname@emailprovider.com, and you want to separate everything into separate cells. Use the TEXTSPLIT function to perform this task easily.

  1. 1. Select the cell in which you want to place the result (B2)
  2. 2. Type the formula: =TEXTSPLIT(SUBSTITUTE(A2;”@”;”.”);”.”)
  3. 3. Press enter

Excel will now distinguish all values with a dot and place them in different columns (B2,C2,D2 and E2).

Note that in this example we have nested the function SUBSTITUTE to ensure that the character ‘@’ is replaced by a period. Thus, whenever a period occurs, the TEXTSPLIT function can distinguish the value and thus split it. Even the text after the @ sign is now split as a result.

Want to know more? Sign up for one of our online or classroom Excel courses to further sharpen your knowledge.