Excel SPLIT Function: Use TEXTSPLIT to Separate Text

Formulas & functions · Updated November 22, 2026

Excel does not have a function named SPLIT. In Microsoft 365 or Excel 2024, use TEXTSPLIT to divide text at a delimiter such as a comma, space, or slash. For example, with a comma-separated value in A2, enter =TEXTSPLIT(A2,",") in an empty cell to spill the pieces into separate columns. If TEXTSPLIT is unavailable, use Data > Text to Columns in Excel for Windows.

What You’ll Need

  • An Excel workbook with the text you want to separate. Make a copy of the source data first if it is important or difficult to recreate.
  • For the formula method, Microsoft 365 or Excel 2024. TEXTSPLIT is not available in Excel 2021 or earlier perpetual versions. Excel for the web also supports TEXTSPLIT.
  • A clear delimiter: the character or characters between the parts you want to separate. Common examples include a comma, a space, a tab, or a hyphen.
  • Enough blank cells beside or below the formula cell for the results to spill. Existing data in the spill area can prevent the formula from working.

Step-by-Step

  1. Check how the source text is arranged. Find a few representative cells and identify the character that marks each boundary. For example, Chen, Mira,North uses commas, while Chen Mira North uses spaces. Check whether every record follows the same pattern. If commas can also appear inside quoted descriptions, a simple delimiter split may divide those descriptions incorrectly, so inspect the data before choosing this method.
  2. Choose a destination. If the original values must remain intact, use a blank area or add a new worksheet. For the formula approach, select a blank cell in the first row where the split results should appear. Leave enough empty columns to the right for a horizontal split, or enough empty rows beneath for a vertical split. Do not put the formula inside an Excel Table if you expect its results to spill across multiple cells; place it in the worksheet grid outside the table.
  3. Enter the basic formula. With a comma-separated value in A2 and the formula going in B2, use =TEXTSPLIT(A2,","). Replace A2 with the actual cell containing your text, and replace the comma inside quotation marks with the delimiter in your data. Press Enter. In Microsoft 365 and Excel 2024, Excel places the parts in adjoining cells automatically; this is called a spill range. The formula remains in the first cell, while the neighboring cells show its results.
  4. Check the output before filling down. Compare the first split row with the original value. Confirm the pieces are in the intended order and that no part has been lost. If the split is correct, copy the formula down for other records: drag the fill handle at the lower-right corner of the formula cell, or copy it and paste into the matching rows. References such as A2 adjust to A3, A4, and so on. If the source is an Excel Table, a formula may fill down in the calculated column, but each row still needs room for its spilled output.
  5. Choose rows instead of columns when appropriate. TEXTSPLIT places results in columns by default. To split a line into separate rows, leave the column-delimiter argument blank and provide the row delimiter as the third argument. With line breaks in A2, for example, the formula is =TEXTSPLIT(A2,,CHAR(10)). The two commas before CHAR(10) indicate that the column delimiter is omitted. CHAR(10) represents a line-feed character often used for line breaks within an Excel cell.
  6. Decide how to handle repeated delimiters. Two delimiters together can create an empty part. For instance, red,,blue contains an empty middle field if the comma is the separator. With the value in A2, use =TEXTSPLIT(A2,",",,TRUE) to ignore empty results. The fourth argument, TRUE, tells Excel to skip empty pieces. Leave this option out if an empty field is meaningful, such as a missing middle value that you need to preserve.
  7. Use Text to Columns for a one-time conversion. In Excel for Microsoft 365 on Windows, select the source cells, then choose Data > Text to Columns. Choose Delimited and select Next. Tick the delimiter that matches your data, or choose Other and type a single character. Check the preview, select Next, and choose a destination if you do not want to overwrite the source column. Select a suitable column data format if needed, then select Finish. This changes worksheet cells into fixed values rather than creating a live split formula.
  8. Preserve the original data where needed. Text to Columns writes its output into the selected area and can overwrite existing cells. Before finishing, make sure the destination columns are clear or set a separate destination in the wizard. With TEXTSPLIT, retain the original column and use a new column for the formula. If you need to send the split results elsewhere as fixed values, copy the spilled cells and use Paste Special > Values.
  9. Adjust the formula for your actual delimiter. The delimiter is matched as text, so a multi-character separator can also be supplied, such as " | " for a space, vertical bar, and space. For data separated inconsistently by a comma or semicolon, TEXTSPLIT can accept multiple delimiters, but the exact formula depends on how your Excel regional settings handle array constants. If the simple single-delimiter formula works, start there and verify the result rather than adding complexity unnecessarily.

The formula is the better choice when the source may change and you want the split results to update automatically. Text to Columns is convenient for a one-time cleanup. On Excel for Mac, TEXTSPLIT uses the same formula; ribbon placement can differ slightly by release, so look under the Data tab for Text to Columns. In Excel for the web, use TEXTSPLIT for a formula-based split; if the desktop wizard is not available in your current browser experience, open the workbook in the desktop app for that workflow.

Common Mistakes

Looking for a function called SPLIT. Excel’s formula is TEXTSPLIT, not SPLIT. SPLIT is a function name used in some other spreadsheet apps, including Google Sheets, but it is not an Excel function. If you type =SPLIT(A2,",") in Excel, the result will be an unrecognized-name error. Use TEXTSPLIT in a supported Excel version or use Text to Columns for a manual conversion.

