Imported Excel data often packs several pieces of information into one cell, such as a full name, address, or product code. Excel can split that content into separate columns with a wizard, a learned pattern, or formulas.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, and Excel 2019 on Windows or Mac when combined text needs to become separate fields without retyping.
Use Text to Columns
This is best when every row uses a consistent separator, such as a space, comma, tab, or semicolon.
- Select the column to split.
- Make sure there are empty columns to the right for the results.
- Open Data > Text to Columns.
- Choose Delimited and click Next.
- Choose the separator, such as Space or Comma, and check the preview.
- Click Next, choose a data format if needed, and click Finish.
Excel writes the split pieces into adjacent columns. If it warns about overwriting data, stop unless the destination columns are truly empty.
Use Flash Fill for uneven patterns
Flash Fill learns from examples instead of splitting only on a fixed delimiter.
- In the column beside your data, type the result you want from the first row, such as the first name.
- Start typing the second row result.
- When Excel previews the remaining results, press Enter to accept.
- If it does not appear, use Data > Flash Fill or Ctrl+E.
- Repeat in another column for the next part, such as last name.
Split with formulas
Use formulas when the result should update if the source text changes.
- Use =TEXTBEFORE(A2,” “) to return text before the first space.
- Use =TEXTAFTER(A2,” “) to return text after the first space.
- Fill the formulas down.
- If those functions are unavailable, use LEFT, MID, and FIND formulas instead.
More control
Handle middle names or initials
A space-based Text to Columns split may create three or more columns for names such as Jane A. Smith. Keep the middle part separate, or recombine it with a formula if needed.
Fix invisible separator problems
If a normal space split fails, the text may contain non-breaking spaces. Use SUBSTITUTE(A2,CHAR(160),” “) before splitting.
Freeze formula results
Formula splits remain connected to the source. Copy and Paste Special > Values if you need static results for sorting, moving, or sharing.
Use multiple delimiters
Text to Columns can use more than one delimiter at once, such as commas and semicolons, when imported data is not perfectly consistent.
Sources
- Microsoft Support – Split text into different columns with the Convert Text to Columns Wizard (2025)
- Microsoft Support – Flash Fill in Excel (2025)
- Microsoft Support – TEXTBEFORE function (2025)