Text to Columns Guide: How to Separate Data in Excel
Ever had to copy a large set of data from somewhere?
Yeah, it’s all fun and games until you’re running out of time, and you have to split data from 50 rows into multiple cells. It can get really confusing to separate columns and keep the original data intact.
Luckily, Excel provides the Text to Columns tool to avoid this mess.
But where do you find it, and how do you use it? Learn all this and more in the guide below 😉
Also, if you want to practice the steps, download our sample workbook here.
Table of Contents
Separate delimited data with Text to Columns
Delimited text refers to text strings that contain delimiters like commas, spaces, tabs, semicolons, and others. This is the easiest and fastest method of splitting text into columns in Excel.
Let’s see how to use it below 😃
We have the following example data.
The data set contains the names, numbers, ages, and places of residence of some students. These are all separated by commas.
We want to split each attribute into a different column using delimited text.
So to split the data:
- Select the original data set.
- Go to the Data Tab.
- Select the Text to Columns option.
The Convert Text to Columns Wizard appears.
- Select Delimited text from the Original Data Type.
- Press Next.
- Choose the required delimiter – we selected a comma.
PRO TIP! 💡
The Text to Columns wizard dialog box shows a data preview window at the bottom. It readily gives you an idea of how your text is affected.
- Press Next.
- Choose an option from the Column Data format – we chose General.
You can choose the format of your data type. And the wizard will split the data accordingly. Select the column whose format you want to change and then choose any option.
- Choose the destination of the cell where you want the text to split.
Make sure you have multiple empty columns on the right side of your original data. Splitting text will overwrite any data to the right of the source data.ext is affected.
Kasper Langmann, Microsoft Office Specialist- Press Finish.
Clean up the data a little bit.
Your data has been split, and Excel did it perfectly – it’s really that easy 🤩
Separate fixed data with Text to Columns
Just like delimiters, you can separate data using fixed-width data types.
Unlike delimited data type, fixed width separates text using character count. This means if you have a four-lettered name, Excel will split the entire column at the fourth character.
Want to know how? Read on 🧐
We will use this data set below.
The data set contains the telephone number of random people. We need to separate these numbers from the name and add them to a different column.
Note that each name is four characters, and the numbers are seven digits each.
The character count is important because, in fixed width, character count is used as the basis for the separation of data. If your character count is not the same, you might have to manually adjust the text in some cells..
Kasper Langmann, Microsoft Office SpecialistNow to separate cells using fixed width:
- Select the data set.
- Go to Data Tab.
- Select the Text to Columns option from the Data tools group.
- Select Fixed Width from the data type.
- Click Next.
- Create a Break Line and move it to the desired width – we placed it at the 5th count.
- Press Next
- Select the Format and Destination.
- Press Finish.
All your data has been split according to the selected width.
Arrange the data to make it look neat.
And it’s done 🥳
Other Text to Columns examples
Using the Text to Column feature in Excel is really fun. And it can help you get the work of hours done in seconds.
Let’s practice some more examples of the Text to Column feature below, and you will be able to master it in no time
Split cells by space
Splitting cells by space is the same as splitting by a comma. Let’s see how it’s done below.
We will use this sample data
We have to separate all the data in column Sr. No. using the delimiter ‘Space.’
- Select the data.
- Go to Data Tab.
- Select the Text to Columns option.
- Click Delimited from the data type.
- Select Space from the delimiters.
- Select the Format.
- Click Finish.
The data appears separated into different columns:
It’s that simple 🤓
Split names into two columns
Let’s now see how to split names into two different columns using the Text to Columns wizard.
We will split the names given in the following data set into two columns.
To do that:
- Select the data.
- Go to Data Tab.
- Select the Text to Columns option.
- Click Delimited from the data type.
- Select Space from the delimiters.
- Select the Format.
- Click Finish.
The names are separated into two different columns:
Pretty easy, no? 🤗
That’s it – Now what?
In this article, we learned how to use Text to Columns in Excel. We also saw how to split text using delimiters and fixed width and some other examples.
Text to Columns tool in Excel is a pretty handy tool and can help split hundreds of data rows into columns in seconds.
Luckily, Excel has many such outstanding features and functions that can help save you a lot of time.
If you are new to Excel, we suggest you practice the VLOOKUP, IF, and SUMIF functions. You can learn them in my 30-minute free email course that teaches these functions and a lot more. Enroll now! 🚀
Other resources
The Text to Column feature is great if you regularly need to copy-paste text and separate it into columns.
If you enjoyed reading this article, we’re sure you’d love to know more. Read similar topics: Split Text, TEXTJOIN, CONCATENATE function, and others.