Excel TEXTSPLIT Array Formula: Streamline Text Formatting

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

Working with Excel, you often encounter scenarios where you need to split text into separate values. Let’s look at a convenient alternative to tools like Power Query, Flash Fill, and Text to Columns. And one that you can perform with a simple and quick function.

This TEXTSPLIT array formula efficiently handles splitting text strings using a specific delimiter. This comes in handy when separating values present in a single cell into multiple cells, either by rows or columns. It elegantly simplifies your tasks and improves the clarity of your data.

Using TEXTSPLIT can save you both time and effort, as it allows you to quickly organize your data without having to go through multiple steps. As you delve further into this handy formula, you’ll discover the versatility and power it brings to your spreadsheet management.

So, let’s give it a try and experiment with an easier way to work with your Excel data!

Excel TEXTSPLIT Basics

You can customize the TEXTSPLIT formula with this syntax:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

The ‘text’ and ‘col_delimiter’ are required arguments. The ‘text’ denotes the text string you want to split, whereas the ‘col_delimiter’ specifies the delimiter to separate columns.

The optional arguments include:

row_delimiter: defines the delimiter for splitting your data into rows.

ignore_empty: if set to TRUE, empty cells between delimiters are ignored.

match_mode: controls how the delimiter is matched, or more specifically, the case-sensitivity.

pad_with: fills the cell with a specific value if the chosen delimiter is not found within the text.

Here’s a simple example using TEXTSPLIT with default arguments. Suppose you have the full name of a customer in cell A2, “John T. Washington”. You can apply the formula =TEXTSPLIT(A2,” “) to split the text into separate cells, dividing them whenever a blank space is found. This makes your data more manageable and easier to work with.

Basic TEXTSPLIT formula array

As you can see, the formula takes the original text and spills it out to the three cells in a new array.

More Examples of TEXTSPLIT Column Delimiters

Let’s explore a few more examples with different column delimiters to help you fully understand the versatility of the Excel TEXTSPLIT function.

Imagine you have a list of dates formatted as “11/6/2023” and you’d like to separate the month, day, and year into their own columns. In this case, you can use the slash as the delimiter. The formula would look like this: =TEXTSPLIT(A2,”/”).

textsplit a date

Just remember to include the delimiter in double-quotes. And if your original text is stored in a date format, you’ll have to change it. Otherwise, the TEXTSPLIT won’t work as you expected.

Now, suppose you have a list of email addresses, and you need to separate the username and domain. The “@” symbol serves as the delimiter. You can use this formula: =TEXTSPLIT(A2,”@”).

textsplit email address

Simple enough, right? Well, there’s even more you can do with TEXTSPLIT.

Using Row Delimiters

Let’s dive into using row delimiters with the Excel TEXTSPLIT function. Remember, this formula allows you to split a text string both vertically and horizontally!

When using the TEXTSPLIT function, the row delimiter is the third argument, indicated as [row_delimiter]. To use just the row delimiter, you can leave the col_delimiter argument empty. For example, assume you have a cell with the text “Morning,Noon,Night”, and you’d like to split it into separate cells. In this case, you would use the formula =TEXTSPLIT(A2,,”,”).

textsplit only rows

Notice the empty column argument represented with the two commas. 

TEXTSPLIT Array Formula with Multiple Delimiters

Now, let’s look at a case where you might want to use both column and row delimiters. Suppose the contents of a cell are “item1;$2.99,item2;$4.57,item3;$1.15,item4;$7.28”, representing various products and their cost. To split the list into separate columns and rows, you can use the following formula:

=TEXTSPLIT(A2,”;”,”,”).

textsplit rows and columns together

What happens is as Excel digests the data from left to right, the first semicolon between item1 and its price (and everything after it), tells TEXTSPLIT to split it into two columns.

Item1 ↔ $2.99,item2;$4.57,item3;$1.15,item4;$7.28

And when TEXTSPLIT comes along the first comma, it knows to split the first price and everything after into a new row.

