How To Split Text Into Columns In Excel (2026)
Excel can split one column of text, such as full names or a comma-separated list, into separate columns in a few clicks.
The tool for the job is Text to Columns on the Data tab, and a set of formulas does the same thing if you want the result to update.
This guide follows Microsoft’s support pages for Excel on Windows.
Quick steps to split text into columns
- Select the cell or column that contains the text you want to split.
- Go to the Data tab and select Text to Columns.
- In the Convert Text to Columns Wizard, choose Delimited and select Next.
- Pick the delimiters your data uses, such as Comma or Space, and check the Data preview.
- Select Next, then choose the Destination cell where the split data should go.
- Select Finish.
Microsoft’s own example splits text using both a comma and a space as delimiters.
If you get stuck, select Help inside the wizard for guidance on every step.
Before you start: make room on the sheet
Text to Columns writes into the columns to the right of your selection.
Leave enough blank columns there so the split data does not overwrite anything already in the sheet.
If needed, insert empty columns to hold each part of the text.
| Rule | Detail |
|---|---|
| Selection size | Any number of rows, but no more than one column |
| Where the button is | Data tab, in the Data Tools group |
| Space to the right | Blank columns for every piece the text will split into |
Delimited or fixed width: which to choose
The first wizard page asks how your text is organized.
Choose Delimited when a character such as a comma, tab or space separates the pieces.
Microsoft’s Text Import Wizard page also describes a Fixed width option, where you click the data preview to add a column break.
In that preview, double-click a break to remove it, or drag it to move it.
Use fixed width only when every piece sits at the same character position in every row.
Delimiter options
These options appear on the delimiter page, according to Microsoft’s Text Import Wizard documentation.
| Option | What it does |
|---|---|
| Delimiters | Pick the separating character, such as tab, semicolon, comma or space, or tick Other and type your own |
| Treat consecutive delimiters as one | Use it when fields are separated by more than one delimiter character |
| Text qualifier | Keeps text inside quotation marks together, so "Dallas, Texas" stays in one cell |
| Data preview | Shows how the split will look before you finish |
Always read the preview before selecting Next, because it is the quickest way to catch a wrong delimiter.
Choose a column data format
On the last wizard page you can tell Excel how to treat each new column.
Microsoft documents four choices for its import wizard.
| Format | What it does |
|---|---|
| General | Converts currency-style numbers to the Currency format, and falls back to General when a column mixes letters and numbers |
| Text | Converts a column made entirely of number characters to Text format |
| Date | Converts date characters to a date format, and you pick the order such as YMD |
| Do not import column (skip) | Leaves the selected column out |
If a date order does not closely match a built-in or custom Excel date format, Excel imports the column as General.
Split full names with formulas instead
Formulas are useful when new rows will keep arriving, because they recalculate.
Microsoft’s examples assume the full name is in cell A2 and use SEARCH to find the position of the space.
| Name format | First name formula | Last name formula |
|---|---|---|
| Jeff Smith | =LEFT(A2,SEARCH(" ",A2,1)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) |
| Eric S. Kurjan (middle initial) | =LEFT(A2,SEARCH(" ",A2,1)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) |
| Kahn, Wendy Beth (last name first) | =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)) | =LEFT(A2,SEARCH(" ",A2,1)-2) |
| Paula Barreto de Mattos (three-part last name) | =LEFT(A2,SEARCH(" ",A2,1)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) |
| Mr. Ryan Ihrig (prefix) | =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) |
Microsoft says that two-part last names such as James van Eaton and hyphenated names such as Julie Taft-Rider use the same formulas as the simple Jeff Smith case.
The same page covers middle initials, two-part first names such as Mary Kay, and suffixes such as Jr. and III, each with its own pattern.
Find the example closest to your data on Microsoft’s page and copy the pattern.
How the splitting formulas work
Microsoft says the key is the position of each character in the text string.
LEFT takes characters from the start of the text, RIGHT from the end, and MID from the middle.
SEARCH returns the position of a character such as a space, and you can nest it to find the second or third space.
LEN returns the total length of the text, which RIGHT uses to count back from the end.
Adjust with +1 to start after a space, or -2 to remove a comma and a space.
Microsoft’s page also lists TEXTSPLIT, TEXTBEFORE and TEXTAFTER as related functions.
Text to Columns or formulas?
| Need | Better method |
|---|---|
| One-time clean-up of a list | Text to Columns |
| Results that update when the source changes | Formulas |
| Simple comma or space separated text | Text to Columns |
| Names with prefixes, suffixes or two-part surnames | Formulas, using the pattern for that name format |
| Combine cells instead of splitting | CONCAT or CONCATENATE, which Microsoft names as the opposite of splitting |
Common problems
| Problem | What to check |
|---|---|
| Existing data was overwritten | Leave blank columns on the right, or choose a different Destination cell |
| Pieces end up in the wrong columns | Open the wizard again and check the delimiter selection in the preview |
| A date split the wrong way | Set the Date format and the correct date order on the last wizard page |
| Selection will not split | Make sure you selected one column only |
Related Excel guides
After splitting a list, you can clear repeats with our guide to removing duplicates in Excel.
To keep your new header row visible, see how to freeze rows and columns.
If a formula returns an error, see fixing #N/A, #VALUE! and #REF! errors.