How To Split Text To Columns In Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

It can be difficult to work with a lot of text in a single column.

Especially if you want to use formulas to work with specific parts of that text.

And while there are some complex formulas that will help you split your text into new columns, it can take a long time and not work all that well.

That’s where Text to Columns comes in!

It’s Excel’s great tool for splitting up text into more manageable chunks.

And it’s much easier than trying to work with formulas to do the same thing!

Kasper Langmann, Co-founder of Spreadsheeto

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

We’ve put together a workbook that contains some examples for you.

If you want to see how Text to Columns works first-hand, download it and follow along!

Download the FREE Exercise File

Download exercise file

Splitting fixed-width text

You can use Text to Columns in two different modes: fixed-width and delimited. While delimited might be more useful, fixed-width is a bit easier. So we’ll start here.

Let’s talk about the difference first.

Fixed-width vs. delimited text

When you’re splitting text to columns, Excel needs to know where to make the splits.

With fixed-width splitting, Excel splits the text at specific character counts. So it might split the text at the 5th, 15th, and 27th characters.

Delimited splitting, on the other hand, takes place when Excel sees certain characters. It might split the text at every comma, tab, or space that it finds.

 

On the first sheet in the example workbook, you’ll see a list of car years and models. Because every year in the list is four digits long, we can use fixed-width splitting.

First, select the data that you’d like to split (in this case, cells A2:A51). Then go to the Data tab and click Text to Columns:

text-to-columns-button

In the Text to Columns Wizard, click the radio button next to Fixed width:

fixed-width-button

Click Next, and you’ll see a preview of where Excel will split your text.

Excel will take a guess at where you might want to split your data. In this case, it guesses correctly!

Kasper Langmann, Co-founder of Spreadsheeto
line-break-preview

If Excel doesn’t guess correctly, you can make changes.

Click on the preview to add a line (Excel will split your text at each line).

Double-click on a line to remove it.

Click and drag a line to move it.

Click Next. Now you can choose how to import each new column:

import-column-types

To import any column in a specific data format, click on the column and select the radio button next to your preferred data type.

In most cases, you can leave it set to General. That’s what we’ll do here.

Kasper Langmann, Co-founder of Spreadsheeto

You also need to set a destination for your new data.

Keep in mind that if you set the destination to the column that your data is coming from (as is the default), you will overwrite your original data.

We’ll change the destination to B2 to keep our originals:

importdestination

Finally, hit Finish to split your text into columns!

text-in-columns

Excel split the text exactly where we told it and created two new columns that are much easier to work with.

Notice that Excel also stripped out the leading space from the cells in the second column. Very convenient!

Kasper Langmann, Co-founder of Spreadsheeto

Splitting delimited text

If your text is split by any delimiter—be it a tab, comma, space, semi-colon, or anything else—Excel can give you a more accurate split.

As you’ll see in a moment, though, you may have to do a bit of extra work to get the columns right.

Even so, delimited splitting is often much faster than fixed-with.

Kasper Langmann, Co-founder of Spreadsheeto

On the second sheet of the example workbook, we have a single column that contains names, phone numbers, and cities.

Let’s split that into three columns: one for the name, one for the phone number, and one for the city.

To start, open up the Text to Columns Wizard by clicking Text to Columns in the Data tab. This time, though, select Delimited:

delimited-split

After clicking Next, you’ll need to choose the delimiter. Because there are no specific characters between the fields in our data, we’ll select Space and deselect everything else:

space-delimiter

As you can see in the preview, Excel is splitting the text at every space—which means it’s splitting the names and phone numbers into multiple cells.

We’ll deal with this later.

Click Next to choose the format of your columns.

This time we’ll change everything to Text so Excel doesn’t mess with the formatting of the phone numbers. Shift-click to select all the columns, and hit the Text radio button.

We’ll set the destination to B2 to save our original data. Then hit Finish to get the new columns:

new-delimited-text-columns

Excel is giving us a lot of warnings about numbers stored as text, but don’t worry about it. With phone numbers, that’s what we want.

Kasper Langmann, Co-founder of Spreadsheeto

Now it’s simply a matter of cleaning up the data!

To do this, use the TEXTJOIN or CONCATENATE functions to combine the names back into a Full Name column. Do the same with the phone numbers.

Note that some cities contain multiple words, and that makes for some strange delimiters and splits. You’ll probably have to fix these manually.

After a bit of clean-up, we have the data split exactly how we want!

final-formatted-data

Save time with Text to Columns

It might take you a couple tries to get used to it, but Text to Columns is one of Excel’s best features for working with text.

You can take a column full of cells that look like they’ll take hours to separate and split them up in a matter of seconds.

And after a bit of clean-up, you’ll have perfect-looking text that you can work with.

Stop using formulas or copy-and-paste to split your text. Text to Columns will save you a lot of time!

Kasper Langmann, Co-founder of Spreadsheeto