How to Split and Extract Text in Microsoft Excel

Microsoft Excel offers a number of functions for working with text. If you want to extract part of a text string, or split a string into rows or columns, there are three specific functions that will do the job.

You can use TEXTBEFORE and TEXTAFTER to extract text before or after a specific word or character. This makes these functions more flexible than the LEFT, RIGHT, and MID functions you might use. To split a string into different cells, you can use TEXTSPLIT.

Note: These three functions are new to Excel as of August 2022. They will be rolled out to Office Insiders and then to all Excel users over time.

The TEXTBEFORE function

The syntax for the function is TEXTBEFORE(text, delimiter, instance, match_mode, match_end, if_not_found). The first two arguments are also required text either the actual text or a cell reference and delimiter is the point where you want the text to be before.

Here are descriptions of the three optional arguments:

• example: Use this argument when there is more than one occurrence of delimiter in the string and you want a specific one.
• match_mode: Enter 0 to be case-sensitive or 1 to be case-sensitive. The default value is 0.
• Playing: Enter 0 to not match the delimiter to the end of the text and 1 to match it. The default value is 1.
• If_not_found: Use this argument if you prefer a result instead of an error for values ​​not found.

Now that you know the arguments, let’s look at some examples of using TEXTBEFORE.

In this first example, we’ll extract all the text before the word “from” in cell A2 using this formula:

=TEXTBEFORE(A2,"from")

With this next formula, we’ll extract all the text before the second occurrence of the word “text”.

=TEXTBEFORE(A2,"text",2)

As another example, let’s use the match_mode Argument for a case-match.

=TEXTBEFORE(A2,"TEXT",,0)

TIED TOGETHER: 13 essential Excel functions for data entry

The TEXTAFTER function

TEXTAFTER is the exact opposite of TEXTBEFORE. The syntax for the function is TEXTAFTER(text, delimiter, instance, match_mode, match_end, if_not_found).

As with its counterpart, the first two arguments are also required text either the actual text or a cell reference and delimiter is the point where you want the text to be after.

The three optional arguments described above work the same as the TEXTBEFORE function.

In this first example, we’ll extract all the text after the word “from” in cell A2 using this formula:

=TEXTAFTER(A2,"from")

With this next formula, we’ll extract all the text after the second occurrence of the word “text”.

=TEXTAFTER(A2,"text",2)

And finally we use the match_mode Argument for a case-match.

=TEXTAFTER(A2,"TEXT",,0)

The TEXTSPLIT function

You can use the TEXTSPLIT function to split text into cells in a row or column based on the delimiter, such as a space or period.

TIED TOGETHER: How to split data into multiple columns in Excel

The syntax is TEXTSPLIT(text, column_delimiter, row_delimiter, ignore, match_mode, pad_with) where the first argument is required and can be actual text or a cell reference. By default, the formula splits the text into columns, but you can use rows with instead row_delimiter Fight.

Here are descriptions of the remaining arguments:

• To ignore: Enter FALSE to create a blank cell if two delimiters are consecutive. The default is TRUE.
• match_mode: Searches the delimiter for a match, which is case-sensitive.
• Pad_mit: To pad the result, enter a value. Otherwise, the #N/A error is displayed.

In this example, we split the text string in cell A2 into columns with a space as ours column_delimiter in quotes. Here is the formula:

=TEXTSPLIT(A2," ")

Instead of splitting the string across columns, we split it across rows using a space row_delimiter with this formula:

=TEXTSPLIT(A2,," ")

Note that in this formula we leave that column_delimiter argument empty and use only the row_delimiter.

For this next example, we’re going to split into another column just after the semicolon:

=TEXTSPLIT(A2,";")

Next, we’re going to split into a row instead of a column just after the semicolon:

=TEXTSPLIT(A2,,";")

The TEXTSPLIT function is very powerful. If you are looking for more complex examples using the optional arguments, visit the Microsoft support page for the TEXTSPLIT function.

Next time you want to extract text from a cell or split a long text string, keep these Excel features in mind.

TIED TOGETHER: 12 basic Excel functions everyone should know