Item1 ↔ $2.99

item2;$4.57,item3;$1.15,item4;$7.28

And this continues until the end of the text.

Now, what if the text you want to split has more than just one delimiter to identify? In that case, you would enter every variation for Excel to consider within braces.

Let’s say we have a small text string with five different currencies with different delimiters like this: “Dollar – Dime -Quarter,Nickel, Penny”. Now, you might think a hyphen and comma would do the trick, but not exactly.

The first two currencies are separated by a space, a hyphen, and another space, while the second and third currencies only have one space and a hyphen. Currencies three and four are separated by a comma, while the last delimiter is a comma, followed by a space.

Not a big difference, but if you don’t want to include any of those extra characters, you would have your TEXTSPLIT formula look like this:

=TEXTSPLIT(A2,,{” – “,” -“,”,”,”, “})

textsplit with multiple delimiters

While I only tackled the varying delimiters for splitting into rows, you would follow the same process if you wanted to split into multiple columns.

Ignore Empty Cells

When using the Excel TEXTSPLIT function, you may encounter situations where your data includes empty cells. To handle these cases more efficiently, the function offers the ignore_empty option. Here’s a quick overview of how it works.

By default, TEXTSPLIT will create an empty cell for each empty value in your data. However, you can modify the formula to ignore these blank spaces by using the ignore_empty argument. To do this, set the argument to TRUE in your formula.

Using a similar example as before, we’ll take all our currencies, this time only separated by a single comma. However, an additional comma has been added before the last value. If we execute the TEXTSPLIT as usual, we get a new blank row.

blank row in textsplit result

To correct this, if that’s your intention, use the ignore_empty option by setting it to TRUE. The output then ignores the empty text string.

ignore empty value

Applying this modified formula will produce a cleaner output as it skips the empty cells during splitting, ensuring you only get the useful data without any unnecessary gaps.

Handling Case-Sensitivity

Excel’s TEXTSPLIT function provides an optional argument to customize case sensitivity while searching for a delimiter. By default, the function searches for delimiters in a case-sensitive manner. But fear not! Adjusting the match_mode parameter can change the case sensitivity of the delimiter search.

For example, let’s say you have a string” 4 FT 0,5 ft 8,9 Ft 1″ and want to split it using the delimiter “ft”. The default case-sensitive search won’t split the string because “FT”, “Ft”, and “ft” are seen as different delimiters. That’s where the match_mode parameter comes into play.

If we split the columns by “ft” and split rows by comma, we’d get a return error.

error with case-sensitivity

To make the delimiter search case-insensitive, simply set the match_mode parameter to “1”.

set case-sensitivity to 1

Now, the function will treat “FT” and “ft” or any other variation as the same delimiter, resulting in the string being split correctly. So, with the match_mode option in your hand, you can easily manage the case sensitivity of delimiters to fit the needs of your data processing tasks.

Pad Return Errors

Finally, TEXTSPLIT has a last option to deal with inconsistencies in your data.

The pad_with argument allows you to specify a value that fills the missing elements when the size of the resulting arrays is uneven. By default, Excel pads missing values with empty strings (“”), but you still have to activate it. However, you can adjust this parameter to better suit your needs and display a specific character or value.

For example, we have the text string of “Holiday-Christmas,Birthday-July,Anniversay”. We’ll use TEXTSPLIT to separate columns by the hyphen, and rows by comma. But our last part of the text string has no hyphen, so the size of the TEXTSPLIT array will be uneven.

textsplit pad error

We can use the pad_with option to give us something other than a N/A error. If you were to simply enter three more commas before the closing parenthesis, that N/A would turn into a blank cell or a zero. But if we wanted something else to show, we’d have to enter it within quotes for that final argument.

completed pad with argument

Remember, this function can be a powerful tool when working with data in Excel. Make sure to utilize the pad_with argument to handle any inconsistencies in your spilled arrays and achieve the desired outcome in your worksheet.

Leave a Comment