How to Split Data into Multiple Columns

Ever wonder how you can easily split data from one column into multiple columns? You can do this with a feature called “Text to Columns.” “Text to Columns” is just what it sounds like – splitting text into multiple columns.

Using Text to Columns is easy. You just need to tell Excel how to split it up. There are two ways of splitting your data, by delimiters or fixed widths. I’ll show you examples of each.


Using Delimiters

Usually, there is a clear “something” in between your text, that determines where you want to split your data. That “something” can be a space, a comma, a semicolon, or really any character. This is your delimiter.

Let’s say you have a bunch of full names all in one column, and there is a space between the first, middle, and last names. In this example, your delimiter is a “space.” Download my example to follow along.

You can split your names by specifying the space as your delimiter. First, select your data.

Then, go to the ribbon at the top of your screen, and select “Text to Columns.”

Choose “delimited” and click Next.

Then choose your delimiter, which in this example is Space. You can generally leave the other options as they are. Click Next.

In the last step, choose the formats for each column. In most cases, you can leave them as General.

When you click Finish, your data will be split!


Using Fixed Width

The other way to split your data is by using the fixed width method. You would use this method if your data can be broken up easily with vertical lines.

For example, say you have a dates and expenses lumped together into one column like this.

Since the dates in our example are the same width, we can split this by using fixed width. First select your data.

Then go to the ribbon again and select Text to Columns.

This time, select Fixed Width and click Next.

Create, delete, or move vertical lines where you want to split your data. Click Next.

Choose your column data formats, and then click Finish.

Your data is now split into two columns!