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

  1. Select the cell or column that contains the text you want to split.
  2. Go to the Data tab and select Text to Columns.
  3. In the Convert Text to Columns Wizard, choose Delimited and select Next.
  4. Pick the delimiters your data uses, such as Comma or Space, and check the Data preview.
  5. Select Next, then choose the Destination cell where the split data should go.
  6. 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.

RuleDetail
Selection sizeAny number of rows, but no more than one column
Where the button isData tab, in the Data Tools group
Space to the rightBlank 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.

OptionWhat it does
DelimitersPick the separating character, such as tab, semicolon, comma or space, or tick Other and type your own
Treat consecutive delimiters as oneUse it when fields are separated by more than one delimiter character
Text qualifierKeeps text inside quotation marks together, so "Dallas, Texas" stays in one cell
Data previewShows 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.

FormatWhat it does
GeneralConverts currency-style numbers to the Currency format, and falls back to General when a column mixes letters and numbers
TextConverts a column made entirely of number characters to Text format
DateConverts 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 formatFirst name formulaLast 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?

NeedBetter method
One-time clean-up of a listText to Columns
Results that update when the source changesFormulas
Simple comma or space separated textText to Columns
Names with prefixes, suffixes or two-part surnamesFormulas, using the pattern for that name format
Combine cells instead of splittingCONCAT or CONCATENATE, which Microsoft names as the opposite of splitting

Common problems

ProblemWhat to check
Existing data was overwrittenLeave blank columns on the right, or choose a different Destination cell
Pieces end up in the wrong columnsOpen the wizard again and check the delimiter selection in the preview
A date split the wrong waySet the Date format and the correct date order on the last wizard page
Selection will not splitMake sure you selected one column only

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.

Sources

Leave a Reply

Your email address will not be published. Required fields are marked *