To split text from one cell into separate cells in Microsoft 365, use TEXTSPLIT. If A2 contains Maya Chen,Seattle,WA, enter =TEXTSPLIT(A2,",") in B2; Excel spills Maya Chen, Seattle, and WA across three cells in the same row.
How formula-based splitting works
A split formula looks for a delimiter: the character or text sequence separating the parts you want. A comma, space, hyphen, slash, or line break can serve as a delimiter. The formula returns the text on either side of it, or—when using TEXTSPLIT—can return several pieces at once.
In current Microsoft 365 for Windows, TEXTSPLIT is usually the simplest choice when you want every part separated. The formula goes in just one cell, and the results fill neighboring cells automatically. This is called a spilled array. The original cell is not changed, so you can keep the source text and use the split values elsewhere.
The delimiter must match the data. If values are separated by a comma followed by a space, specifying only a comma leaves a leading space at the start of later fields. Conversely, specifying a comma and space will not split a value that has a comma but no following space. Check a few source cells before choosing the delimiter.
Split text across columns with TEXTSPLIT
For a horizontal split, provide the delimiter as TEXTSPLIT’s second argument. With the sample text in A2, a comma delimiter returns one piece per column. B2 displays Maya Chen, C2 displays Seattle, and D2 displays WA.
If A2 contains Maya Chen, Seattle, WA, enter this in B2 to split on the exact comma-space sequence:
With the full name and location string in A2, this formula returns Maya Chen in B2, Seattle in C2, and WA in D2: =TEXTSPLIT(A2,", ").
TEXTSPLIT can also split on more than one delimiter. Suppose A2 contains apples;pears,plums. To treat either a comma or semicolon as a separator, use an array constant for the column delimiters:
With apples;pears,plums in A2, this returns apples, pears, and plums in adjacent columns: =TEXTSPLIT(A2,{",",";"}).
When a cell contains repeated delimiters, empty pieces may matter. For example, a double comma can represent a blank field in a data record. TEXTSPLIT keeps empty pieces by default. To ignore empty pieces instead, set its fourth argument to TRUE:
With North,,West in A2, this formula returns North and West in adjacent columns, skipping the empty middle field: =TEXTSPLIT(A2,",",,TRUE).
Split content into rows instead
Sometimes the desired layout is vertical: each item should appear on its own row. TEXTSPLIT accepts a row delimiter as its third argument. Leave the column delimiter argument empty, then supply the separator used between items as the third argument.
With Red|Green|Blue in A2, this formula returns Red, Green, and Blue in separate rows beneath the formula cell: =TEXTSPLIT(A2,,"|").
The direction matters because a spilled result needs clear cells in its destination area. A horizontal split needs empty cells to the right; a vertical split needs empty cells below. If you need to rearrange a horizontal result into a vertical one, or vice versa, you can use Excel’s TRANSPOSE function around the split formula.
With Red|Green|Blue in A2, this formula transposes the split so the three values spill vertically: =TRANSPOSE(TEXTSPLIT(A2,"|")).
Extract only the part before or after a delimiter
You do not always need to split every field. TEXTBEFORE and TEXTAFTER are useful when you only need the text on one side of a separator—for example, a product prefix before a hyphen or a domain name after an at sign. These functions are available in Microsoft 365 and Excel 2024.
With the identifier INV-2048-NY in A2, this returns the text before the first hyphen, INV: =TEXTBEFORE(A2,"-").
With the same identifier in A2, this returns the text after the first hyphen, 2048-NY: =TEXTAFTER(A2,"-").
To get the text after the second hyphen instead, specify the delimiter instance number. With INV-2048-NY in A2, this returns NY: =TEXTAFTER(A2,"-",2). This is handy when codes have a consistent number of separators, but it may not be appropriate if the source data varies.
Use older formulas for compatibility
TEXTSPLIT, TEXTBEFORE, and TEXTAFTER are not available in every Excel release. If a workbook must work in an older version, LEFT, MID, FIND, LEN, and TRIM can extract a value around a known delimiter. These formulas generally return one piece each, so you may need separate formulas for separate output columns.
With Maya Chen, Seattle in A2, this formula returns everything before the first comma, Maya Chen: =LEFT(A2,FIND(",",A2)-1).
With the same text in A2, this formula returns the text after the comma and removes the extra surrounding spaces, giving Seattle: =TRIM(MID(A2,FIND(",",A2)+1,LEN(A2))).
These older formulas assume the delimiter exists. FIND returns an error if it cannot find the comma, so a formula may need additional error handling when some rows do not follow the same pattern. They are also less convenient for splitting a variable number of fields. For a long list or a changing number of delimiters, a newer dynamic-array formula or a data-import tool is usually easier to maintain.
Choose a formula or a built-in tool
Use a formula when you want split results to update automatically as the source cell changes, or when the result needs to be part of a larger calculation. Use a one-time data tool when you want to convert a selected range and do not need the split to recalculate from the original text.
- TEXTSPLIT: Best for a flexible formula-based split into several columns or rows in Microsoft 365 or Excel 2024.
- TEXTBEFORE or TEXTAFTER: Best when you only need one side of a delimiter and have a supported Excel version.
- LEFT, MID, FIND, and TRIM: Useful for older-version compatibility or a fixed, simple pattern.
- Text to Columns: A built-in desktop option for splitting a selected range without writing a formula. In Excel for Windows, find it on the Data tab in the Data Tools group.
Text to Columns is useful for a one-off cleanup, but it writes the split values into the worksheet rather than creating a live formula relationship with the original cell. Before using it, check that the cells to the right of the selected data are empty; the operation can place output in those cells. Menu availability and layout can differ in Excel for the web, so use the desktop app if you do not see the command there.
Common errors and how to fix them
A #SPILL! error means Excel cannot place the full dynamic-array result in the required cells. Look across the row or down the column where the result should appear and clear any blocking values. Merged cells in the output area can also prevent a spill. Once the destination is clear, the formula can return its full result.
A result that keeps spaces usually points to a delimiter mismatch or untrimmed source data. If you split on a comma but the source has a space after it, the next piece may begin with a space. You can include the space in the delimiter, or clean the extracted values with TRIM. Be cautious when spaces are meaningful, such as in a full name or an address.
If a formula shows an error because the delimiter is absent, check whether all rows use the same format. A value such as Seattle WA has no comma, so a formula that expects one cannot find the split point. You may need to handle exceptions separately or standardize the source values first.
Also confirm that you are using the correct delimiter character. A hyphen, en dash, and em dash look similar but are different characters. Data copied from websites or other systems may contain nonbreaking spaces or inconsistent punctuation. If a formula appears correct but does not split the text, inspect and standardize the source character rather than repeatedly changing the formula at random.
Practical tips for reliable results
Before filling a formula down a large dataset, test it on a few representative cells: a typical row, a row with an empty field, and one with an unusual value. This reveals whether the separator is consistent and whether the number of parts can vary. If a formula refers to A2, filling it down normally changes the reference to A3, A4, and so on.
Keep the original column until you have checked the split results. A formula-based output is easy to revise if you discover that a delimiter was wrong. If you ultimately need fixed values, copy the results and use Paste Special > Values to replace formulas with their current results. Check the output range before pasting so you do not overwrite other data.
Remember that splitting text is not the same as converting it into a number, date, or other data type. A result that looks like a number may still be stored as text, depending on the source and the operation. If you plan to calculate with the results, verify the cell format and test a simple calculation rather than assuming that the displayed appearance proves the underlying type.
Frequently Asked Questions
Can Excel split a cell automatically when I type a delimiter?
Not with a formula that edits the original cell. A formula can display the separated parts in other cells and update when the source changes. For a one-time split of existing data, use Text to Columns in the desktop app.
Why does TEXTSPLIT return extra spaces?
The formula may be splitting on a comma while the source contains a comma followed by a space. Include the space in the delimiter or trim the returned values. Check the actual source characters because punctuation and spacing can vary between rows.
Can I split a full name into first and last names with a formula?
Yes, if the name format is consistent. For a name separated by a single space, TEXTSPLIT can return the parts into neighboring cells. Names with middle names, multiple spaces, or family names containing spaces need a clear rule; a simple split cannot reliably determine which words are first and last names.
Does TEXTSPLIT work in older Excel versions?
TEXTSPLIT is available in Microsoft 365 and Excel 2024, not every older release. For compatibility, consider formulas such as LEFT, MID, FIND, and TRIM, or use Text to Columns for a one-time split in desktop Excel.
Can I split text vertically instead of across columns?
Yes. In TEXTSPLIT, leave the column delimiter argument empty and provide a row delimiter as the third argument. The output then spills down the worksheet, so make sure the cells below the formula are available.
Related
- Resolving Circular References in Excel
- Repeat Print Area in Excel: Repeat Rows on Every Page
- Remove Text to Columns in Excel: Undo or Reverse It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MATCH Lookup Array: How to Use ItThe MATCH function in Excel finds the position of a lookup value within a one-dimensional range or array,…
- Date Difference in Minutes in Excel: Formulas That WorkIf A2 contains a start date and time and B2 contains an end date and time, enter =(B2-A2)*1440…
- Excel Formula for Expense Report? Simple & Pro TipsThe core Excel formula for an expense report is =SUM(range) to total a column of amounts, but a…
- Caleb Hammer Budget Spreadsheet: How to Find the Right OneIf you’re looking for a Caleb Hammer budget spreadsheet, verify any download through Caleb Hammer’s official website or…
- Excel COUNTIF Remove Duplicates: How to Count Unique ValuesThe short answer: Excel’s COUNTIF function does not remove duplicates, but you can combine it with other functions…
- Excel MIN Ignore NA: Formulas That WorkYes. In Excel for Microsoft 365, use =AGGREGATE(5,6,A2:A10) to find the smallest numeric value in A2:A10 while ignoring…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.