How to Split Text in Formula (TEXTSPLIT Function)

This guide is going to be about splitting text in Excel.

But we have been doing it before as well – either using the Text-to-Columns feature or the Flash Fill tool.

Yes, okay. But this time we are going to do it using a function. You heard that right.

The TEXTSPLIT function of Excel is a modern-day function of Microsoft Excel 365 that’s meant to split text strings across multiple cells. It caters to almost all of the considerations in your head for splitting text strings in Excel 🔤

And I just can’t wait to walk you through the tutorial below to equip you with everything you need to know about it. So here’s your free practice workbook for this guide, download it now and come along with me.

Table of Contents

The TEXTSPLIT Function (What is it and Syntax)

The TEXTSPLIT function is a modern Excel function that splits text in Excel based on a given delimiter (the point from where you want the text to be split) across multiple rows and columns ✂

As the result is text split across multiple rows and columns, it is a dynamic array that spills across multiple cells.

Syntax:

The syntax of the TEXTSPLIT function is as below:

Here’s a breakup of syntax to see how each argument works:

• Text (Required argument): The text to be split.
• Col_Delimiter (Optional argument): The text/character at the point from where the text is to be split across columns.
• Row_Delimiter (Optional argument): The text/character at the point from where the text must be split across rows.
• Ignore_Empty (Optional argument): Ignores consecutive delimiters if set to 1 or TRUE. If omitted, defaults to FALSE or 0 and produces empty cells by considering consecutive delimiters.
• Match_mode (Optional argument): Performs a case-sensitive match if set to 1 or TRUE. If omitted, it defaults to FALSE or 0 and performs a case-insensitive match.
• Pad_with (Optional argument): The value to pad the result with if there are missing cells in two-dimensional arrays. If omitted, it defaults to the #N/A error.

Split Text in Excel (Basic Example)

Time we see a basic example of splitting text in Excel using the TEXTSPLIT function.

This is text in Excel (a long one that puts out the various versions of Excel) 💻

Split text in columns

To split this text into columns where each Excel version is populated in a separate column, follow the steps below.

Step 1) Begin writing the TEXTSPLIT function.

Step 2) The first argument requires the text to be split. Refer to the cell containing the text.

Step 3) Specify a comma and a space character as the column delimiter by enclosing it in double quotation marks.

Each Excel version in the given text is separated by a comma and then a space character so that’s our column delimiter 🚚

Step 4) Leave all the other arguments omitted at the moment.

Step 5) Press Enter.

That’s phenomenal. All Excel versions are populated in separate cells, and a single-cell text now becomes spilled across a range of cells.

Split text in rows

To split the same text into rows where each Excel version is populated in a separate row, we will again write the TEXTSPLIT function but, with slight changes.

Step 1) Begin writing the TEXTSPLIT function.

Step 2) As the first argument, refer to the cell containing the text to be split.

Step 3) Omit the column delimiter argument.

Step 4) Specify a comma and a space character as the row delimiter by enclosing it in double quotation marks.

This time we don’t want to split across columns but rows so the col_delimiter argument stays omitted, and the delimiter is instead defined as the row delimiter 💡

Step 5) Leave all the other arguments omitted.

Step 6) Press Enter.

Wow! All text strings separated by a comma and a space character are split across multiple rows.

Split Text across Rows and Columns

If you have text within a cell that you want split across a two-dimensional array (columns and rows, both), here’s how you do it using the TEXTSPLIT function.

The image below is about the information of a student that’s populated within a single cell.

I want it split across multiple rows and columns such that all the field names (Student name, grade, and height) are split across rows. All the information against it (Sarah, IV, and 5’7) comes against these rows in a separate column.

This will make a two-dimensional array where the split text spills across multiple rows and columns both 🎯

To achieve this:

Step 1) Write the TEXTSPLIT function and give the cell reference A2 as the text argument.

Step 2) For the column delimiter, write a colon and a space character in double quotation marks. After the colon comes the field information and that is exactly from where we want the text split to the next column.

Step 3) For the row delimiter, write a comma and a space character in double quotation marks. After the comma comes, a new field starts and that is exactly where we want the text split to the next row.

Step 4) Perfect. Press Enter.

The student information is populated across rows and columns making it a two-dimensional array – just how we wanted it.

Pad the missing values

Padding missing values is a very well-thought feature of the TEXTSPLIT function that’s hard to understand unless you see it in action.

It will only be important in case you’re working out a two-dimensional array (like in the above section) 🧾

Continuing from the section above, if everything remains the same in the example above except for one difference. This time, we do not know the Grade of Sarah. Hence the grade field just stays empty, and the next field starts.

