Text to Columns in Excel: how it works

With Data > Text to Columns you split one column into several, at every space, comma or semicolon. Handy for names or a pasted export.

Try the first exercises for freeIn the lesson Cleaning up data with the ribbon

What does Text to Columns do?

Sometimes everything is in one column: a first name and a surname, or a whole line from an export with semicolons in between. With Text to Columns, you split a column like that over several columns. A three-step wizard asks how the pieces are separated, and Excel puts each piece in a column of its own.

You choose between Delimited, if the same character is always between the pieces, and Fixed width, if each piece is the same length. The result is ordinary values, not formulas: if the source changes, you split it again.

When do you use Text to Columns?

Full names in column A, which you want to split into a first name column and a surname column.
A CSV export that came in as one column, with semicolons between the data.
Codes such as UK-2024-115, where you want the country, the year and the number separately.
Numbers stored as text that Excel won't calculate with: Text to Columns followed straight away by Finish turns them into real numbers.

Splitting a column, step by step

This is how to go about it:

Insert enough empty columns to the right of the column, so that Excel doesn't overwrite anything.
Select the column with the text you want to split.
Choose Data > Text to Columns. Choose Delimited and click Next.
Tick the character that separates the pieces, such as Space or Semicolon. At the bottom, you see straight away what the result will look like.
Click Next, choose a format per column if you like, and click Finish.

Where it often goes wrong

If there's already something in the columns to the right, Excel overwrites it after a warning. Insert empty columns first.
Text to Columns works once. If the source changes, you have to split again. In Excel 365, TEXTSPLIT does it with a formula that keeps up.

Want to try it yourself?
Practise this in the lesson Cleaning up data with the ribbon. The first exercises are free.

Try the first exercises for free

Frequently asked questions

Can I convert a column of numbers stored as text this way?

Yes. Select the column, choose Text to Columns and click Finish straight away. Excel reads every cell again, and text that looks like a number becomes a real number.