exceltestpracticeStart free
Practice testsLearnFunctionsShortcutsPricingStart free

How-to guides

How to use Text to Columns in Excel

Split text in one column into separate columns at a comma, space, tab or other delimiter, with the Text to Columns wizard on the Data tab.

Data often arrives with several facts in one cell: “Ruiz,Ana,Austin” or a full name or a date and time together. Text to Columns cuts that cell into parts at a character you choose and spreads the pieces across columns.

It is a one-time command and it writes values, not formulas. The care goes into the destination and into the format of each piece.

Steps in Excel for Windows

Shortcut: Alt, A, E

  1. Make room on the right. Text to Columns writes into the columns to the right of the selection and overwrites whatever is there. Insert blank columns first if neighbors hold data.
  2. Select the column. Click the column header, or select just the cells to split. The command works on one column at a time.
  3. Open the wizard. On the Data tab, in the Data Tools group, click Text to Columns. The keyboard route is Alt, A, E.
  4. Step 1: choose Delimited. Pick Delimited when the pieces are separated by a character such as a comma. Pick Fixed width only when every piece sits at the same character position. Click Next.
  5. Step 2: tick the delimiter. Tick Comma, Space, Tab, or Other and type your own character. The Data preview shows where the splits fall. Tick Treat consecutive delimiters as one if double spaces appear.
  6. Step 3: set formats and destination. Choose a Column data format for each piece. Select Text for ZIP codes and IDs so leading zeros survive. Set Destination to a cell on the right if you want to keep the original column. Click Finish.

Menu names are from Microsoft 365 on Windows. Excel for Mac and Excel for the web put the same commands in slightly different places.

Worked example

1. Split at the comma

Before
A
1Ruiz,Ana,Austin
2Lee,Kim,Dallas
3Patel,Raj,Boston
After
ABC
1RuizAnaAustin
2LeeKimDallas
3PatelRajBoston

Each cell in column A holds a last name, a first name and a city separated by commas. After the split, A holds the last name, B the first name and C the city. The original text in A is replaced by its first piece.

2. Pull the first piece out with a formula

Pull the first piece out with a formula. The formula =LEFT(A1,FIND(",",A1)-1) in cell B1 returns Ruiz.
AB
1Ruiz,Ana,AustinRuiz

When the split has to keep working as the data changes, use a formula. FIND locates the first comma at position 5 of A1, and LEFT takes the four characters before it.

Common mistakes

  • Overwriting the neighbors. Excel asks "There's already data here. Do you want to replace it?" Read that prompt. Click Cancel, insert empty columns, and start again if you were about to lose data.
  • Losing leading zeros. A ZIP code such as 02134 becomes 2134 when the column format is General. Set that column to Text in step 3 of the wizard.
  • Dates changing shape. A value like 3/4 can be turned into a date. Set the column format to Text if it should stay as typed.
  • Leftover spaces. Splitting "Ruiz, Ana" at the comma leaves a space at the front of the second piece. Tick Space as a second delimiter with consecutive delimiters treated as one, or clean the result with TRIM.
  • Expecting it to update. The result is plain text, not a formula. If the source changes, run the command again, or use a formula instead.

On Excel job tests

Cue words are "split", "separate into columns" and "each piece in its own cell". Look at the delimiter in the sample data and choose it on purpose. A CSV-like column splits at the comma, a full name at the space.

If the prompt demands a formula, or says the result must update, use LEFT, MID, RIGHT with FIND instead of the wizard. In Microsoft 365, TEXTBEFORE, TEXTAFTER and TEXTSPLIT cover the same ground.

  • LEFTReturns a number of characters from the start of text.
  • MIDReturns characters from the middle of text, starting at a position you choose.
  • RIGHTReturns a number of characters from the end of text.
  • FINDReturns the position of one text string inside another, matching case exactly.
  • TRIMRemoves extra spaces from text, leaving single spaces between words.
  • split full names into first and last name in ExcelBreak a Full Name column into First and Last columns with a formula that keeps working, and handle middle names that break the simple version.
  • combine first and last names in ExcelJoin a first-name column and a last-name column into one full name, with the right spacing, order and handling for missing parts.

Questions

Can I undo Text to Columns?
Yes, immediately with Ctrl+Z. Once you have saved and closed, the original text is gone, so keep a copy of the column before splitting.
How do I split a column by a space?
In step 2 of the wizard tick Space. If names have double spaces, also tick Treat consecutive delimiters as one.
Why did my split numbers lose their leading zeros?
Excel read them as numbers. In step 3 choose Text as the column data format and finish the wizard again.
What is Fixed width for?
For text where every piece starts at the same character position, such as a report with aligned columns. You click in the preview to set the break lines.

Practice this

No free lesson covers Text to Columns yet. The closest practice is the free 5-item sampler, which uses the same kind of tasks in a working grid.

Try a 5-item Excel test sample