Excel's TEXTSPLIT function splits a full name or a comma-separated list into separate cells with a formula, so the split updates whenever the source cell changes. Below are the formulas for columns, rows, multiple delimiters and empty values, plus the version check that trips most people up.
Check your Excel version first
TEXTSPLIT is not in every copy of Excel. Microsoft's TEXTSPLIT page lists it for Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024 and Excel 2024 for Mac. Excel 2021 and earlier are not on that list. The page also does not mention Excel for the web or the mobile apps.
If you are on Excel 2021 or older, this formula is not an option for you. Use Flash Fill instead: see How to Use Flash Fill in Excel (Ctrl+E) to Split or Join Names. Microsoft describes TEXTSPLIT as working like the Text-to-Columns wizard, but as a formula, so Text to Columns is the other non-formula route.
The TEXTSPLIT syntax
Here is the full syntax from Microsoft Support:
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
- text: the cell or string to split.
- col_delimiter: what to split on when the pieces should go across columns.
- row_delimiter: what to split on when the pieces should go down rows.
- ignore_empty: set this to TRUE to skip empty values.
- match_mode and pad_with: optional. Microsoft's page documents them, but this guide does not use them.
Square brackets mark the optional ones.
Split a full name into columns
- Put the name in A2, for example
Dakota Lennon Sanchez. This is the example Microsoft uses. - Click an empty cell with blank cells to its right, such as B2.
- Type this formula and press Enter:
=TEXTSPLIT(A2, " ")
The space in quotes is the column delimiter, so each word lands in its own column. The same formula works on any sentence. Microsoft's second example runs it on "To be or not to be".
Because the result spreads across several cells, keep the cells to the right of the formula empty. If something is already sitting there, the formula has nowhere to put its result.
Split a list down rows instead
To stack the pieces vertically, leave the column delimiter empty and put your delimiter in the third argument. Note the two commas in a row:
=TEXTSPLIT(A2,,".")
That splits A2 on each period and sends each piece down the column. Leave the cells below the formula empty.
Split into a grid with column and row delimiters
Use both delimiters together to build a small table from one cell. In Microsoft's example, commas separate columns and semicolons separate rows:
=TEXTSPLIT(A2,",",";")
In Microsoft's example, this produces a 2x3 array. Each semicolon starts a new row, and each comma starts a new column within that row.
Split on multiple delimiters at once
This is the part most people get wrong. You cannot list delimiters one after another. You have to wrap them in an array constant, which means curly braces with the items separated by commas.
To split on both a comma and a period, Microsoft's page gives:
=TEXTSPLIT(A1,{",","."})
The same idea works for a period and a hyphen:
=TEXTSPLIT(A2,{".","-"})
And it works in the row position too:
=TEXTSPLIT(A2,,{".","-"})
Skip empty values when delimiters sit side by side
When two delimiters are next to each other, such as a period followed by a hyphen, there is nothing between them. Excel can either return that as an empty piece or skip it. The fourth argument, ignore_empty, controls this.
To skip the empties, set it to TRUE:
=TEXTSPLIT(A2,,{".","-"},TRUE)
To keep them, set it to FALSE:
=TEXTSPLIT(A2,{".","-"},,FALSE)
If your split shows blank cells you did not expect, adding TRUE in the fourth position is the first thing to try. Count the commas carefully: ignore_empty is the fourth argument, so you need placeholders for any delimiter you skip.
When the formula does nothing at all
If Excel does not recognise TEXTSPLIT, go back to the version check at the top. On Excel 2021, 2019 or 2016, the function is not listed as available on Microsoft's page. A workbook that uses it will not behave the same for colleagues on those versions.
TEXTSPLIT is the inverse of TEXTJOIN. If you later need to glue the pieces back together, that is the function to look for. The Google Sheets TEXTJOIN guide covers the idea on the Sheets side.
