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
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:
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:
The 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 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):
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 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 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:
From the full address, we need formulas to return two address parts -- State and City
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:
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.
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
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
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:
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
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
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
Last updated: January 13, 2023 9:20 AM