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 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 onetime 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:
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 SyntaxThe TEXTSPLIT function has the following 6 arguments in its syntax:
The first two arguments are required:
The remaining four arguments are optional:

Ex 1: Split Product DescriptionIn 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 FormulaHere 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 formula result starts in cell B4, with the product code, and spills into the next 2 columns:
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 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 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 AddressIn 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
TEXTSPLIT with CHOOSECOLS FunctionHere 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) CHOOSECOLS FunctionTo 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.
Get Columns 3 and 2To 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

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:
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
Table HeadingsBecause 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:
TEXTSPLIT with CHOOSECOLS FunctionHere 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 
Get the Practice FileTEXTSPLIT 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 
Last updated: January 13, 2023 9:20 AM