Excel does not have a worksheet function named SPLIT; in Microsoft 365, use TEXTSPLIT to divide text at a chosen delimiter. For example, if A2 contains Mira Chen | 18 | Seattle, enter =TEXTSPLIT(A2," | ") in B2 to return the three parts in neighboring cells. The function evaluates the text and delimiter, then spills the results into the worksheet.
What people mean by Excel’s split function
“Split function” is often a general name for a formula that separates text. It is also the name of a function in some other spreadsheet programs and programming languages. In current Excel, the built-in formula for splitting text is TEXTSPLIT. Searching for or entering =SPLIT(...) in an Excel worksheet will not call an Excel worksheet function of that name.
TEXTSPLIT is useful when information has been combined into one text value using a consistent separator. Typical examples include a full name separated by a space, a list of tags separated by commas, or fields in a line of imported data separated by a pipe. It is not a general-purpose way to infer where one piece of information ends and another begins: the delimiter must identify the boundaries you want.
TEXTSPLIT is available in Microsoft 365 and Excel 2024. The examples here use Excel for Microsoft 365 on Windows. On Mac, the formula itself works in supported Excel versions; ribbon and keyboard details can differ. Excel for the web may also support the function, but the instructions below focus on the Windows desktop app.
How TEXTSPLIT evaluates its arguments
The function takes the text to divide and one or more delimiter arguments. Its first delimiter separates items across columns; an optional second delimiter separates items into rows. Optional arguments control whether empty pieces are kept, whether delimiter matching is case-sensitive, and what to return when rows have different numbers of items.
- Text: the cell reference or text value to divide.
- Column delimiter: the character or text string that marks a split into adjacent columns.
- Row delimiter: optionally, a separator that starts a new output row.
- Ignore empty: optionally, TRUE skips empty pieces created by repeated delimiters; FALSE keeps them.
- Match mode: optionally, 0 matches the delimiter with case sensitivity and 1 ignores case. The default is 0.
- Pad with: optionally, a value to use where a row has fewer results than another row.
Excel evaluates the formula using the values in its referenced cells. If a referenced cell changes, the spilled results recalculate. The delimiter is a literal match, not a description of a pattern: a space is different from a comma, and a space followed by a pipe followed by a space is a three-character delimiter. Be exact when entering it.
Split a value into columns
To separate a line into neighboring cells, enter the formula in the cell where the first result should appear. Assume A2 contains the text Mira Chen | 18 | Seattle. With that value in A2, enter this formula in B2:
=TEXTSPLIT(A2," | ")
The result spills across B2:D2: Mira Chen, 18, and Seattle. The spaces on both sides of the pipe are part of the delimiter, so they are removed along with the pipe. If the formula used only "|", the adjacent spaces would remain attached to the returned pieces. You could remove surrounding spaces afterward with TRIM, but matching the complete separator is usually simpler.
Only enter the formula once, in the top-left cell of the intended output area. Do not copy it across the cells where results should appear; Excel creates a dynamic array and fills those cells automatically. A dotted outline may show the spill range when you select the formula cell.
Split text into rows or use more than one delimiter
A column delimiter creates columns; a row delimiter creates rows. For instance, a cell may contain a comma-separated list on each line, with a line break between lines. Assuming A2 contains red,green, then a line break, then blue,yellow, enter this in B2:
=TEXTSPLIT(A2,",",CHAR(10),TRUE)
Here the comma divides each line into columns, CHAR(10) identifies a line-feed character as the row separator, and TRUE ignores empty pieces. The result is a two-row by two-column array: red and green in the first row, blue and yellow in the second. This is handy for some imported or pasted text, although line breaks can vary between data sources. If a source uses a different line-break character, check the actual contents before assuming CHAR(10) is the right separator.
Some data uses multiple characters as one separator, such as two colons in item::quantity::location. Assuming that full string is in A2, this formula in B2 splits at each double colon:
=TEXTSPLIT(A2,"::")
Do not use a single colon unless it is genuinely the boundary marker. In a different data set, it could split a time such as 09:30 into pieces that were not intended to be separate fields.
Empty pieces, case, and uneven rows
By default, TEXTSPLIT preserves empty pieces. If A2 contains red,,blue, splitting on a comma returns three positions: red, an empty middle position, and blue. That blank is meaningful when repeated separators represent a missing field. If repeated delimiters are accidental and should not create blank results, set the fourth argument to TRUE.
Assuming A2 contains red,,blue, enter this formula in B2 to skip the empty item:
=TEXTSPLIT(A2,",",,TRUE)
The two commas after the quoted delimiter are intentional: they leave the optional row-delimiter argument blank so TRUE is supplied as the fourth argument, ignore_empty. This returns red and blue in two cells. Use the default behavior instead when the position of a missing value matters—for example, when each field corresponds to a fixed column in a data record.
When splitting on text whose capitalization can vary, the optional match-mode argument can matter. Its default is case-sensitive, so a delimiter written as END will not match end in that mode. Set match_mode to 1 when a case-insensitive match is needed. Also consider what the result means: ignoring case is appropriate only if those differently capitalized strings should be treated as the same separator.
When row and column delimiters produce rows with different numbers of items, the returned array may need padding. For example, one line might have two comma-separated values and another might have three. The optional pad_with argument lets you supply a replacement for missing positions rather than leaving the default error value. Decide whether a blank, a label, or another marker is most useful for the next step in your workbook.
What the results are—and are not
TEXTSPLIT returns an array of text pieces. A piece that looks like a number may still be text, which can affect arithmetic, sorting, comparisons, and charting. If A2 contains 18,25, this formula in B2 splits the values and converts each result to a number:
=VALUE(TEXTSPLIT(A2,","))
For the stated input, the two results are numeric 18 and 25, so they can be used in arithmetic. Conversion can fail if a piece contains nonnumeric characters, unexpected spaces, or a number format Excel does not recognize. Dates and decimal separators are especially sensitive to regional settings. Check the converted results instead of assuming that every text item has become the intended value.
Splitting also does not validate the meaning of fields. If a name contains a space, splitting on every space will create separate pieces for each word, not reliably identify first and last names. Likewise, a comma inside a quoted address can be mistaken for a delimiter by a simple split. For structured formats with quoting or escaping rules, a basic delimiter formula may be insufficient; use an import tool designed for that format or clean and inspect the source first.
Spill behavior and common problems
TEXTSPLIT is a dynamic-array function. The formula occupies one cell, while its output occupies a spill range. If any cell in that range already contains data, Excel cannot place the results and shows #SPILL!. Clear the obstructing cell, move the formula, or make room for the expected number of output rows and columns. Merged cells in the spill area can also prevent the array from appearing.
Other frequent issues include:
- Wrong delimiter: check for extra spaces, punctuation, or a different separator in some records.
- Unexpected blanks: decide whether empty fields should be retained or skipped, then set ignore_empty accordingly.
- One long result instead of several: confirm that the delimiter actually occurs in the source text and matches exactly.
- Formula name error: confirm that the installed Excel version supports TEXTSPLIT; older versions may not recognize it.
- Different output in another locale: formula argument separators in Excel can depend on regional settings. If commas produce a formula error, check the separators used by your Excel installation.
To insert the formula, select the destination cell and type it in the formula bar or directly in the cell. On Windows, press Enter to calculate it. If you need to locate the formula later, select a spilled result cell: Excel identifies the originating formula cell, and you can inspect or edit that formula there.
When to choose another method
TEXTSPLIT is a good fit for a formula-driven worksheet where the source text changes and you want the separated results to recalculate. It is also useful when you need to combine splitting into a larger formula. For example, a well-formed list can be split, transformed, and used by other dynamic-array formulas without manually repeating a fixed number of columns.
For a one-time task or a repeatable import-and-clean process, Power Query may be more suitable. In Excel for Windows, select the data and use Data > From Table/Range to open the Power Query Editor; select the text column and choose Home > Split Column, then select the delimiter-based option that fits the data. Menu labels can vary slightly by build. Power Query is designed to transform and refresh imported data, whereas TEXTSPLIT is a worksheet formula that returns results in cells.
For older Excel versions without TEXTSPLIT, alternatives include Text to Columns for a one-time split or formulas built from text functions such as LEFT, MID, FIND, and LEN. Those approaches can work, but are less convenient when the number of pieces varies. The right choice depends on whether you need a live formula result, a refreshable data-cleaning workflow, or a one-off rearrangement.
Frequently Asked Questions
Does Excel have a SPLIT function?
No worksheet function named SPLIT is built into current Excel. Use TEXTSPLIT in Microsoft 365 or Excel 2024 to separate text by a delimiter.
Why does TEXTSPLIT show #SPILL?
One or more cells needed for the output are not empty, or another layout constraint is blocking the array. Clear the destination area, including merged cells, or move the formula to an open area.
How do I split text into rows instead of columns?
Use the optional row_delimiter argument. For line breaks, CHAR(10) is a common choice; use it only when that character is actually present in the source text.
Can TEXTSPLIT keep blank fields?
Yes. Its default is to keep empty pieces. Set ignore_empty to TRUE if repeated delimiters should be skipped rather than represented by blank results.
Why are split numbers treated as text?
TEXTSPLIT returns text pieces, even when they look like numbers. Convert them with VALUE when appropriate, and check that the values and regional number formats are interpreted correctly.
Related
- What Is Balance in Accounting? Meaning and Examples
- Can You Play Split Fiction Solo? What to Know
- SQL ENUM Example: Syntax, Uses, and Alternatives
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF Not a Number? Count Text, Blanks, and ErrorsCOUNTIF can count cells that match a criterion, but it does not have a built-in criterion meaning “is…
- Conditional Formatting in Excel: If-Then Color RulesYes. In Excel, select the cells you want to format, then choose Home > Conditional Formatting and create…
- Using IFERROR in Excel: Examples and Common FixesUse Excel’s IFERROR function to return a value you choose when a formula produces an error: wrap the…
- DAX SUMIF: How to Sum Values by ConditionDAX has no standalone SUMIF function. To sum a column when a condition is true, create a measure…
- Combine Cells in Excel for Names: Formulas and StepsTo combine a first name in A2 and a last name in B2, enter =A2&" "&B2 in C2…
- Microsoft Excel RANK Formula: How to Rank ValuesIn Microsoft Excel for Microsoft 365, use RANK.EQ to rank a number against a list: with scores in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.