To split text at a specific character in Excel for Microsoft 365, use TEXTBEFORE for the part to its left and TEXTAFTER for the part to its right; for example, splitting North-42 at a hyphen returns North and 42. To separate the original data into columns without formulas, use Data > Text to Columns and choose the character as the delimiter.
What “split a cell” means in Excel
Excel does not divide one worksheet cell into two independently editable cells while keeping the original cell intact. A split means putting different pieces of a text value into neighboring cells, either by extracting them with formulas or by converting the existing column into multiple columns. For instance, a cell containing INV-2026-048 could be separated into an invoice prefix and a number, or into all three hyphen-separated pieces.
Choose the method according to what you need afterward. Formulas are useful when source values may change and you want the results to update automatically. Text to Columns is a good fit for a one-time cleanup or for a column that should be replaced by separate values. Both methods need somewhere to put the results: check that the cells to the right are empty before splitting, or you may overwrite data or receive a warning.
Split at the first occurrence with formulas
For the examples below, assume A2 contains North-42, with the hyphen as the character to split on. In Microsoft 365 or Excel 2024, enter this in B2 to return the text before the first hyphen: =TEXTBEFORE(A2,"-"). It returns North. In C2, use =TEXTAFTER(A2,"-") to return 42. These results remain linked to A2, so editing the source updates the formula outputs.
After entering a formula, press Enter. If the list has more rows, copy or fill the formulas down. Select B2:C2 and drag the fill handle at the bottom-right corner of the selection, or copy the formulas and paste them into the required rows. Excel adjusts the relative reference from A2 to A3, A4, and so on.
TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024. They are not available in some older perpetual editions. If you share a workbook with people using an older version, use the compatible formulas described below or check compatibility in the recipient’s Excel version.
Keep the right side and handle a missing character
A common concern is what happens when a row does not contain the chosen character. By default, TEXTBEFORE and TEXTAFTER return an error when the delimiter is missing. If A2 contains North42 instead of North-42, you can choose a fallback. Assuming the same layout, enter =IFERROR(TEXTBEFORE(A2,"-"),A2) in B2. It returns the whole value, North42, when no hyphen is present, rather than displaying an error.
Another option is to return a blank when there is no delimiter. With the source still in A2, use =IFERROR(TEXTAFTER(A2,"-"),"") in C2. For North-42, the result is 42; for North42, it is blank. Decide which behavior makes sense for your data: retaining the unsplit value can make exceptions visible, while a blank may suit reports where incomplete records should not show a partial result.
Split at a later or last occurrence
If the delimiter occurs more than once, specify which occurrence should count. Suppose A2 contains INV-2026-048. To return everything before the second hyphen, enter =TEXTBEFORE(A2,"-",2) in B2; it returns INV-2026. To return the text after that second hyphen, enter =TEXTAFTER(A2,"-",2) in C2; it returns 048. Each formula assumes the complete example string is in A2.
A negative occurrence number counts from the end. With INV-2026-048 in A2, =TEXTBEFORE(A2,"-",-1) returns INV-2026, the portion before the last hyphen. =TEXTAFTER(A2,"-",-1) returns 048, the portion after the last hyphen. This is useful for filenames such as budget.final.xlsx when the final period, rather than the first, marks the extension. Each formula should be entered in a separate output cell, and the source is assumed to be in A2.
Split at every occurrence
When every piece needs its own column, use TEXTSPLIT rather than writing a separate before-and-after formula for each part. If A2 contains INV-2026-048, enter =TEXTSPLIT(A2,"-") in B2. The result spills across three cells: B2 shows INV, C2 shows 2026, and D2 shows 048. The formula is entered once; Excel fills the adjacent result cells automatically.
TEXTSPLIT is available in Microsoft 365 and Excel 2024. Leave the spill area empty; if a value already occupies a cell where an output belongs, Excel displays a #SPILL! error. Also note that splitting an entire column with a dynamic-array formula is different from copying a one-row formula down: test the formula on a few records first, especially if the number of pieces varies between rows.
The delimiter does not have to be one character. If A2 contains Sam Lee, =TEXTSPLIT(A2," ") splits at the space, assuming A2 contains that full name. For a multi-character separator such as ::, use that exact text as the delimiter. A delimiter that appears inside a value will also split it, so check whether the character is truly a reliable boundary in the source data.
Use Text to Columns instead
For a permanent, one-time split in Excel for Microsoft 365 on Windows, select the cells containing the source values, then choose Data > Text to Columns. In the wizard, choose Delimited and select Next. Select the delimiter’s checkbox if it is listed; otherwise select Other and type the character in the box. The preview shows how Excel will divide the values. Select Next, choose a destination if you do not want the results to begin beside the source column, and select Finish.
For example, if A2:A100 contains hyphen-separated codes and the cells in B and beyond are empty, select A2:A100 and run the wizard with a hyphen delimiter. Excel places the pieces into adjacent columns. Selecting a destination such as B2 keeps the original source column in A; leaving the destination at the default can put the first result over the source data. Set the destination deliberately and make a backup before replacing data.
- Use the preview to catch rows where the delimiter occurs more times than expected; those rows may produce extra columns.
- If consecutive delimiters should count as one separator, review the “Treat consecutive delimiters as one” option in the wizard.
- If leading zeros matter, choose Text for the relevant output column in the wizard’s final step. Excel may otherwise interpret text such as
0042as the number 42. - For dates, codes, or identifiers, check the preview and resulting values. Automatic interpretation can change how a value is stored or displayed.
On Mac, use the Data tab’s Text to Columns command when it is available; the dialog can look different depending on the Excel version. In Excel for the web, the full desktop Text to Columns workflow may not be available, so use formulas or open the workbook in the desktop app. The formula approach is also convenient across platforms when the required functions are supported.
Use older-compatible formulas
If the workbook must work in an older Excel version without TEXTBEFORE and TEXTAFTER, LEFT, FIND, MID, and LEN can split at the first occurrence. Assume A2 contains North-42. Enter =LEFT(A2,FIND("-",A2)-1) in B2 to return North. Enter =MID(A2,FIND("-",A2)+1,LEN(A2)) in C2 to return 42.
FIND locates the hyphen’s position. The left formula subtracts one so the hyphen itself is excluded. The right formula begins one character after the hyphen and takes the remaining characters. These formulas split at the first hyphen only; they do not automatically separate every section of a multi-hyphen value. They also return an error if the delimiter is missing, so either clean the input first or wrap the formula in IFERROR with a suitable fallback.
Choose the method and troubleshoot results
Use formulas when results should update with the source, when you need repeatable logic, or when you want to keep the original value unchanged. Use Text to Columns for a quick, static split that you can review and then edit independently. Use TEXTSPLIT when a modern Excel workbook needs to separate all pieces at once; use TEXTBEFORE and TEXTAFTER when you need only the left and right portions.
If the output looks wrong, check these common causes before changing the formula:
- Unexpected extra columns: the delimiter appears inside some values more often than expected. Decide whether to split at a particular occurrence or revise the source format.
- Formula error: the delimiter may be absent, or the Excel version may not support the function. Test a known row and use the older-compatible formulas if needed.
- Spaces remain: a space beside the delimiter is part of the text. For example, splitting
North - 42at the hyphen leaves a space at the end of the left part and at the start of the right part. A cleanup function such asTRIMcan remove ordinary leading and trailing spaces. - Results do not update: Text to Columns produces values, not live formulas. If the original value changes, run the split again or use formulas instead.
- Values disappear or get overwritten: undo immediately if possible, then restore from a saved copy or source file. Before running Text to Columns, verify the destination range is clear.
One related misconception is that a cell can be split visually in the same way a merged cell can be unmerged. Text splitting is a data operation: it creates outputs in other cells. If you only want a line break within a displayed cell, that is a different task and does not create separate values that can be sorted or calculated independently.
Frequently Asked Questions
Can Excel split a cell at a comma?
Yes. Use a comma as the delimiter in TEXTBEFORE, TEXTAFTER, or TEXTSPLIT, or select Comma in Data > Text to Columns. Check whether commas inside quoted text are part of the data before splitting a whole imported file.
How do I split at the second hyphen?
For Microsoft 365 or Excel 2024, use the occurrence argument: with the source value in A2, =TEXTBEFORE(A2,"-",2) returns everything before the second hyphen, and =TEXTAFTER(A2,"-",2) returns what follows it. The cell needs to contain at least two hyphens for that split to succeed.
Can I split text into separate rows instead of columns?TEXTSPLIT can use a row delimiter as well as a column delimiter, but the result depends on how the source is separated. For a simple one-row split into columns, use its second argument for the column delimiter. For more complex layouts, use Power Query or rearrange the result after checking the required output.
Will formulas change the original cell?
No. A formula in another cell reads the source and displays a result; it does not edit the source value. Text to Columns, by contrast, writes split results into the selected destination area and can replace the original column if you leave the default destination there.
Why does Excel show #SPILL! after TEXTSPLIT?
The formula needs empty cells for its output, and something is blocking one or more of those cells. Clear the obstruction or move the formula to an empty area. Also check that the spill range does not overlap a merged cell or another result you need to keep.
Related
- Excel Slicer Multiple Selections: How to Use Them
- Excel Slicer Formatting: Styles, Colors, and Layout
- Excel Slicer Greyed Out? Causes and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- IFERROR in Google Sheets: Meaning and How to Use ItIn Google Sheets, IFERROR checks whether a formula produces an error and lets you show a different value…
- Google Debt Snowball Spreadsheet: Build One in SheetsYes. You can build a debt snowball spreadsheet in Google Sheets by listing each debt, sorting debts from…
- How to Create Name Labels in Word: A Step-by-Step GuideTo create a sheet of name labels in Microsoft Word, go to Mailings > Start Mail Merge >…
- Excel Split Screen: How to Jump Between Panes QuicklyTo jump between panes in a split Excel worksheet, click inside the pane you want to use, or…
- Lock Cells in Excel for Scrolling with Freeze PanesTo keep headings or identifying columns visible while you scroll in Excel, use View > Freeze Panes. Select…
- One-Column Data Table in Excel: How to Create OneIn Excel for Microsoft 365 on Windows, create a one-column data table by placing trial values in a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.