Home > Formulas > Text > TEXTSPLIT Excel TEXTSPLIT Function ExamplesSplit 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 FunctionIf 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 onetime 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:
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: 
TEXTSPLIT SyntaxThe TEXTSPLIT function has the following 6 arguments in its syntax:
A) The first two arguments are required:

B) The remaining four arguments are optional:

Tip: Get more details on the TEXTSPLIT function on its Microsoft help page.
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.
The the product list shown above:
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
The second argument, ", ", sets the column delimiter as a comma and space.
The TEXTSPLIT formula result starts in cell B4, with the product code, and spills into the next 2 columns, based on the specified delimiter:
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 TableBecause 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. 
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:
From the full address, we need formulas to return two address parts  State and City
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:
First, the TEXTSPLIT function splits the full address from cell A4 into an array of 4 columns, based on the comma & space delimiter:
We need to get columns 3 (State) and 2 (City)
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.
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
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:
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
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:
Here is the formula in cell B4, to return one column from the full address, based on the number typed in cell B1
Next, here is the formula in cell C4, to return one column from the full address, based on the number typed in cell C1
Because each formula returns a single column, the formula results will NOT show a #SPILL! error
Split Text  Different SeparatorsFor 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 Practice FilesTEXTSPLIT 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 
Names, Split/Reverse First and Last
Last updated: February 8, 2024 2:55 PM