Often in Excel, I'll have a long list of data in one column, that I really wish I had in two columns.
A great example would be two numbers with a space, or maybe a column of names like "Eric, Andrews".
Before I knew about the Text to Columns feature, I used to go through these lists manually to move the data into a second column.
But once I encountered a 1000 row list, I realized it just was not possible anymore to do this work manually.
So I found a way to split up data using Text to Columns in just a couple clicks.
And by the end of this post, so will you!
Example of how to use the Text to Columns feature of Excel
Let's split some data using an example and sample data set.
Let’s get started!
Here is a list of numbers with a random space midway between each number.
So, we want to split the data into two columns, using the space as a split point.
Split the data using the text to columns feature
Highlight the data, go to the data tab, and click “text to columns”
Our data is separated by space, so “characters such as commas or tabs separate each field” is a perfect description of our data.
So, in our example, let’s choose “delimited” data.
*if we had a comma separating the data, or tabs, or any type of character, we could also use that as a split point.
In my red box, we see a preview of how the data will be split.
It isn’t split into columns yet, so let’s click the “Space” box.
That means we want to split the data anywhere that there is a “Space”.
In the red square you’ll see our data is splitting correctly now (that little vertical black line shows where the data will break into two separate columns).
Boom! The data is split into two columns using the space as a split point.
Now you should be a pro at using the Text-To-Columns feature in Excel!
To go deeper with the rest of Microsoft Excel, join the army of students on Udemy.com taking my video-based course: Become An Excel Power User in 2.5 Hours. Take a look below!