To split text in Excel with a formula, use =TEXTSPLIT(A2,"|") when the full text is in A2 and each part is separated by a vertical bar. In Microsoft 365 or Excel 2024, the results spill into nearby cells automatically. Replace the bar with the delimiter in your data, such as a comma or space. If your Excel version does not include TEXTSPLIT, you can use older functions such as LEFT, MID, FIND, and LEN for simpler splits.
What You’ll Need
- A Microsoft Excel workbook and the text you want to separate in a cell or column.
- A consistent separator, also called a delimiter. Common examples are a comma, space, hyphen, slash, or vertical bar.
- Microsoft 365 or Excel 2024 for TEXTSPLIT, TEXTBEFORE, and TEXTAFTER. Older Excel versions can use the legacy formula examples below for basic splits.
- Empty cells beside the source data for formula results to spill into. Dynamic array formulas cannot spill over existing content, and they should be entered outside an Excel table.
Step-by-Step
- Check the pattern in your text. Look at several source cells before writing a formula. Identify the exact character between the parts and check whether it appears consistently. For example, if A2 contains
Maya Chen|Sales|Seattle, the vertical bar separates the name, department, and city. If some rows use a comma and others use a dash, one simple delimiter formula will not split every row in the same way. - Choose where the results should go. Keep the original text in its current column and place the formula in the first result cell on the same row. With the text in A2 and room to the right, enter the formula in B2. Leave the cells to the right blank: TEXTSPLIT returns multiple values, and Excel fills adjacent cells with those results. For a list of source strings, you can fill a compatible formula down for each row, or use a dynamic array approach if you specifically want to process a range.
- Split on one delimiter with TEXTSPLIT. With
Maya Chen|Sales|Seattlein A2, enter this in B2:=TEXTSPLIT(A2,"|"). The result is Maya Chen in B2, Sales in C2, and Seattle in D2. The first delimiter argument tells Excel which character marks each boundary; the formula does not include that character in the returned pieces. For comma-separated text, use a comma as the delimiter. If the source uses comma followed by a space, you can split on that exact pair instead. With text in A2 separated by comma-space, use=TEXTSPLIT(A2,", "). This avoids leaving an unwanted leading space on each later item. - Split a sentence at spaces, and decide how to handle repeated spaces. With words in A2 separated by single spaces, enter
=TEXTSPLIT(A2," ")in B2. If the source may contain two spaces in a row, ordinary splitting can return empty cells between words. With that same text in A2, enter=TEXTSPLIT(A2," ",,TRUE)in B2 to ignore empty pieces created by repeated spaces. The third argument is intentionally left blank because it is the row delimiter; TRUE is the fourth argument, which tells Excel to ignore empty results. - Return the pieces vertically instead of horizontally, if that suits your layout. TEXTSPLIT uses its second argument for a column delimiter, which normally places results across a row. Its third argument specifies a row delimiter. With a comma-separated string in A2, use
=TEXTSPLIT(A2,,",")in B2 to split at commas and spill the pieces down a column. The empty second argument means there is no column delimiter. Make sure the cells below B2 are clear so the results can spill. - Extract only the text before or after a separator. If you need two specific pieces rather than every piece, TEXTBEFORE and TEXTAFTER can be more direct. With
Maya Chen|Salesin A2, enter=TEXTBEFORE(A2,"|")in B2 to return Maya Chen, and enter=TEXTAFTER(A2,"|")in C2 to return Sales. TEXTAFTER returns everything after the first matching delimiter, so if A2 contained three bar-separated fields it would return both later fields together. These functions are available in Microsoft 365 and Excel 2024. - Use a legacy formula if TEXTSPLIT is unavailable. For text in A2 containing
Maya Chen,Sales, enter=LEFT(A2,FIND(",",A2)-1)in B2 to return the part before the first comma. To return everything after that comma, enter=MID(A2,FIND(",",A2)+1,LEN(A2))in C2. FIND locates the comma, LEFT takes the characters before it, and MID begins just after it. These formulas handle a simple two-part split; with several delimiters, TEXTSPLIT is usually easier to maintain. - Check the output, then fill the formula for other rows. Verify the first result against the original text, including punctuation and spaces. If the split is correct and each row follows the same pattern, copy or fill the formula down the column. In Windows Excel, select the formula cell and drag its fill handle down, or copy and paste it into the target range. On Mac, the fill handle works in the same way; keyboard shortcuts differ, with Command+C and Command+V for copy and paste instead of Ctrl+C and Ctrl+V. Excel adjusts references such as A2 to A3 as the formula moves down.
TEXTSPLIT, TEXTBEFORE, and TEXTAFTER are available in Microsoft 365 and Excel 2024; they are not present in some older perpetual versions. The formulas work in current Microsoft 365 for Windows, Mac, and the web, although the surrounding interface differs. If Excel displays an error as soon as you enter a formula, your installation may use a different list separator because of regional settings. The examples here use standard comma argument separators.
Common Mistakes
- Using a delimiter that does not exactly match. A comma and a comma followed by a space are different separators. If your formula splits at the comma alone, the next piece may begin with a space. Choose the exact delimiter or remove unwanted spaces separately.
- Expecting a split formula to overwrite neighboring cells. A spill formula needs an open output area. If a cell in the results range already contains anything, Excel cannot place the full result there.
- Assuming every row has the same structure. A missing delimiter, extra delimiter, or inconsistent spacing can make results differ from row to row. Review representative rows before filling formulas down.
- Putting a spill formula inside an Excel table. Dynamic array results need room to spill and cannot spill inside a table’s data area. Place the formula outside the table or use a legacy formula that returns one value per cell.
- Confusing a formula result with a permanent split. Formula results change when the source text changes. If you need fixed values, copy the result cells and use Paste Special > Values after checking them.
Tips
- Make a copy of the source column before cleaning or rearranging data. This preserves the original if you discover that the delimiter or split pattern needs adjustment.
- Use a delimiter that is not part of the actual values when you can choose the source format. A vertical bar or a distinctive tag is less ambiguous than a space, which may occur inside names and addresses.
- For space-separated names, consider whether a person may have a middle name or a multiword surname. Splitting every space into separate cells can create more columns than the two you intended; TEXTBEFORE and TEXTAFTER can instead separate the first word from the remainder.
- When reviewing a spill result, select its first formula cell. Excel outlines the spill range, making it easier to see which cells belong to the formula.
- If the same split is needed repeatedly in new files, keep a small example workbook with the source pattern and formula. Confirm that the delimiter still matches whenever the incoming data format changes.
Troubleshooting
You see #SPILL!. Select the formula cell and look at the intended result area. Clear any content in the way, including a space or a formula returning an empty string. If the formula is inside an Excel table, move it outside the table. Also check that there are enough rows or columns available for all returned pieces.
You see #NAME?. Excel may not recognize TEXTSPLIT, TEXTBEFORE, or TEXTAFTER because your version does not include the function, or because its name was mistyped. Check that you are using Microsoft 365 or Excel 2024 for these functions. If your version is older, use the LEFT, MID, FIND, and LEN examples for a basic split, or update Excel if your license allows it.
You see #VALUE! with FIND or the split does not happen. FIND returns an error when the exact delimiter is not present. Check whether the source has a different character, such as a hyphen instead of an en dash, or includes a space before or after the separator. With TEXTSPLIT, confirm that you supplied the separator that actually appears in the cell.
The formula appears as text instead of calculating. The cell may be formatted as Text, or the formula may have an apostrophe before the equals sign. Change the cell format to General, edit the cell, and confirm the formula begins with an equals sign. Then press Enter. Check also that Show Formulas is not enabled; on Windows, Ctrl+` toggles that view. On Mac, the equivalent shortcut is Control+` in current Excel, though keyboard layouts can affect the key.
Excel rejects the formula syntax. Check that each opening parenthesis has a closing parenthesis and that straight quotation marks surround text delimiters. The examples use commas between function arguments. Depending on regional settings, Excel may require semicolons as argument separators; if so, replace argument-separating commas with semicolons, but do not change a comma that is part of the text delimiter.
Frequently Asked Questions
Can I split text in Excel with a formula without TEXTSPLIT?
Yes. For a basic split around one delimiter, LEFT, MID, FIND, and LEN can return text before or after the delimiter. The legacy examples above work in older Excel versions, but separating many fields usually takes more complicated formulas.
Does TEXTSPLIT change or remove the original cell?
No. It returns results in other cells and leaves the source text unchanged. The results recalculate when the source cell changes.
Can one formula split text into rows instead of columns?
Yes. In TEXTSPLIT, provide the separator as the third argument, which is the row delimiter, and leave the second argument blank. The vertical example above shows the comma-separated layout.
What happens if my text contains the delimiter more than once?
TEXTSPLIT returns a separate piece for each interval between matching delimiters. For example, two bars in one string produce three pieces. With TEXTAFTER, the default result is all text after the first matching delimiter, not just the next field.
Can I split text on more than one kind of delimiter?
TEXTSPLIT can accept multiple delimiters, but the formula must be designed for the exact separators and your Excel version. If your data has inconsistent punctuation or exceptions, standardize the source first or handle those cases separately rather than assuming one simple delimiter will fit every row.
Can I keep the split results as fixed text?
Yes. After confirming the results, copy the output range and choose Home > Paste > Paste Special > Values. This replaces formulas with their current results while leaving the original source column intact.
Related
- How to Save an Excel File in Read-Only Mode: Steps
- Excel Rounding Shortcut: Change Decimals or Values
- How to Rotate Text in Microsoft 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 an Excel Table With FormulasTo copy an Excel table and keep its formulas, select the table, press Ctrl+C, click a blank destination,…
- Excel Filter: Select Multiple Values with a ShortcutIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off; this shortcut does…
- SUMIF and SUMIFS Examples in ExcelIn Excel, use SUMIF to add values that meet one condition and SUMIFS to add values that meet…
- Excel Age from Date of Birth with DATEDIF ExplainedTo calculate age in completed years in Excel, put the date of birth in A2 and enter =DATEDIF(A2,TODAY(),"Y")…
- Excel Formula for Quarter From Date: 3 Easy MethodsIf the date is in A2, use =ROUNDUP(MONTH(A2)/3,0) to return its calendar quarter as a number from 1…
- Excel Value Comparison Formula: Examples and OptionsIn Excel, compare two values with =A2=B2: it returns TRUE when the values match and FALSE when they…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.