To split text in Excel, select the cells, choose Data > Text to Columns, select Delimited or Fixed width, set the split point, and finish the wizard. For a repeatable formula-based split in Microsoft 365, use TEXTBEFORE for the left part and TEXTAFTER for the right part.
Choose the right way to split the text
The best method depends on whether you need a one-time cleanup or a result that updates when the original text changes. Text to Columns is usually quickest for a list that is already complete. Formulas are more useful when new rows arrive regularly, or when the split needs to recalculate after someone edits the source. Flash Fill can help when the pattern is irregular but easy to demonstrate.
- Text to Columns: Use it to divide a selected column in place, such as splitting a batch of full names at a space.
- Formulas: Use them when you want the original values preserved and the split results to update automatically.
- Flash Fill: Use it for a recognizable pattern that is awkward to express with one delimiter, and check its results carefully.
Before choosing, identify what marks the boundary: a comma, space, hyphen, tab, or a consistent character position. Also check whether the delimiter can appear inside a value. Splitting every comma in a description, for example, may create more than two columns or separate text that belongs together.
Split a column with Text to Columns
In Excel for Microsoft 365 on Windows, Text to Columns is a built-in conversion tool. It does not create a live link to the source: it writes the split values into cells. Make sure there is room for the output before you run it.
- Put the unsplit text in one column, with one record per row, and select the cells you want to divide. You can select the whole column, but selecting only the data range helps avoid processing unused cells.
- Choose Data > Text to Columns on the ribbon.
- In the wizard, choose Delimited if a character marks the split, or Fixed width if the boundary is at a consistent position. Select Next.
- For a delimited split, tick the relevant delimiter, such as Comma, Space, or Other, and enter a custom character if needed. Review the preview.
- Select Next if you need to set column formats or a destination, then choose Finish. If Excel warns that cells already contain data, cancel and move or clear the conflicting content first.
By default, the destination begins at the selected column. If the source is in A2:A20, the results may occupy columns A and B; that can replace the original values in column A. To keep the source, specify an empty destination such as C2 in the wizard. Make sure the destination has enough empty columns for every piece of text the delimiter may produce, not just two.
Excel for Mac also has a Text to Columns command on the Data tab; the wizard’s appearance can differ slightly by release. Excel for the web may not offer the same full desktop wizard in every build, so use the available Data command or a formula if the command is missing.
Set delimiters and handle repeated separators
A delimiter is the character or mark that divides the text. For Jordan Lee, a space is the delimiter; for Jordan,Lee, it is a comma. In the wizard’s preview, check that the boundary falls where you expect before you finish. A delimiter split can create more than two output columns if that character occurs multiple times in a cell.
The Treat consecutive delimiters as one option is useful when spacing is inconsistent. For example, a row with several spaces between first and last name can otherwise produce empty columns. Turning the option on treats a run of adjacent delimiters as a single separator. It is not appropriate when empty fields are meaningful, such as two consecutive commas indicating a blank field in a structured record.
For delimited files, the Text qualifier setting can matter. A CSV-style value such as "Lee, Jordan",Sales contains a comma inside quotation marks as well as the comma between fields. A text qualifier of a double quote can tell the wizard to keep the quoted comma within the same field. Always inspect the preview: badly formed quoting or a different file convention may require cleaning the source first.
Use formulas for an automatic split
In Microsoft 365 or Excel 2024, TEXTBEFORE and TEXTAFTER can split at the first occurrence of a delimiter. Unlike Text to Columns, formulas leave the source alone and update when it changes. The formulas below assume the original text is in A2 and the split character is one ordinary space.
With the full name in A2, enter this in B2 to return the text before the first space. For Jordan Lee, the result is Jordan.
=TEXTBEFORE(A2," ")
With the full name in A2, enter this in C2 to return the text after the first space. For Jordan Lee, the result is Lee.
=TEXTAFTER(A2," ")
Fill both formulas down using the fill handle, or select the formula cells and press Ctrl+D on Windows. On Mac, the fill-down shortcut is generally Control+D. If a row has no delimiter, these basic formulas return an error. To return the original text in the left result and a blank in the right result when the delimiter is absent, use error handling.
With source text in A2 and one space as the delimiter, this formula in B2 returns Jordan for Jordan Lee and returns the whole value for a row with no space.
=IFERROR(TEXTBEFORE(A2," "),A2)
With source text in A2 and one space as the delimiter, this formula in C2 returns Lee for Jordan Lee and a blank if there is no space.
=IFERROR(TEXTAFTER(A2," "),"")
These examples split at the first space, not the last. Thus Jordan A Lee becomes Jordan and A Lee. If you need the last name alone, the right-side formula needs a different approach because the number of spaces varies. For data with multiple fields, consider whether the desired rule is “first delimiter,” “last delimiter,” or “split every delimiter” before building formulas.
Clean inconsistent spaces and protect important values
Names and imported data often contain leading spaces, trailing spaces, or repeated ordinary spaces. A simple split can leave a space at the start of the right-hand result. You can clean ordinary extra spaces with TRIM, but note that Excel’s TRIM does not remove every unusual whitespace character, such as some nonbreaking spaces copied from web pages.
With the source text in A2, this formula in B2 returns the first word after trimming ordinary extra spaces. For Jordan Lee, the result is Jordan.
=TEXTBEFORE(TRIM(A2)," ")
With the same source in A2, this formula in C2 returns the remainder after the first word. For Jordan Lee, the result is Lee.
=TEXTAFTER(TRIM(A2)," ")
Take particular care with postal codes, product codes, and account identifiers. Excel may interpret split results as numbers or dates, removing leading zeros or changing how a value displays. In the Text to Columns wizard, select the affected output column and choose Text as its column data format when preserving characters exactly matters. A value such as 00427 should remain text if those zeros are part of the identifier rather than a numeric quantity.
Fixed-width splits and Flash Fill
Choose Fixed width in the wizard when the split position is consistent even though there is no delimiter. For example, if every record begins with a two-character region code followed by a product name, set the break after character two. The preview lets you add, move, or remove break lines. Fixed width is unsuitable when the first part varies in length, because the same character position would cut different records incorrectly.
Flash Fill is another option for a pattern Excel can infer from examples. If full names are in A2:A20, type the desired first name from A2 into B2, then begin the next example in B3. When Excel recognizes the pattern, accept its preview or choose Data > Flash Fill. On Windows, Ctrl+E invokes Flash Fill. Its availability and shortcut can differ on Mac, where the ribbon command is the safer route.
Flash Fill produces values rather than formulas: later edits to the source do not automatically revise the filled results. It can also infer an unintended rule if the examples are ambiguous. Check several rows, including unusual names or blank cells, before relying on the output. For repeatable workbooks, formulas are usually easier to audit and refresh.
Text to Columns, formulas, or Flash Fill?
For a one-time list with a clear delimiter, Text to Columns is direct and lets you preview the result. It is especially convenient when a whole column needs to be converted quickly. Its main risk is overwriting adjacent data or changing the original column, so use an empty destination or make a copy first.
Formulas are a better fit for an ongoing process, such as a monthly export pasted into a table, because the result can recalculate as the source changes. They also make the split rule visible to someone inspecting the workbook. The trade-off is that formulas require a little setup, and delimiter exceptions need explicit handling. Flash Fill is fast for one-off patterns that are hard to describe, but it is less transparent and does not update.
There is no single best choice for every dataset. If the source must remain untouched, formulas or an output destination elsewhere are safer. If the split boundary varies by row, a simple delimiter split may not be enough; examples, cleanup, or a more specific formula rule may be required.
Common problems and how to fix them
- The output overwrote data: Undo immediately with Ctrl+Z on Windows or Command+Z on Mac, if the action is still in the undo history. Then rerun the wizard with a blank destination column. Saving a copy before a large conversion is a sensible precaution.
- Text split into too many columns: The chosen delimiter appears more than once in some cells. Review the preview and decide whether all occurrences should split, or use formulas that return only the text before and after the first delimiter.
- Blank results or formula errors: Check that the delimiter in the formula matches the actual character. A normal space is different from a tab, hyphen, or nonbreaking space. Use the wizard preview or inspect a copied value to identify the separator.
- Leading zeros disappeared: Excel treated an identifier as a number. Restore the original data if possible, then split again while setting the relevant output column to Text. Formatting a number with zeros afterward does not always restore the original characters.
- Some rows split incorrectly: The data may not follow one consistent pattern. Check names with middle initials, descriptions containing commas, and blank fields separately rather than assuming every row has the same structure.
Frequently Asked Questions
How do I split text into two columns at a space in Excel?
Select the cells and choose Data > Text to Columns, select Delimited, tick Space, and finish the wizard. If you need an automatic result, use TEXTBEFORE and TEXTAFTER with a space as the delimiter.
How can I split a cell at a comma without changing the original?
Use formulas in two empty columns, with the source in A2: =TEXTBEFORE(A2,",") returns the text before the first comma, and =TEXTAFTER(A2,",") returns what follows it. These functions are available in Microsoft 365 and Excel 2024.
Can Text to Columns split text into exactly two columns?
Not necessarily. It splits at each occurrence of the selected delimiter, so repeated delimiters can create additional columns. If only the first occurrence should count, formulas using TEXTBEFORE and TEXTAFTER are more precise.
Will the split update when I change the original text?
Text to Columns and Flash Fill create values, so they do not update automatically. Formula results recalculate when the source changes, provided the formulas remain in place.
Why is Text to Columns splitting at the wrong place?
Confirm the delimiter and inspect the wizard preview. The data may contain repeated separators, quoted delimiters, or a different whitespace character. If the split point is a fixed character position rather than a separator, choose Fixed width instead.
Related
- Excel Group Rows: How to Expand and Collapse Rows Easily
- Excel File Corrupted? How to Recover and Repair It
- Excel Group By and Remove Duplicates: How to Choose
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Insert a Blank Line in Microsoft Word: Quick StepsTo insert a blank line in Microsoft Word, place the cursor where you want the extra space and…
- Report Builder CountRows: Count Rows in a GroupIn Microsoft Report Builder, count rows in a group with =CountRows("GroupName"), replacing GroupName with the tablix group’s actual…
- Excel Scatter Plot With Named Points: Add Name LabelsTo show names beside points in an Excel scatter plot, put the names in worksheet cells, select the…
- Can You Lock Cells in Google Sheets? How It WorksYes. In Google Sheets, you can protect individual cells, ranges, or an entire sheet so that selected people…
- Conditional Formatting Colour in Excel: Rules and FixesIn Excel, conditional formatting colour is a fill, font, border, data bar or icon that appears automatically when…
- How to Delete a Blank Last Page in Microsoft Word QuicklyTo delete a blank last page in Word, show formatting marks with Home > Paragraph > Show/Hide ¶,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.