TEXTSPLIT function in Excel: split cells / text strings by delimiter

The tutorial shows how to use the brand-new TEXTSPLIT function to split strings in Excel 365 by any delimiter that you specify.

There may be various situations when you need to split cells in Excel. In earlier versions, we were already equipped with a number of instruments to accomplish the task such as Text to Columns and Fill Flash. Now, we also have a special function for this, TEXTSPLIT, that can separate a string into multiple cells across columns or/and rows based on the parameters that you specify.

Excel TEXTSPLIT function

The TEXTSPLIT function in Excel splits text strings by a given delimiter across columns or/and rows. The result is a dynamic array that spills into multiple cells automatically.

The TEXTSPLIT function takes as many as 6 arguments, only the first two of which are required.

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

text (required) - the text to split. Can be supplied as a string or cell reference.

col_delimiter (required) - a character(s) that indicates where to split the text across columns. If omitted, row_delimiter must be defined.

row_delimiter (optional) - a character(s) that indicates where to split the text across rows.

ignore_empty (optional) - specifies whether to ignore empty values or not:

  • FALSE (default) - create empty cells for consecutive delimiters without a value in-between.
  • TRUE - ignore empty values, i.e. don't create empty cells for two or more consecutive delimiters.

match_mode (optional) - determines case-sensitivity for the delimiter. Enabled by default.

  • 0 (default) - case-sensitive
  • 1 - case-insensitive

pad_with (optional) - a value to use in place of missing values in two-dimensional arrays. The default is a #N/A error.

For example, to split a text string in A2 into multiple cells using a comma and a space as the separator, the formula is:

=TEXTSPLIT(A2, ", ") TEXTSPLIT function to split text string in Excel

TEXTSPLIT availability

Currently, the TEXTSPLIT function is only available in the Office Insider Beta channel of Excel 365.

Tips:

  • In Excel versions where the TEXTSPLIT function is not available (other than Excel 365), you can use the Text to Columns wizard to split cells.
  • To perform the reverse task, i.e. to join the contents of multiple cells into one using a certain delimiter, TEXTJOIN is the function to use.

Basic TEXTSPLIT formula to split a cell in Excel

For starters, let's see how to use a TEXTSPLIT formula in its simplest form to split a text string by a particular delimiter.

Split a cell horizontally across columns

To split the contents of a given cell into multiple columns, supply a reference to the cell containing the original string for the first (text) argument and the delimiter that marks the point where the splitting should occur for the second (col_delimiter) argument.

For example, to separate the string in A2 horizontally by comma, the formula is:

=TEXTSPLIT(A2, ",")

For the delimiter, we use a comma enclosed in double quotes (",").

As a result, each item separated by a comma goes into an individual column: Splitting a cell horizontally across columns

Split a cell vertically across rows

To split text across multiple rows, the third argument (row_delimiter) is where you place the delimiter. The second argument (col_delimiter) is omitted in this case.

For instance, to separate the values in A2 into different rows, the formula is:

=TEXTSPLIT(A2, ,",") Splitting a cell vertically across rows

Please note that, in both cases, the formula is only entered in one cell (C2). In neighboring cells, the returned values spill automatically. The resulting array (which is called a spill range) is highlighted with a blue border indicating that everything inside it is calculated by the formula in the upper left cell.

Split text by substring

In many cases, the values in the source string are separated by a sequence of characters, a comma and a space being a typical example. To handle this scenario, use a substring for the delimiter.

For instance, to separate the text in A2 into multiple columns by a comma and a space, use the string ", " for col_delimiter.

=TEXTSPLIT(A2, ", ")

This formula goes to B2, and then you copy it down through as many cells as needed. Split cells by a substring

Split string into columns and rows at once

To split a text string into rows and columns at a time, define both delimiters in your TEXTSPLIT formula.

For example, to split the text string in A2 across columns and rows, we supply:

  • The equal sign ("=") for col_delimiter
  • A comma and a space (", ") for row_delimiter

The complete formula takes this form:

=TEXTSPLIT(A2, "=", ", ")

The result is a 2-D array consisting of 2 columns and 3 rows: Splitting a string into columns and rows at once

Split cells by multiple delimiters

To handle multiple or inconsistent delimiters in the source string, use an array constant like {"x","y","z"} for the delimiter argument.

In the screenshot below, the text in A2 is delimited by both commas (",") and semicolons (";") with and without spaces. To split the string vertically into rows by all 4 variations of the delimiter, the formula is:

=TEXTSPLIT(A2, , {",",", ",";","; "})

Or, you can include only a comma (",") and semicolon (";") in the array, and then remove extra spaces with the help of the TRIM function:

=TRIM(TEXTSPLIT(A2, , {",",";"}))

Splitting a string by multiple delimiters

Split text ignoring empty values

If the string to be split contains two or more consecutive delimiters without a value between them, you can choose whether to ignore such empty values or not. This behavior is controlled by the fourth ignore_empty parameter, which defaults to FALSE.

By default, the TEXTSPLIT function does not ignore empty values. The default behavior works nicely for structured data as in the below example.