Now if you write the TEXTSPLIT function to split this data into a 2D array, this is what happens.

Instead of giving back a missing value that would have distorted the structure of the array, the TEXTSPLIT function returns the #N/A error when there are missing values in the source string.

This is because, in the above function, the pad_with argument is omitted, hence it defaults to the #N/A error 🗝

If you don’t want to see the #N/A error on your sheet, you can specify the value that you want instead as the pad_with argument of the TEXTSPLIT function.

See here.

Step 1) Rewrite the TEXTSPLIT function to specify the sixth argument (pad_with) as a hyphen “-“.

Step 2) Hit enter and the #N/A error will be replaced with a hyphen.

Step 3) You can also leave it vacant by supplying an empty string in place of the pad_aith argument.

Hope this explains how the pad_with argument comes in handy while splitting text.

Split text based on multiple delimiters

If you have a text like this in Excel:

That has multiple delimiters (some brands are separated by a comma and some by a semi-colon), how to use the TEXTSPLIT function to split it across columns?

Step 1) Write the TEXTSPLIT function as follows:

Step 2) For the column delimiter argument, start a curly bracket and start defining the delimiters (each enclosed in double quotation marks and separated by a comma).

Step 3) Press enter to see how TEXTSPLIT works with multiple delimiters.

You can specify as many delimiters as you want using the trick above.

Line break as the delimiter

Here’s a brain teaser for you. Up until now, we have seen multiple examples of splitting text using the TEXTSPLIT function.

The drill is simple, you give it some text and specify the delimiter within it which marks the point of splitting. The delimiter can be a text string a character, or even an array of characters.

Okay – but what do you do for data populated within a cell using line breaks?

I want the relevant fields like name, age, and city split across columns and the text strings John, 65, and NewYork split across columns.

Here’s to writing the TEXTSPLIT function to work this out.

Step 1) Start writing TEXTSPLIT and refer to cell A2 as the text argument.

Step 2) As the column delimiter, write a colon in double quotation marks.

As the rows are to be delimited using line breaks, what should we specify as the row delimiter?

Pro Tip!

The delimiter has to be a character or text string and to your surprise, no character defines a line break.

Does that mean we cannot split text based on line breaks? Not, we will work it around smartly.

The CHAR function is a special function of Excel that produces special characters in Excel against their code. For example, the code for line break is 10 so writing the CHAR function as

= CHAR (10)

Will return a line break.

We will use this function as the column/row delimiter.

Step 3) As the row delimiter, nest the CHAR function with the line-break code.

Step 4) Press enter to see the amazing results.

The field names are split across columns (delimited by a colon), and the name, age, and city are split across rows (delimited by a line break represented by the CHAR function).

Save the hack, you’d need it a lot 📝

Case-sensitive and case-insensitive text splitting

Case-sensitivity of the delimiter is an important aspect of text splitting, and the TEXTSPLIT function takes care of it well.

Why is it important?

Check this list of some items separated by the text string “vs.” 🆚

Let’s write the TEXTSPLIT function to split the text in this list by using the text string “vs.” as the delimiter.

Step 1) Write the TEXTSPLIT function and specify “vs.” as the delimiter.

Step 2) Press Enter and drag the results of this formula down.

The TEXTSPLIT function splits the text into two columns but, for some entries (the 2nd and the 4th one), the text is not split.

Why is that?

Pay close attention to see that the sentence case of “vs.” for both these entries is different than that of the delimiter supplied to the TEXTSPLIT function.

The “V” in both these entries is in the capital case whereas, the delimiter has a small case “v”.

The results of the TEXTSPLIT function are distorted only because of different text strings. You can help this situation by using the match_mode argument 👀

Step 3) Rewrite the TEXTSPLIT function and set the match_mode argument to 1.

Step 4) Hit enter and drag the results down.

This time TEXTSPLIT splits the text in the 2nd and the 4th entry, too (irrespective of the case of the delimiter).

Now you know when and why is the match_mode argument required 💭

Conclusion

Done with splitting texts in Excel? Hope you enjoyed learning about this modern function of Excel as much as I did researching it for you.

Splitting text in Excel is important. But as frequently you’ll need to split texts, you will have to join/merge it too. To your good, Excel offers multiple ways to merge multiple text strings within a cell.

My following Excel tutorials explain these methods. Give them a read here.

• How to Concatenate in Excel: CONCAT, Ampersand, and more
• How to Combine First and Last Names in Excel (2024 Guide)
• How to Use TEXTJOIN in Excel: Concatenate Cells (2024)