Excel does not have a worksheet function named SPLIT. In Microsoft 365 for Windows, use TEXTSPLIT to separate text with a formula; for a point-and-click option, use Data > Text to Columns. Excel’s VBA editor also has a separate Split function for macros.
Why “SPLIT” Can Mean Different Things in Excel
The word “split” describes a task—breaking text into pieces—but Excel offers different tools depending on where and how you want to do it. A worksheet formula recalculates when its source text changes. Text to Columns changes cells as a one-time operation. VBA’s Split function is used in Visual Basic for Applications code, not entered into a worksheet cell.
This distinction matters when looking up instructions. Guides for Google Sheets, programming languages, or Excel VBA may refer to a function called SPLIT or Split, but that does not mean the same name is available as a standard Excel worksheet formula. In a worksheet, Excel will generally report an unrecognized-function error if you try to enter =SPLIT(...). The closest built-in formula for splitting text in current Excel is TEXTSPLIT.
For example, if a cell contains a comma-separated record, a formula can return its parts in nearby cells. If you only need to divide a column once and keep the results as fixed values, Text to Columns may be simpler. If you are writing a macro, VBA’s function is the relevant option.
How to Access TEXTSPLIT in a Worksheet
In Excel for Microsoft 365 on Windows, select a blank cell where the first result can appear and type =TEXTSPLIT(. Excel’s formula autocomplete can show the function and its argument hints. You can also open Formulas > Insert Function and search for TEXTSPLIT. Enter the source cell and the delimiter, close the parenthesis, and press Enter.
With the text “red,green,blue” in A2, enter =TEXTSPLIT(A2,",") in B2. The result spills across the row: “red” in B2, “green” in C2, and “blue” in D2. You only enter the formula once. The cells to the right must be empty so Excel can display the spilled results.
TEXTSPLIT is available in Microsoft 365 and Excel 2024, but it is not included in every older Excel release. In particular, do not assume that an Excel 2021 installation has it. If a workbook containing the function is opened in an edition without support, the formula may show as an unsupported function. Check the Excel version and update status before relying on it in a workbook shared with others.
Excel for Mac has the same general formula approach when the installed edition supports TEXTSPLIT. The function is also distinct from a menu command: you do not need to enable an add-in or turn on a special setting to use it in a supported version.
What TEXTSPLIT Does and How Its Arguments Work
TEXTSPLIT separates a text value at a delimiter you specify. The delimiter is the character or text sequence marking each boundary: a comma, space, hyphen, slash, or another consistent separator. Its basic form is TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]). The square brackets in this description mean an argument is optional; do not type the brackets as part of a formula.
textis the text or cell reference to divide.col_delimitersplits the text into results across columns.row_delimiter, when supplied, splits at a second delimiter into separate rows.ignore_emptycontrols whether adjacent delimiters produce empty results.match_modecontrols whether delimiter matching is case-sensitive.pad_withsupplies a value for missing positions when the result has an uneven shape.
Most basic uses need only the first two arguments. If the source contains line breaks or uses two types of separators, the optional arguments can help, but test the result on representative data before filling a formula down a large range. A delimiter is literal: splitting on a comma does not automatically treat a semicolon as equivalent.
TEXTSPLIT returns a dynamic array. That means the output occupies as many cells as needed and can resize if the source text changes. It is not the same as a formula copied into each output cell. Keep the destination area clear, and avoid putting the formula inside an Excel Table if you expect a multi-cell spill; dynamic array formulas have limitations inside table columns.
Practical Examples for Common Text
A frequent job is splitting a list at commas while removing the spaces that follow them. With the text “North, West, 12” in A2, enter =TRIM(TEXTSPLIT(A2,",")) in B2. The result is “North” in B2, “West” in C2, and “12” in D2. TRIM removes leading and trailing ordinary spaces from each returned item, which avoids leaving “ West” with a stray leading space.
For a name stored as “Morgan Lee” in A2, the formula =TEXTSPLIT(A2," ") separates the text at each space. This works well for a simple two-part name, but names can contain middle names, multiple spaces, or compound surnames. Splitting is mechanical; it does not understand which word is a first name or family name. Inspect the data and decide how to handle exceptions rather than assuming every person’s name has the same structure.
For a code such as “INV-2048” in A2, use a hyphen as the delimiter: =TEXTSPLIT(A2,"-"). The result is “INV” and “2048” in neighboring cells. The second piece is text returned by the split; if it must be used in arithmetic, convert it to a number deliberately and confirm that all records follow the expected pattern.
These formulas are useful when the original value may change and you want the separated results to update automatically. If the source is imported data with inconsistent separators, blank pieces, or text that itself contains the delimiter, clean or review those records first. No splitting function can infer the intended structure in every irregular string.
When Text to Columns Is a Better Fit
For a one-time split, Text to Columns is a straightforward alternative that does not require a formula. Select the cells in the source column, then choose Data > Text to Columns. In the wizard, choose Delimited, select the delimiter that matches the data, review the preview, and finish. Excel places the pieces into columns rather than returning a live formula result.
Take care with the destination. If the adjacent columns contain information, the split output can overwrite it. Choose a suitable destination in the wizard or move the source data to an empty area first. Also review the preview before finishing: dates, leading zeros, and long digit strings may be interpreted or reformatted in ways that are not appropriate for identifiers. For postal codes or product codes, preserving the original text may be more important than treating the pieces as numbers.
Text to Columns is useful in versions that lack TEXTSPLIT and for data that only needs to be separated once. It does not keep the output linked to the original cell. If the original text later changes, you must repeat the operation or use a formula-based method.
Using VBA’s Split Function
Excel’s VBA Split is a programming function, not a worksheet formula. To access it, open the Visual Basic Editor with Alt+F11 on Windows, choose Insert > Module, and place code in the module. VBA’s Split takes a string and a delimiter and returns an array of pieces. Array positions start at zero, so the first item is element 0, not element 1.
For example, this small macro splits a sample string at each vertical bar and prints the second item in the Immediate window:
Sub ShowSplitExample()
Dim parts As Variant
Dim secondPart As String
' Split the sample text at each vertical bar.
parts = Split("red|green|blue", "|")
secondPart = parts(1)
Debug.Print secondPart
End Sub
The printed result is “green.” A macro can use the returned array to process data, but it requires VBA code and a macro-enabled workflow; it is not a replacement for a cell formula when users simply need a visible, recalculating result. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. On Mac, the Visual Basic Editor and VBA support are available in supported Excel editions, though the keyboard shortcut for opening the editor can differ; use the Developer tab or Excel’s menu commands if Alt+F11 is not applicable.
Common Problems and How to Fix Them
- #NAME? or an unsupported-function message: Check that the formula says
TEXTSPLIT, notSPLIT, and confirm that Excel supports the function. Older installations may need an update or a different method. - #SPILL!: One or more cells in the expected output area are occupied, merged, or otherwise blocking the array. Clear the obstructing cells or move the formula to an open area.
- Unexpected spaces: A comma followed by a space leaves that space attached to the next item. Wrapping TEXTSPLIT in TRIM can remove ordinary leading and trailing spaces from results.
- Missing or extra pieces: Confirm the delimiter matches the actual data. A comma, semicolon, tab, and line break are different characters, even if the source looks like a list.
- Unexpected blank results: Repeated delimiters can create empty pieces. Review whether those blanks are meaningful and use the optional empty-value setting only when discarding them is appropriate.
Also remember that a delimiter can appear inside a legitimate field. For instance, a comma inside a quoted address or description may not be a true field boundary. A simple text split does not automatically parse every structured file format. If the data is a CSV export with quoting rules, use Excel’s import tools and inspect the parsing options rather than treating every comma as a separator.
Frequently Asked Questions
Does Excel have a SPLIT function?
No worksheet function with that exact name is available in standard Excel. Use TEXTSPLIT in supported versions, Text to Columns for a one-time operation, or VBA’s Split function in code.
Where do I find TEXTSPLIT in Excel?
There is no separate button to turn it on. Enter =TEXTSPLIT( in a cell, or search for TEXTSPLIT through Formulas > Insert Function. It requires Microsoft 365 or Excel 2024.
Can TEXTSPLIT separate text into rows instead of columns?
Yes. TEXTSPLIT has an optional row-delimiter argument. The first delimiter normally divides into columns; providing a row delimiter lets you define a second kind of boundary that returns values on separate rows.
Why does my split formula show #SPILL!?
The formula needs neighboring cells for its results, and something is blocking that area. Clear the cells where the results should appear or move the formula to a larger empty area.
Should I use TEXTSPLIT or Text to Columns?
Use TEXTSPLIT when you want a formula-driven result that can update with its source. Use Text to Columns when you want to split selected data once and keep the resulting values without a live formula connection.
Related
- Split by Position in Power Query: A Practical Guide
- TinyTask for Sol’s RNG: What It Can and Can’t Do Safely
- Java split() Method: Split Strings with Regex and Limits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Working Hours Formula: Calculate Time WorkedIf A2 contains a shift start time and B2 its end time on the same day, enter =(B2-A2)*24…
- Divide by Half in Excel: Formulas and StepsTo divide a value by one half in Excel, use a formula such as =A2/0.5; this doubles the…
- Weighted Average Rate in Excel: Formula and ExamplesTo calculate a weighted average rate in Excel, multiply each rate by its corresponding weight, add those products,…
- Excel LEN Function: Count Characters in Cells EasilyTo count the characters in a cell, put the text in A2, select another cell, and enter =LEN(A2).…
- Excel IFS List: Syntax, Examples, and Common FixesExcel’s IFS function checks conditions in order and returns the result paired with the first condition that is…
- Excel Date and Time: Formatting, Formulas, and FixesExcel stores dates as serial numbers and times as fractions of a day, so you can format them…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.