In this sample table, the scores are missing in some strings. The TEXTSPLIT formula with the ignore_empty argument omitted or set to FALSE handles this case perfectly, creating an empty cell for each empty value.

=TEXTSPLIT(A2, ", ")

Or

=TEXTSPLIT(A2, ", ", FALSE)

As a result, all of the split values appear in the appropriate columns. Split strings creating empty cells for missing values.

In case your strings contain homogeneous data, it may stand to reason to ignore empty values. For this, set the ignore_empty argument to TRUE or 1.

For instance, to split the below strings placing each skill in a separate cell without gaps, the formula is:

=TEXTSPLIT(A2, ", ", ,TRUE)

In this case, the missing values between consecutive delimiters are ignored completely: Split text ignoring empty values.

Cell splitting case-sensitive or case-insensitive

To control the case-sensitivity of the delimiter, make use of the fifth argument, match_mode.

By default, match_mode is set to 0, making TEXTSPLIT case-sensitive.

In this example, the numbers are separated by the lowercase "x" and uppercase "X" letters.

The formula with the default case-sensitivity only accepts the lowercase "x" as the delimiter:

=TEXTSPLIT(A2, " x ")

Please pay attention that the delimiter has a space on both sides of the letter " x " in order to prevent leading and trailing spaces in the results. Case-sensitive TEXTSPLIT formula

To turn off case sensitivity, you supply 1 for match_mode to force the TEXTSPLIT formula to ignore the letter case:

=TEXTSPLIT(A2, " x ", , ,1)

Now, all the strings are split correctly by either delimiter: Case-insensitive TEXTSPLIT formula

Pad missing values in 2D array

The last argument of the TEXTSPLIT function, pad_with, comes in handy in case one or more values are missing in the source string. When such a string is split into both columns and rows, by default, Excel returns #N/A errors instead of the missing values not to mangle the structure of a two-dimensional array.

In the below string, there is no "=" (col_delimiter) after "Score". To keep the integrity of the resulting array, TEXTSPLIT outputs #N/A next to "Score". #N/A errors appear in the place of missing values.

To make the result more user-friendly, you can replace the #N/A error with any value you want. Simply, type the desired value in the pad_with argument.

In our case, that could be a hyphen ("-"):

=TEXTSPLIT(A2, "=", ", ", , ,"-") Pad missing values with a hyphen.

Or an empty string (""):

=TEXTSPLIT(A2, "=", ", ", , ,"") Show blanks for missing values.

Now that you've learned practical uses of each argument of the TEXTSPLIT function, let's discuss a couple of advanced examples that can help you cope with non-trivial challenges in your Excel spreadsheets.

Split dates into day, month and year

To split a date into individual units, first you need to convert date to text because the TEXTSPLIT function deals with text strings while Excel dates are numbers.

The easiest way to convert a numeric value to text is by using the TEXT function. Just be sure to supply an appropriate format code for your date.

In our case, the formula is:

=TEXT(A2, "m/d/yyyy")

The next step is to nest the above function in the 1st argument of TEXTSPLIT and enter the corresponding delimiter for the 2nd or 3rd argument, depending on whether you are splitting across columns or rows. In this example, the date units are delimited with slashes, so we use "/" for the col_delimiter argument:

=TEXTSPLIT(TEXT(A2, "m/d/yyyy"), "/") Splitting dates into day, month and year

Split cells and remove certain characters

Imagine this: you've split a long string into pieces, but the resulting array still contains some unwanted characters, such as parentheses in the screenshot below:

=TEXTSPLIT(A2, " ", "; ") The resulting array contains some unwanted characters.

To strip off the opening and closing parentheses at a time, nest two SUBSTITUTE functions one into another (each replacing one parenthesis with an empty string) and use the TEXTSPLIT formula for the text argument of the inner SUBSTITUTE:

=SUBSTITUTE(SUBSTITUTE(TEXTSPLIT(A2, " ", "; "), "(", ""), ")", "") Split cells removing unwanted characters

Tip. If the final array contains too many extra characters, you can purge them using one of the methods described in this article: How to remove unwanted characters in Excel.

Split strings skipping certain values

Supposing you want to separate the below strings into 4 columns: First name, Last name, Score, and Result. The problem is that some strings contain the title "Mr." or "Ms.", because of which the results are all wrong: Strings are split incorrectly because of excessive values.

The solution is not obvious but quite simple :)

In addition to the existing delimiters, which are a space (" ") and a comma and a space (", "), you include the strings "Mr. " and "Ms. " in the col_delimiter array constant, so that the function uses the titles themselves to separate the text. To ignore empty values, you set the ignore_empty argument to TRUE.

=TEXTSPLIT(A2, {" ",", ","Mr. ","Ms. "}, ,TRUE)

Now, the results are absolutely perfect! Split strings skipping certain values

That's how to split cells in Excel 365 with the help of the TEXTSPLIT function. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

TEXTSPLIT function to split strings – formula examples (.xlsx file)

You may also be interested in