Using the wrong delimiter. A comma will not split text separated by a semicolon, and a space delimiter can split a full name into more pieces than intended. Inspect the source cells closely, including punctuation and spaces. A delimiter may consist of more than one character; if so, enter the complete sequence in the formula. In Text to Columns, the Other option accepts a single character, so a multi-character separator may be easier to handle with a formula or a prior cleanup.

Expecting spaces to disappear automatically. If the source is Chen, Mira, splitting at the comma leaves a leading space before Mira. That space is part of the text, not a separate delimiter to be silently removed. You can select the results and clean them separately, or wrap a suitable formula with TRIM when you have confirmed it behaves as needed for your data. TRIM removes ordinary extra spaces at the start and end, but it does not remove every possible invisible character, such as a nonbreaking space.

Overwriting important cells or blocking a spill. The TEXTSPLIT formula needs empty cells for its results. A value, another formula, or even a merged cell in the spill area can trigger a #SPILL! error. Text to Columns can replace content in the destination area without offering the same dynamic spill behavior. Use a blank destination and keep a backup if the source matters.

Splitting data that has no consistent structure. A single delimiter works best when every record follows the same pattern. If a field itself contains commas, a basic split may produce extra columns. Similarly, a space-separated address can have a variable number of parts. Check edge cases before applying the method to thousands of rows; use a more structured import or cleanup approach if delimiters have multiple meanings.

Tips

Test a few unusual records, not only the neatest example. Include a blank source cell, a value with a repeated delimiter, the shortest record, and the longest record. This catches unexpected empty fields and confirms that the spill area is wide or tall enough. If your results will be used for sorting or lookup, confirm that numbers and dates are interpreted as intended; a split may return text that looks like a number but is stored as text.

Keep the source column until you have checked the output and any downstream calculations. Give the new columns meaningful headings, such as First name, Last name, and Region, rather than leaving a row of unexplained values. If you need a static deliverable, copy the results and paste values into the destination after checking them. If you expect new source records or edits, keep the formula so Excel can recalculate the split.

For a delimiter that appears in several forms, standardize it first where practical. For example, records may use comma-space in some rows and semicolon-space in others. Replacing inconsistent separators with one consistent character before splitting can make the result easier to audit. Always review the replacement scope so you do not change punctuation that belongs inside a field. When the data arrives regularly from an external system, consider fixing the export format rather than repeating manual cleanup.

On Windows, common editing shortcuts include Ctrl+C to copy, Ctrl+V to paste, and Ctrl+Z to undo a recent worksheet action. On Mac, use Command+C, Command+V, and Command+Z. Undo is useful for a recent Text to Columns operation, but do not rely on it as a backup plan; save a copy before a bulk conversion. Formula syntax generally works the same on Windows and Mac, although argument separators can vary with regional settings. The examples here use commas as argument separators, as in the standard English-language Excel setup.

Troubleshooting

Excel shows #NAME? Check that the formula says TEXTSPLIT and that the Excel version supports it. TEXTSPLIT requires Microsoft 365 or Excel 2024. If the function is unavailable, update Microsoft 365 if your license allows it, open the file in a supported version, or use Data > Text to Columns in the desktop application.

Excel shows #SPILL!. Select the formula cell and look for the highlighted spill range. Clear any contents in that area, unmerge cells if necessary, or move the formula to a location with enough open cells. If the formula is inside a table, move it outside the table so the results can spill into neighboring worksheet cells.

The whole value stays in one cell. Confirm that the delimiter entered in the formula exactly matches the source, including spaces and punctuation. A formula splitting on a comma will not separate a value that uses a comma followed by a different character sequence if you included that extra character in the delimiter by mistake. Try a single-character delimiter first, then compare the result with the source text.

The output has blank pieces or too many columns. Repeated separators create empty fields unless you tell TEXTSPLIT to ignore them. Decide whether those blanks represent missing data or should be removed, then set the ignore-empty option accordingly. Too many columns may mean that the chosen delimiter also appears inside a field; review those records rather than discarding the extra pieces automatically.

Text to Columns puts values in the wrong place. Reopen the process only after checking the source and destination. In the wizard, confirm the Delimited option, the delimiter checkbox, and the preview. Set a destination cell in an empty area before finishing if the source must stay untouched. If you already replaced data, use Undo immediately if available or restore the saved copy.

Frequently Asked Questions

Does Excel have a SPLIT function?
No. Excel uses TEXTSPLIT for formula-based splitting in Microsoft 365 and Excel 2024. For a one-time split in desktop Excel, Text to Columns is another option.

Can I split text into rows instead of columns?
Yes. In TEXTSPLIT, provide the row separator as the third argument and leave the column separator blank. For a cell containing line breaks, CHAR(10) commonly represents the row separator.

Will TEXTSPLIT change the original cell?
No. The formula returns results in other cells and leaves the source value intact. Text to Columns, by contrast, writes converted values into the worksheet area you choose and can overwrite existing content.

Can I split a full name at the space?
Yes, if a space reliably separates the parts you want. A simple split can create more than two pieces for names with multiple given names or surnames, so check the format first and do not assume every person’s name has the same number of words.

Why does my formula return an error in an older Excel version?
Older perpetual versions do not include TEXTSPLIT. Use Text to Columns for a one-time conversion, or open the workbook in Microsoft 365 or Excel 2024. A workbook containing TEXTSPLIT may not calculate correctly for someone using an unsupported version.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.