If you searched for “text.split”, you are almost certainly looking for the Excel function that splits text into separate cells. In Microsoft Excel, the function is written as TEXTSPLIT, and the search term text.split is simply how many people type it when they are trying to remember the name. This guide explains what text.split does, how its syntax works, and how to use it to split names, addresses, dates, and other messy text into clean columns without manual copying and pasting.
What “text.split” Means in Excel
The term text.split refers to the TEXTSPLIT function, a modern Excel function that takes a single text string and divides it into multiple values wherever a chosen delimiter appears. A delimiter is just a character that marks the boundary between pieces of data, such as a comma, a space, a semicolon, or a dash. For example, the text “Smith, John” can be split at the comma into “Smith” and “John”, each landing in its own cell.
Before text.split existed, Excel users relied on the Text to Columns wizard, a multi-step tool that still works but is slower and harder to repeat. The TEXTSPLIT function changed that by turning the whole job into a single formula that updates automatically. When the source text changes, the split results update too, which makes text.split ideal for reports, imported data, and dashboards that refresh regularly.
The text.split function is available in Microsoft 365 and Excel for the web, and it spills its results across neighbouring cells automatically. Spilling means you type one formula in one cell and Excel fills the cells to the right, or below, with the split pieces. You never have to drag the formula across, which saves time and reduces errors in large worksheets.
The text.split Syntax Explained
The full syntax of text.split looks longer than it really is, because most of its arguments are optional. In its simplest form you only need the text and one delimiter. The complete form is TEXTSPLIT(text, col_delimiter, row_delimiter, ignore_empty, match_mode, pad_with), and each argument has a clear job that is worth understanding before you start building formulas.
The text argument is the string you want to split, usually a cell reference such as A2. The col_delimiter is the character that separates values into columns, and the row_delimiter is the character that separates values into rows. The ignore_empty argument tells Excel whether to skip empty results, match_mode controls case sensitivity, and pad_with supplies a value for missing pieces when rows or columns come out uneven.
| Argument | What it does | Example |
|---|---|---|
| text | The string to split | A2 |
| col_delimiter | Character splitting into columns | “,” |
| row_delimiter | Character splitting into rows | “;” |
| ignore_empty | TRUE skips empty results | TRUE |
| match_mode | 0 for case-sensitive, 1 to ignore case | 1 |
| pad_with | Value for missing entries | “-“ |
How to Split Text Into Columns With text.split
Using text.split to split text into columns takes only a few steps. First, click the cell where you want the first result to appear, making sure the cells to its right are empty so the spill has room. Second, type a formula such as =TEXTSPLIT(A2, “, “) if your data is separated by a comma and a space. Third, press Enter and watch Excel spread the pieces across the row. That is the whole process, and it works the same way for thousands of rows if you copy the formula down.
A common real-world example is a full name stored as “Garcia, Maria” in one cell. The formula =TEXTSPLIT(A2, “, “) returns “Garcia” in one cell and “Maria” in the next. If the name is stored the other way round, as “Maria Garcia”, then =TEXTSPLIT(A2, ” “) splits on the space and gives the same two pieces in the opposite order. The text.split approach adapts to whatever separator your data actually uses, which is why it is so flexible.
Addresses are another classic use for text.split. An address such as “12 Beach Road, Miami, FL 33101” can be split at each comma into street, city, and state-plus-postcode. Dates written as “2026-10-26” split neatly at the hyphen into year, month, and day. Product codes such as “SHOE-BLUE-42” split at the hyphen into product, colour, and size. Once you start noticing delimiters in your data, you will find uses for text.split everywhere.
Practical text.split Examples
The fastest way to learn text.split is to study examples. The table below shows everyday formulas, the data they operate on, and the result they produce. Try each one in a blank worksheet to see the spill behaviour for yourself, and change the delimiter to match your own data.
| Goal | Formula | Result |
|---|---|---|
| Split a comma list | =TEXTSPLIT(A2, “,”) | Each item in its own column |
| Split first and last name | =TEXTSPLIT(A2, ” “) | First name, last name |
| Split into rows | =TEXTSPLIT(A2, , “;”) | Each item in its own row |
| Split and skip blanks | =TEXTSPLIT(A2, “,”, , TRUE) | No empty cells in the output |
| Split on two delimiters | =TEXTSPLIT(A2, {“,”, “;”}) | Splits on either character |
One of the most useful tricks with text.split is splitting on more than one delimiter at once. Passing an array such as {“,”, “;”} as the delimiter tells Excel to split wherever either character appears, which handles data that was typed inconsistently. Another handy trick is combining text.split with other functions: wrapping it in INDEX lets you pull out just the second piece, and combining it with TEXTJOIN lets you rebuild text in a new order.
The text.split function also handles rows as well as columns. If your data uses semicolons to separate records and commas to separate fields, you can pass both delimiters at once and Excel will build a small table in one step. That two-dimensional splitting is something the old Text to Columns wizard cannot do in a single pass, and it is a major reason power users have adopted text.split so enthusiastically.
text.split Versus Text to Columns
Text to Columns still has a place, especially for one-off cleanups on older Excel versions, but TEXTSPLIT wins on almost every other measure. A TEXTSPLIT formula is live, meaning it recalculates when the source changes, while Text to Columns produces static values that must be re-run after every edit. Formulas are also easier to audit, since anyone can click the cell and read exactly what the split does.
Text to Columns does have two advantages worth noting. It works in every version of Excel, including versions from before dynamic arrays existed, and it can write results over the original data in place. The TEXTSPLIT function, by contrast, needs Microsoft 365 or Excel for the web, and it needs empty spill space. If you share files with people on old Excel versions, Text to Columns or a pasted-values copy of your TEXTSPLIT results may be the safer choice.
For most modern workflows, the recommendation is simple: use TEXTSPLIT for anything that will be repeated or refreshed, and reserve Text to Columns for quick one-time fixes. Many analysts now build their imports around TEXTSPLIT formulas so that next month’s data cleans itself the moment it is pasted in.
Common TEXTSPLIT Errors and How to Fix Them
The most common TEXTSPLIT error is the spill error, shown as SPILL with an exclamation mark. It means something is blocking the cells where the results need to go, such as existing data, a merged cell, or a table boundary. Clear the obstructing cells and the formula will spill correctly. This error is easy to misread as a broken formula, but it is almost always a space problem, not a syntax problem.
Another frequent issue is the wrong delimiter. If your data uses a comma followed by a space but your formula splits only on the comma, every result will carry a leading space that breaks lookups and sorting. The fix is to include the full separator, such as “, “, in the formula, or to wrap the result in a trimming function. Similarly, invisible characters from copied web data can defeat TEXTSPLIT; pasting data as plain values first usually removes them.
A third issue is the CALC error, which appears when the formula cannot produce a valid result, often because an argument is the wrong type. Check that the text argument is genuinely text and not a number formatted as text, and that ignore_empty and match_mode are TRUE or FALSE and 0 or 1 respectively. Finally, remember that TEXTSPLIT is case-sensitive by default, so splitting on “x” will not match “X” unless you set match_mode to 1.
For more on working with text in documents and spreadsheets, see our guide to free text tools and our writing guide for clearer documents.
Frequently Asked Questions About TEXTSPLIT
What is TEXTSPLIT in Excel?
The search term TEXTSPLIT refers to the TEXTSPLIT function, which divides a text string into multiple values at a delimiter you choose, such as a comma, space, or semicolon, and spills the results into neighbouring cells.
Does TEXTSPLIT work in older Excel versions?
No. The TEXTSPLIT function requires Microsoft 365 or Excel for the web, because it relies on dynamic arrays. Older versions should use the Text to Columns wizard instead.
Can TEXTSPLIT split into rows instead of columns?
Yes. Leave the column delimiter empty and pass your separator as the row delimiter, for example =TEXTSPLIT(A2, , “;”), and the results will stack vertically.
Can TEXTSPLIT use more than one delimiter?
Yes. Pass an array of delimiters such as {“,”, “;”} and the function will split wherever any of them appears in the text.
Going Further With TEXTSPLIT
Once you are comfortable with the basics, TEXTSPLIT combines beautifully with the rest of Excel. Pair it with TEXTBEFORE and TEXTAFTER when you only need one piece of a string, or with FILTER to keep only the pieces that meet a condition. Analysts often use TEXTSPLIT inside larger formulas that parse email-style lists, tag fields, and survey responses, turning one messy column into a tidy dataset in a single step.
Another advanced pattern is splitting a whole column at once. Because TEXTSPLIT works with arrays, you can feed it a range and let it process many rows together, though the output layout needs care when rows produce different numbers of pieces. The pad_with argument exists for exactly this situation, filling short rows so the resulting table stays rectangular and ready for sorting, filtering, or pivot tables.
Performance is rarely a concern with TEXTSPLIT, even on thousands of rows, because the calculation engine handles spilling efficiently. The main thing to watch is workbook compatibility: anyone opening your file in an old Excel version will see errors instead of results. If compatibility matters, keep a values-only copy of your cleaned data alongside the live formulas.
Conclusion
The search term TEXTSPLIT points to one of the most practical functions in modern Excel. TEXTSPLIT turns messy, delimiter-packed text into clean columns and rows with a single live formula, replacing repetitive manual work and the static Text to Columns wizard for most jobs. Learn its syntax, watch out for spill space and delimiter details, and your spreadsheets will clean themselves the moment new data arrives.
