Excel does not have a worksheet function named SPLIT. In Microsoft 365 and Excel 2024, the function that divides text at a chosen delimiter is TEXTSPLIT. The name SPLIT is standard in Google Sheets, which is one reason people search for a split function in Excel; the two spreadsheet programs use different function names and syntax.
What “split function” means
A split function takes a text value and separates it into smaller pieces at a boundary called a delimiter. A comma, space, hyphen, tab, or line break can serve as that boundary. For example, splitting the text red,green,blue at commas produces three text values: red, green, and blue.
“Split function” is a descriptive term for this kind of operation, not a slang expression or a single universal function name. The English verb split means to divide or separate. Software uses the same ordinary word for operations that break a string into parts, but the exact function name depends on the application or programming language. There is no one spreadsheet function called SPLIT that works identically in every product.
Does Excel have a SPLIT function?
No. In Excel, entering =SPLIT(...) does not call a built-in worksheet function called SPLIT. The corresponding modern Excel function is TEXTSPLIT. It is available in Microsoft 365 and Excel 2024, including current Microsoft 365 for Windows. It is also available in supported Microsoft 365 and Excel 2024 editions on Mac. It is not available in Excel 2021 and earlier perpetual versions; an older version may return a name error if a workbook uses it.
Google Sheets does have a built-in function named SPLIT. Its existence is a common source of the Excel-versus-Sheets mix-up. The names are related by what they do, not because Excel accepts the Google Sheets function name. When sharing a workbook or instructions, identify the program and use its own function name. A formula copied from Sheets may need to be rewritten as TEXTSPLIT for current Excel.
The name TEXTSPLIT is a descriptive Excel function name: it indicates that the function splits text. It should not be confused with a programming-language method or with Excel’s older Text to Columns command, which is a user-interface tool rather than a worksheet formula.
How TEXTSPLIT works
The basic syntax is TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]). The square brackets indicate optional arguments; they are part of the syntax description, not characters to type into a formula. The first argument is the text to divide. The second says where to split into columns. The optional third argument can specify a different delimiter for splitting into rows.
The last arguments let you control what happens with empty pieces, case-sensitive delimiter matching, and uneven rows. For a basic comma-separated value, you usually need only the text and column-delimiter arguments. TEXTSPLIT returns a dynamic array: Excel places the results in the neighboring cells automatically, rather than requiring a separate formula in each output cell.
For example, with the text North,South,West in cell A2, enter this formula in B2: =TEXTSPLIT(A2,","). The result spills across B2:D2 as North, South, and West. Leave the destination cells clear so the results have room to appear. If another value blocks the spill range, Excel displays a #SPILL! error.
Split text by commas, spaces, or other delimiters
A delimiter must match the text you want to divide. If A2 contains June|July|August, use a vertical bar as the delimiter, not a comma. With that text in A2 and the formula entered in B2, =TEXTSPLIT(A2,"|") returns three values across the row. A delimiter can contain more than one character, so a repeated symbol or a text marker can also be used when it occurs consistently in the source.
Spaces around the pieces are not necessarily removed as part of splitting. If A2 contains red, green,blue, splitting at a comma leaves a leading space before green. To remove ordinary leading and trailing spaces from each returned value, with that sample in A2, use =TRIM(TEXTSPLIT(A2,",")) in B2. The spilled result is red, green, and blue. TRIM also reduces repeated internal spaces to one, so consider whether that change is appropriate for your data.
For data separated by a space, set the delimiter to a space character. This can be useful for separating a simple first and last name, but it is not a reliable way to parse every name: names may contain middle names, multiple spaces, or family names with spaces. The formula can only follow the pattern you specify; it does not understand the meaning of the text.
Split text into rows as well as columns
TEXTSPLIT can use one delimiter to create columns and another to create rows. For instance, a comma can separate items within a row while a line break separates records. To split a line break into rows, supply an empty second argument and use CHAR(10) as the row delimiter.
Assume A2 contains three names separated by line breaks, and enter the formula in B2: =TEXTSPLIT(A2,,CHAR(10)). The result spills vertically into B2:B4, one name per row. The blank argument between the commas tells Excel that no column delimiter is being supplied. This pattern is useful for turning a cell containing a multi-line list into separate rows, provided the line breaks in the source are consistent.
When you supply both delimiters, the column delimiter determines divisions across a row and the row delimiter determines divisions down the result. If some resulting rows have fewer items than others, Excel may fill the unmatched positions with an error value by default. The optional pad_with argument can specify replacement content, such as an empty string, when that behavior is appropriate.
Common problems and limitations
- Wrong function name or version:
SPLITis not the Excel function name. UseTEXTSPLITin Microsoft 365 or Excel 2024; older Excel versions need another approach. - Delimiter mismatch: A comma formula will not divide text separated by semicolons or tabs. Check the actual source characters, including punctuation that may look similar.
- Unexpected blank results: Consecutive delimiters can represent empty pieces. TEXTSPLIT’s optional
ignore_emptyargument lets you choose whether to keep those empty pieces. Do not discard them automatically if their positions carry meaning. - Spill range blocked: Clear the cells where the result needs to appear. A merged cell or existing content in that range can prevent the dynamic array from spilling.
- Spaces remain: Splitting identifies the delimiter; it does not automatically clean every character around each piece. Use TRIM when its treatment of spaces suits the data.
- Text is not converted to the intended data type: A piece that looks like a date, number, or code may be returned as text. Check the resulting values and apply the needed conversion or formatting separately.
Another important limit is that a split is based on a repeated pattern, not on context. Splitting an address at every comma may break an address into more pieces than intended if commas also occur inside a component. Review a sample of the results before relying on them for a larger dataset.
TEXTSPLIT versus Text to Columns and Power Query
Use TEXTSPLIT when you want a formula-driven result that updates as the source cell changes and your Excel version supports dynamic arrays. It is especially handy for a small, regular list in a worksheet. The output is a spill range, so other formulas can refer to it and Excel recalculates it when the source changes.
For a one-time division of a selected column, Excel’s Text to Columns wizard may be a better fit. In Excel for Microsoft 365 on Windows, select the cells, then choose Data > Text to Columns. Follow the wizard to choose a delimited or fixed-width layout. This command writes results into worksheet cells rather than returning a dynamic array formula, so choose an empty destination area or risk overwriting existing data. Menu labels and placement can differ somewhat in Excel for Mac.
For repeatable imports or larger data-cleaning workflows, Power Query can split a column by a delimiter and refresh the transformation when data is loaded again. It is more suitable than hand-editing repeated imports, though it involves a query workflow rather than a cell formula. The right choice depends on whether the task is a live formula, a one-time worksheet operation, or a repeatable data import.
Frequently Asked Questions
What is the SPLIT function in Excel?
Excel has no built-in worksheet function named SPLIT. In Microsoft 365 and Excel 2024, use TEXTSPLIT to separate text by a delimiter. SPLIT is the corresponding function name in Google Sheets.
What is the Excel formula to split text?
Use TEXTSPLIT. For example, if A2 contains one,two,three, =TEXTSPLIT(A2,",") returns the three pieces across columns. The formula assumes current Microsoft 365 or Excel 2024.
Can TEXTSPLIT separate text into rows?
Yes. Use its row-delimiter argument. For line breaks, CHAR(10) is commonly used as the row delimiter; leave the column-delimiter argument blank when you do not need one.
Why does TEXTSPLIT return #SPILL!?
Excel cannot place the full dynamic-array result because one or more cells in the required output area are occupied or otherwise unavailable. Clear the spill range, then recalculate or re-enter the formula.
Can I use TEXTSPLIT in Excel 2021?
No. TEXTSPLIT requires Microsoft 365 or Excel 2024 under Microsoft’s supported version availability. In Excel 2021 or earlier, use Text to Columns for a manual split or Power Query for a repeatable transformation.
Related
- Tableau and R Integration: How It Works
- PowerPoint Bullseye Diagram Template: How to Make One
- Bullseye Glass Inventory Worksheet: Build One in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Copy a Formula in Excel to Other Cells SafelyTo copy a formula in Excel, select the cell that contains it, then drag its fill handle into…
- How to Extract One Word From a Cell in Excel With FormulasTo extract a particular word from text in Excel for Microsoft 365, put the text in A2 and…
- Date to Weekday in Excel: Formulas and FormattingTo turn a date into its weekday in Excel, use =TEXT(A2,"dddd") if the date is in A2; it…
- Excel Trim Based on Character: How to Remove TextTo trim text in Excel based on a specific character, use the LEFT, RIGHT, and MID functions combined…
- Excel Not Blank Criteria: COUNTIF, SUMIF, and Practical ExamplesIn Excel, use the not-blank criterion "<>" in functions such as COUNTIF, COUNTIFS, SUMIF, and SUMIFS. For example,…
- Import CSV Into Excel as Rows, Not Columns the Right WayTo import a CSV into Excel with each record on its own row, open Excel and choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.