Home > Formulas > Text > TEXTSPLIT

Excel TEXTSPLIT Function Examples

Split address or other text strings with Microsoft Excel TEXTSPLIT function. Formula examples, practice file. Similar to Excel Text to Columns feature, but with formulas. Available in Excel 365

TEXTSPLIT Function

If you're using Excel 365, TEXTSPLIT is one of the new text functions that make it easier to separate a full address, or other text strings, into columns, using formulas.

TEXTSPLIT is like the Excel Text to Columns feature, but it uses formulas to split the text, instead of a one-time operation, that doesn't update if the original data changes.

This short video, by the Mike Tholfsen, from the Microsoft Education team, shows 3 simple examples of using the Excel TEXTSPLIT function.

How Could You Use TEXTSPLIT?

The TEXTSPLIT function is in the Text Function category of Excel functions.

You can use the TEXTSPLIT function to return an array of columns or rows. For example:

1. Separate a full address into columns with street, city, state, zip code
2. Return only the city and state from a full address

These examples are shown in the sections below

Note: In older versions of Excel, you can split text with complex formulas that use a combination of functions like LEFT, RIGHT, FIND, SEARCH, and SUBSTITUTE. See the examples on these pages:

--a) Names, Split/Reverse First and Last

--b) Address, Split with Formulas

TEXTSPLIT Syntax

The TEXTSPLIT function has the following 6 arguments in its syntax:

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

The first two arguments are required:

• text - Text the formula should split
• col_delimiter- Character that marks where split should occur, across columns
  • Can be left blank (with placeholder), if row_delimiter is used

The remaining four arguments are optional:

• [row_delimiter]- Character that marks where split should occur, down rows
  • If not used, and valid match not found, #N/A error is returned
• [ignore_empty]- Ignore consecutive delimiters - TRUE or FALSE
  • If not entered, the default of FALSE is used, to create an empty cell
• [match_mode]- For case-insensitive match, use 1
  • If not used, default match mode (0) is used - case-sensitive match
• [pad_with]- Value to use to pad the formula result
  • This only applies if you use both a row and column delimiter.
  • Formula works out maximum number of column delimiters across all rows, and pads out the columns of any rows with less than the maximum number of column delimiters, using the pad_with value

Ex 1: Split Product Description

In this example, we'll use TEXTSPLIT to split a product description into columns on a worksheet

In the screen shot below, there is a product list on the worksheet (NOT in a named table):

• TEXTSPLIT formula was entered in cell B4, and copied down to row 12
• Product descriptions are entered in cells A4 to A12
• Product descriptions are in a consistent format:
  • Product code, product name, and product size
  • All separated by a comma & space character

TEXTSPLIT Formula

Here is the formula in cell B4, with a reference to the full product description in cell A4

• =TEXTSPLIT(A4,  ", ")

The second argument, ", ", sets the column delimiter as a comma and space.

The formula result starts in cell B4, with the product code, and spills into the next 2 columns:

1. product code, JK001, is in cell B4
2. product name, Sweater, is in cell C4
3. product size, Small, is in cell D4

In the screen shot above, cell B4 is selected, and there is a thin blue border around the cell with the results - B4 to D4

Spill Error in Excel Table

Because this TEXTSPLIT formula returns multiple columns, the product list and formula cannot be in a named Excel table, or it will show a #SPILL! error.

In one of the examples below, you'll see how to return a SINGLE column from a TEXTSPLIT formula.

That type of formula, which doesn't try to spill into adjacent columns, WILL work correctly in a named Excel table.

Ex 02: Get State and City From Address

In this example, there is an address list in column A, starting in cell A4. The list is on the worksheet, but it is NOT in a named table.

Each full address has four parts, separated by a comma and space:

• Street, City, State, Zip Code

From the full address, we need formulas to return two address parts -- State and City

• Note: because this formula returns multiple columns, the data cannot be in a named Excel table, or it will show a #SPILL! error.

TEXTSPLIT with CHOOSECOLS Function

Here is the formula to return only two columns from the full address, using TEXTSPLIT combined with the CHOOSECOLS function -- another one of Excel new functions:

• =CHOOSECOLS(TEXTSPLIT(A4, ", "), 3, 2)

First, the TEXTSPLIT function splits the full address from cell A4 into an array of 4 columns, based on the comma & space delimiter:

• 1) Street  2) City   3) State   4) Zip Code

We need to get columns 3 (State) and 2 (City)

CHOOSECOLS Function

To get specific columns from an array, you can use the CHOOSECOLS function. It returns a specific column, or multiple columns, from a range or array of columns.

Here are the function syntax arguments for the CHOOSECOLS function, with 2 required arguments, and optional arguments for additional column numbers.

• =CHOOSECOLS(array, col_num1, [col_num2],...)

Get Columns 3 and 2

To get columns 3 and 2 from the TEXTSPLIT result, the CHOOSECOLS function has the 2 required arguments, and one optional argument, for a second column

• =CHOOSECOLS(TEXTSPLIT(A4, ", "), 3, 2)

Ex 03: Get Specific Columns (Flexible)

In this example, there is a named Excel table, with an address list in column A, starting in cell A4.

Like the previous example, the full address has four parts, separated by a comma and space:

• Street, City, State, Zip Code

From the full address, we need two formulas that will each return a single part of the address -- based on the column numbers typed in cell B1 and cell C1

• Note: because each formula returns a single column, the formulas can be in a named Excel table, as shown in the screen shot below. The single-column formula results will NOT create a #SPILL! error.

Table Headings

Because the results for these two formulas can show any of the address parts, I changed two of the heading cells from specific address parts (State and City), to generic headings:

• cell B3 -- "AddrA"
• cell C3 -- "AddrB"

TEXTSPLIT with CHOOSECOLS Function

Here is the formula in cell B4, to return one column from the full address, based on the number typed in cell B1

• =CHOOSECOLS(TEXTSPLIT([@FullAddress], ", "), B$1)

Next, here is the formula in cell C4, to return one column from the full address, based on the number typed in cell C1

• =CHOOSECOLS(TEXTSPLIT([@FullAddress], ", "), C$1)

Because each formula returns a single column, the formula results will NOT show a #SPILL! error

Get the Practice File

TEXTSPLIT Examples: In Excel 365, use the new TEXTSPLIT function for an easier way to separate a text string into columns. Download this file to see examples for TEXTSPLIT function from this page. The zipped file is in xlsx format, and does not contain any macros

More Text Functions

Combine Text/Numbers

Names, Split/Reverse First and Last

FORMULATEXT Function

CODE and CHAR functions

ADDRESS Function