Text To Columns – Splitting Your Data

Here are three examples of columns of data that we might want to split up.  The first has text that is divided by spaces, and the lengths of any cell may vary.  The second has similar data separated by a dash.  Finally, the third has no spaces or characters, but we want to shave off the first two characters and have the model number separate:

Starting with the first example, begin by highlighting the data range, but not the column heading, and from the Data tab, choose Text To Columns from the Data Tools group:

The Text to columns wizard dialog box appears.  The first thing we need to decide is how our data is separated.  The two choices are Delimited – which means there is a character of some sort separating the text where we want to divide it; or Fixed width – which means that we want to separate the data based on a specific number of characters.  In our case, we want to choose delimited and click Next:

Since our data is separated by a space, we’ll select that as the delimiter and we’ll see in the box below how Excel will separate the text into columns.  Click Next:

And the dialog box that appears allows us to decide how to format each column of data, or decide not to import it at all.  Just by example, I’ve chosen to skip the third column of data, and Excel heads that column as “Skip Column”.  Subsequently, I put that back to General and cllicked Finish:

And now the data is nicely separated into three columns!

The second set of data is separated by a “-“, so the only change to the previous procedure is that instead of selecting the space option, choose “Other” and insert the appropriate character:

The result will be the same and Excel will import the text into three separate columns without the “-“.

Finally, if we want to separate the text based on a specific number of characters, we’ll choose Fixed width:

Click on Next and Excel will give you the option to choose where you want to split your data:

Clicking Next will present the same options as previous allowing you to choose the format and inclusion or exclusion of any column.  Click finish and your data is now separated into two columns:

And there you go!  I hope you find this information valuable and useful in working with Excel.

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply