When importing data from external software, CSV files, or messy web forms into Microsoft Excel, it is extremely common to find multiple pieces of information crammed into a single cell. The most frequent offender is names: a column filled with “John Smith” or “Doe, Jane” when you actually need one column for “First Name” and a separate column for “Last Name” to sort the data properly.
Historically, separating this data required using the clunky “Text to Columns” wizard, or writing incredibly complex formulas using LEFT, RIGHT, and FIND. Microsoft has permanently solved this problem by introducing a brilliantly simple new formula: TEXTSPLIT.
What is the TEXTSPLIT Function?
The TEXTSPLIT function is a “dynamic array” formula available in Microsoft 365 and Excel 2021. It looks at a single cell of text, searches for a specific character (like a space or a comma), and automatically cuts the text into separate pieces, “spilling” the results across multiple adjacent columns.
The basic syntax is:
=TEXTSPLIT(text, col_delimiter)
- text: The cell containing the messy data you want to split.
- col_delimiter: The specific character (the “knife”) you want Excel to use to cut the text apart.
Example 1: Splitting First and Last Names
Assume cell A2 contains the text Jane Doe. You want “Jane” in column B and “Doe” in column C.
The delimiter (the character separating the two words) is a single space.
- Click on cell B2 (the first empty cell next to the name).
- Type the following formula:
=TEXTSPLIT(A2, " ") - Press Enter.
Notice that the delimiter (the space) must be enclosed in double quotation marks. When you press Enter, Excel instantly puts “Jane” in B2 and automatically spills “Doe” into C2.
You can now click the bottom-right corner of cell B2 and drag it down to apply the formula to your entire list of hundreds of names in seconds.
Example 2: Splitting Data with Commas
Sometimes data is formatted differently. Assume cell A2 contains Smith, John, Manager. You need to split this into three columns: Last Name, First Name, and Title.
In this case, the delimiter is a comma followed by a space.
- Click on cell B2.
- Type the formula:
=TEXTSPLIT(A2, ", ") - Press Enter.
Excel will cut the text every time it sees a comma and a space, spilling “Smith” into B2, “John” into C2, and “Manager” into D2.
Handling Missing Data (Ignoring Empty Cells)
If your data is incredibly messy, you might have extra spaces, such as Jane Doe. By default, TEXTSPLIT will see those extra spaces as individual delimiters, resulting in blank columns between “Jane” and “Doe”.
To fix this, TEXTSPLIT has an advanced optional argument called ignore_empty.
The full syntax is: =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty])
To tell the formula to ignore extra spaces and avoid blank columns, you add the word TRUE to the fourth argument slot. Because we are skipping the third argument (row delimiter), we must leave a blank space between the commas:
=TEXTSPLIT(A2, " ", , TRUE)
With this formula, no matter how many accidental spaces are between the first and last name, Excel will cleanly split them into exactly two adjacent columns.