Text to Columns Online
Split full names, pasted lists, locations, codes, and other delimited Excel cell values into clean separate columns.
Drop your file here or choose a fileTap to choose a file
Opened on your device - never uploaded · up to 50MB
Supports .xlsx, .xls, .xlsm, .xlsb · or paste a file from your clipboard
No file handy? · download it
How to Separate Text in Excel
Excel has four ways to split one column into several: the Text to Columns wizard, Flash Fill, the TEXTSPLIT function, and classic LEFT, MID and FIND formulas. Which one fits depends on your Excel version and on whether the split must update when the source changes.
Method 1: The Text to Columns wizard
Best for: A one-off split in any version of Excel.
- Make sure there are enough empty columns to the right. The wizard overwrites whatever is there.
- Select the column to split.
- Go to Data > Data Tools > Text to Columns, choose Delimited and click Next. Choose Fixed width instead when every piece has the same number of characters.
- Tick the separator - Comma, Space, Tab, Semicolon, or type one in Other. Tick Treat consecutive delimiters as one if double spaces appear.
- Click Next, set any column that holds codes with leading zeros to Text, check the Destination cell, and click Finish.
Method 2: Flash Fill
Best for: Pulling one piece out of irregular text, such as a first name. Excel 2013 and later.
- In the column next to your data, type the result you want for the first row.
- Move to the cell below and press Ctrl+E, or use Data > Data Tools > Flash Fill.
- Excel fills the column by copying the pattern. Check a few rows; if it guessed wrong, type a second example and run it again.
Flash Fill results are fixed values - they do not update if the source changes.
Method 3: TEXTSPLIT, TEXTBEFORE and TEXTAFTER
Best for: Microsoft 365, when the split must stay live.
=TEXTSPLIT(A2,",")spills each comma-separated piece into its own column.- Split on several separators at once with
=TEXTSPLIT(A2,{",",";"}). - Take one piece with
=TEXTBEFORE(A2," ")for the first word or=TEXTAFTER(A2," ",-1)for the last.
Method 4: LEFT, MID and FIND
Best for: Older versions without TEXTSPLIT.
- First word:
=LEFT(A2,FIND(" ",A2)-1) - Everything after the first space:
=MID(A2,FIND(" ",A2)+1,LEN(A2)) - Wrap either in
=IFERROR(...,A2)so a cell with no space returns itself instead of #VALUE!.
In Google Sheets
Select the column and use Data > Split text to columns, or write =SPLIT(A2,",").
Common mistakes
- Leading zeros disappear when a piece like 00742 is treated as a number. Set that column to Text in step 3 of the wizard.
- Dates can flip. A piece such as 03/04 may become a date in your regional format. Set it to Text, or to Date with the right order, in the wizard.
- Commas inside quotes - "Smith, John" - split in the wrong place. Import the file as CSV instead, where quoted fields stay whole.
Exactly What the Split Does
Pick one source column and one delimiter. Every data row's cell in that column is read as text and cut at each occurrence of the delimiter, and each resulting piece has its surrounding spaces trimmed. The tool then looks at the whole column to find the row that produced the most pieces, and gives every row that many new columns - shorter rows are padded with empty cells so the grid stays rectangular. The new columns are named after the source header with a number appended.
| Full Name (before) | Full Name 1 | Full Name 2 | Full Name 3 |
|---|---|---|---|
| Grace Hopper | Grace | Hopper | |
| Ada King Lovelace | Ada | King | Lovelace |
| Alan Turing | Alan | Turing |
Splitting on a space gives three columns here, because one name has a middle element. That is the behavior to watch for on names and addresses: a single long row widens the whole sheet.
Delimiters and the Replace Option
Space, Comma, Semicolon, and Tab cover most pasted data. Custom takes anything you type, including more than one character - use - with its surrounding spaces to split Ticket - Priority cleanly, or | for pipe-separated exports. A custom delimiter left empty falls back to a comma. Replace source column is on by default and drops the original once it has been split; untick it to keep the original beside the parts while you check the result. Either way the columns on both sides of the source keep their positions and the rest of the sheet is untouched.
Edge Cases
There is no quote handling: "Smith, John" split on a comma becomes two pieces with the quote marks still attached. Consecutive delimiters produce empty pieces, so a double space yields a blank column in the middle - the Clean tab's Collapse repeated spaces fixes that before you split. Non-text cells are converted to text first, so splitting a number on . separates its decimal part, and every output piece is stored as text, which is worth remembering if you intended to sum the results. With First row headers off there is no header to name the new columns after, so they are numbered from the generic column label instead. The preview shows the first 30 rows; the download writes all of them to a single sheet.
How to Split a Column
- Open the workbook - .xlsx, .xls, .xlsm, or .xlsb, read in your browser.
- Choose the source column - the one holding the joined values.
- Choose the delimiter - or type your own under Custom.
- Check the preview - especially the widest row, which sets the column count for everyone.
- Download Split Excel - a new workbook; your original file is never modified.
When a Different Tool Fits Better
If the values have no separator at all and you are cutting by character position, the LEFT, RIGHT and MID generator is the right approach. If the file is real CSV text with quoted fields, let CSV to Excel parse it properly instead of splitting it after the fact. When you want the split to stay live in the sheet rather than becoming a static download, the TEXTSPLIT generator writes the formula. To pull one specific thing out of a messy cell - an email inside a sentence - use Excel Data Extractor. And to go the other way and glue columns together, use Combine Columns.
Frequently Asked Questions
As many as the row with the most parts needs. If one address in a thousand splits into six pieces and the rest into four, every row gets six columns and the short rows are padded with empty cells - nothing is truncated or spilled into a neighbouring column.
No. The split is a plain text split on the delimiter you choose, with no quote awareness, so "Smith, John" splits into two parts and the quote characters stay in the data. For genuinely CSV-quoted text, import the file through the CSV to Excel converter, which parses quoting properly.
You get an empty column between them. Splitting 'John Smith' on a single space produces John, an empty cell, and Smith, because there are two spaces. Collapse repeated spaces in the Clean tab first and the split then behaves as expected.
Yes. Untick Replace source column and the original stays in place, immediately to the left of the new parts, so you can check the split before discarding it.
No. Excel's Text to Columns wizard offers Delimited and Fixed Width; this tool implements the delimited half only. A fixed-width layout with no separator character cannot be split here.
Related Tools
Combine Columns
The reverse - join fields back into one
TEXTSPLIT Generator
The same split as a live Excel formula
LEFT, RIGHT, MID Generator
Split by position when there is no delimiter
CSV to Excel
Proper quote-aware parsing of CSV text
Excel Data Extractor
Pull a pattern out of a cell instead of splitting it