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

Excel TEXTSPLIT function separates text into columns

TEXTSPLIT Function

If you're using Excel 365, TEXTSPLIT is one of the new Excel 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 into separate cells, instead of a one-time operation, that doesn't update if the original data changes.

In this short video, I show a simple example of using the Excel TEXTSPLIT function, to separate text into 3 separate columns. Also, you'll see how to avoid the #SPILL! error, if you use TEXTSPLIT in a named Excel table.

How Could You Use TEXTSPLIT?

The TEXTSPLIT function is in the Text Function category of Excel functions. It is a dynamic array function (spill function)

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 functions. See the examples for the old way to split text on these pages:

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

--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]) 

A) The first two arguments are required:

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

B) The remaining four arguments are optional:

  • [row_delimiter]- Character that marks where split should occur, down rows
    • If row delimiters are 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

Tip: Get more details on the TEXTSPLIT function on its Microsoft help page.

Ex 1: Split Product Description

In the screen shot below, there is a product list on the worksheet (NOT in a named table). The full description is in column A in each row.

In this example, we'll use TEXTSPLIT to split each product description into separate columns on a worksheet -- Code, Item, and Size.

Excel TEXTSPLIT function separates text into columns

Product List Notes

The the product list shown above:

  • 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

To split the product information into separate columns, a TEXTSPLIT formula was entered in cell B4, and copied down to row 12

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.

Excel TEXTSPLIT function separates text into columns

Formula Result Spills Across

The TEXTSPLIT formula result starts in cell B4, with the product code, and spills into the next 2 columns, based on the specified delimiter:

  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 in 3 different columns - B4 to D4

Warning: 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 example 3, shown 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.

Excel TEXTSPLIT function error in named 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.

Excel TEXTSPLIT function gets city and state

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"

Excel TEXTSPLIT function gets city and state

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)

No Error

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

Split Text - Different Separators

For text that has more than one type of separator, you can use the TEXTBEFORE and TEXTAFTER functions, to separate the items. For example, get the text before the first space, or get text after the last comma.

In the download section, the 2nd sample file shows how to split an address that has a hyphen, a comma and a space character as separators.

get the street address using hyphen comma space separators

Get the Practice Files

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

Split Address Excel 365: In Excel 365, use the new text functions for an easier way to separate a full address into columns. Download this file to see formulas with TEXTBEFORE, TEXTAFTER, TEXTSPLIT and CHOOSECOLS functions. The zipped file is in xlsx format, and does not contain any macros

More Text Functions

Combine Text/Numbers

TEXTJOIN Function Examples

Names, Split/Reverse First and Last

Spill Function Examples

FORMULATEXT Function

CODE and CHAR functions

ADDRESS Function

 

 

Last updated: February 8, 2024 2:55 PM