To split text in Excel for Microsoft 365 or Excel 2024, put the original string in A2 and enter =TEXTSPLIT(A2,",") in an empty cell. If A2 contains Mira,28,Seattle, the formula spills Mira, 28, and Seattle into adjacent cells. TEXTSPLIT separates text wherever it finds the delimiter you specify; for older Excel versions, use Text to Columns instead.
What You’ll Need
- An Excel workbook and a cell containing the text you want to divide.
- A clear delimiter: the character or text marking where one piece ends and the next begins, such as a comma, space, hyphen, or line break.
- Excel for Microsoft 365 or Excel 2024 to use the TEXTSPLIT formula. It is also available in Excel for the web and supported Microsoft 365 versions for Mac. If TEXTSPLIT is not recognized in your Excel version, use Text to Columns or check whether your Excel is up to date.
- Blank cells where the separated results can spill. Leave enough space to the right for a horizontal split or below for a vertical split.
Step-by-Step
- Check how the text is put together. Find a representative entry and identify the exact separator. For example,
Jordan Lee|Sales|Torontouses a vertical bar, whileJordan Lee, Sales, Torontouses commas. Check whether separators are consistent and whether there are spaces after them. This matters because TEXTSPLIT looks for the delimiter you provide; it does not automatically guess whether a comma, space, or other character marks a boundary. - Choose where the results should go. If your source text is in A2, select an empty cell in the same row, such as B2, for results that should spread across columns. Make sure the cells to the right are empty. If they contain values, Excel cannot spill the result and may show a
#SPILL!error. For data that should run down a column instead, use the row-delimiter option described in step 5. - Enter TEXTSPLIT with the right delimiter. With the source text in A2 and the delimiter being a comma, enter
=TEXTSPLIT(A2,",")in B2. If A2 containsMira,28,Seattle, the output occupies B2:D2 as Mira, 28, and Seattle. Replace the comma inside the quotation marks with the actual delimiter in your data. For a pipe-separated value, for example, use|between the quotation marks. Keep the quotation marks: they tell Excel that the delimiter is text. - Review the spill range before copying anything. A dynamic-array formula returns all split parts from the cell where you entered it, spilling them into nearby cells. The formula itself appears only in B2; the other results are connected to it. Do not type into those output cells or put another formula in the spill range. If you need to split more source rows, copy the formula cell down one row per record, or use the fill handle. Each copied formula will refer to the corresponding source cell, such as A3, A4, and so on.
- Split on more than one possible delimiter if needed. TEXTSPLIT can treat a list of delimiters as alternatives. With the full string in A2 and either a comma or semicolon separating the fields, enter
=TEXTSPLIT(A2,{",",";"})in B2. For example,red,green;bluereturns red, green, and blue in adjacent cells. This is useful when imported entries use inconsistent punctuation. In some regional settings, Excel uses different list separators in formulas and array constants, so use the separators expected by your Excel installation if the formula is rejected. - Remove spaces left beside delimiters. TEXTSPLIT keeps spaces that are part of the text. If A2 contains
Mira, 28, Seattle, splitting on a comma leaves a leading space before 28 and Seattle. With the source text in A2 and commas between its fields, enter=TRIM(TEXTSPLIT(A2,","))in B2 to remove extra spaces at the beginning and end of each returned part. TRIM does not remove spaces inside a name such as Jordan Lee. If spaces are meaningful in your data, inspect the results before using this cleanup. - Choose whether repeated delimiters should create blank results. By default, consecutive delimiters mark an empty field. If A2 contains
red,,blue, then=TEXTSPLIT(A2,",")in B2 returns red, a blank part, and blue. With the same source layout, enter=TEXTSPLIT(A2,",,,TRUE)to ignore empty parts and return red and blue next to each other. Use this option only when empty fields are not important; otherwise, ignoring them can shift values out of their intended positions. - Split into rows instead of columns when that fits your layout. TEXTSPLIT normally puts each part in a separate column. To put the parts on separate rows, leave the column-delimiter argument empty and provide the row delimiter. With comma-separated text in A2, enter
=TEXTSPLIT(A2,,",")in B2. The returned values spill down the rows below B2. Ensure those cells are empty too. This can be handy when you want a vertical list of items from one cell. - Make the output permanent if required. Formula results update when the source text changes. If you need fixed values, select the spilled results, copy them, then use Home > Paste > Paste Values. In Excel for the web, use the available Paste values option from the paste menu. Save a copy first if the original formula-driven output may be useful later. In Excel for Mac, TEXTSPLIT works in supported Microsoft 365 versions, though ribbon labels or paste-menu placement can vary slightly.
Common Mistakes
Using the wrong delimiter: A formula splitting on a comma will not divide a string that uses semicolons or pipes. Look closely at the original data, including less obvious characters such as a hyphen or tab. If the delimiter is not visible, copy one instance from the source cell and paste it between the formula’s quotation marks.
Expecting Excel to guess the pattern: TEXTSPLIT acts on the delimiter you specify. It will not know that a space after a comma is incidental, or that a period belongs to an abbreviation rather than a boundary. Choose a delimiter that reliably marks field boundaries, and clean up the results only after checking them.
Overwriting or blocking the spill range: A formula can be correct and still fail if any destination cell is occupied, merged, or otherwise unavailable. Clear the cells where the output needs to go, and check for a #SPILL! message. Remember that an output area can need a different number of cells for different records.
Dropping meaningful empty fields: Setting the ignore-empty option to TRUE removes empty results. That is convenient for a casual list but risky for structured data. For example, if the second field in a record is intentionally blank, removing it may make the third field appear to be the second.
Assuming the split changes the source cell: TEXTSPLIT returns results elsewhere; it does not edit the original text in place. If you need to replace the original data, verify the split output first, then copy and paste values where appropriate.
Tips
Test the formula on a few varied records before filling it down a large dataset. Include examples with missing fields, repeated delimiters, extra spaces, and the longest expected entry. This helps reveal inconsistent source formatting before it affects many rows.
Use a delimiter that is unlikely to appear inside the values themselves. A comma is common, but it can be a poor choice if names or addresses contain commas that are not intended as field boundaries. A pipe or another dedicated separator may be safer when the source format allows you to choose one.
For a one-time split in place, Text to Columns is a practical alternative. Select the cells containing the text, then choose Data > Text to Columns in Excel for Microsoft 365 on Windows. In the wizard, choose Delimited, select Next, check the delimiter, and review the preview. Set a destination cell if you do not want the output to replace nearby source data, then select Finish. On Mac, the command is also on the Data tab in supported desktop versions; dialog details can differ. Excel for the web may not offer the same full wizard, so use TEXTSPLIT or open the workbook in the desktop app.
For repeated workbooks, keep the original text in one column and the split formula in a separate column area. This makes it easier to compare the result with the source, adjust the delimiter, or correct an import without losing the original entry.
Troubleshooting
Excel displays #NAME?: Your Excel version may not support TEXTSPLIT, or the function name may be misspelled. TEXTSPLIT requires Microsoft 365 or Excel 2024 under the compatibility guidance for this task. Update Excel if appropriate, or use Data > Text to Columns in the Windows desktop app. If a formula uses commas between arguments but Excel rejects it, your regional settings may require another argument separator; follow the separator style Excel uses for other formulas on your computer.
The formula returns the whole string in one cell: Check that the delimiter in the formula exactly matches the character in the source. A regular space is different from a nonbreaking space, and a hyphen is different from an en dash. Retype or copy the delimiter from the text. If the string uses several possible separators, provide all of them as delimiters.
You see #SPILL!: Select the error cell and inspect the indicated output area. Clear any values or formulas blocking the spill, unmerge cells if necessary, and make sure the formula is not inside an Excel table that prevents the expected spill behavior. Then recalculate or re-enter the formula.
Some results contain unwanted spaces or blanks: Use TRIM around TEXTSPLIT to remove extra leading and trailing spaces, or use the ignore-empty option when empty fields truly do not matter. If the issue remains, inspect the source for unusual whitespace or inconsistent separators. Do not remove blanks automatically if they represent missing but meaningful fields.
Text to Columns replaces data you wanted to keep: Undo immediately with Ctrl+Z on Windows or Command+Z on Mac, then run the wizard again with a different destination cell. Review the preview and destination before selecting Finish. If Undo is no longer available, restore the original values from a saved copy or source file.
Frequently Asked Questions
Is there a function called SPLIT in Excel?
Excel’s function for splitting a string into parts is TEXTSPLIT, not SPLIT. TEXTSPLIT lets you choose a delimiter and return results in columns or rows. Other spreadsheet programs may use a function named SPLIT, so check which application your formula is intended for.
Can I split a cell at a space?
Yes. With the text in A2, enter =TEXTSPLIT(A2," ") in an empty cell to separate it at each regular space. This can divide a full name into words, but it may create extra blank results when there are repeated spaces. Review the data first, especially if names can contain several parts.
Can TEXTSPLIT separate text at a line break?
Yes. A line break can be used as a row delimiter, though the formula can be less convenient to enter. If the source is copied from another application, try Text to Columns or replace the line break with a visible delimiter first. Check the resulting rows carefully to ensure no content is lost.
Will the split results change when I edit the original string?
Yes. TEXTSPLIT is a formula, so its spilled results recalculate when the source cell changes. If you want to preserve a particular result, copy it and use Paste Values. Keep the source text separately if you may need to re-check the split.
How do I split only at the first delimiter?
TEXTSPLIT is designed to split at each occurrence of the specified delimiter, not just the first one. If you need only the first portion and the remainder, use a suitable first-delimiter approach such as TEXTBEFORE and TEXTAFTER in Microsoft 365 or Excel 2024, or use Text to Columns and review the available options for your version. Always test the result when the delimiter might also appear inside a value.
Can I split a whole column of strings at once?
Enter the formula beside the first source value, confirm the spill area is clear, then fill the formula down for the remaining rows. Each row will split its corresponding source cell. Check entries with more parts than the others, because they may need extra empty columns to the right.
Related
- Assembly Split in CATIA: How to Separate a Product
- Balance Sheet Accounting: How It Works
- J Curve Balance of Payments: How It Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Insert Shortcut: Add Cells, Rows, and ColumnsIn Excel for Microsoft 365 on Windows, select the cells, row, or column where you want space, then…
- Excel Shortcut to Select a Column on Windows and MacIn Excel for Microsoft 365 on Windows, press Ctrl+Spacebar to select the entire column containing the active cell.…
- How to Insert Color in Excel: Cells, Text, and RulesTo add color in Excel, select the cells, open Home > Font > Fill Color, and choose a…
- Google Sheets Jira Add-On: Setup, Features, and LimitsYes—Atlassian’s Jira Cloud for Sheets add-on connects Jira Cloud with Google Sheets so you can bring Jira issues…
- Google Sheets Unix Timestamp to Date Conversion GuideTo convert a Unix timestamp in Google Sheets, divide seconds by 86,400 or milliseconds by 86,400,000, then add…
- Freeze Cells in Excel While Scrolling: A Step-by-Step GuideTo keep headings visible while you scroll in Excel, select the cell below the rows and to the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.