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.

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.

How to use to text to columns in excel

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:

  1. Select the original data set.
Existing data in one column
  1. Go to the Data Tab.
  2. Select the Text to Columns option.
Select Text to columns next to flash fill

The Convert Text to Columns Wizard appears.

Text to column wizard dialog box.
  1. Select Delimited text from the Original Data Type.
Choosing data type
  1. Press Next.
  2. Choose the required delimiter – we selected a comma.
Comma next to treat consecutive delimiters as one

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.

  1. Press Next.
  2. Choose an option from the Column Data format – we chose General.
Selecting column format for column b

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.

  1. 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
  1. Press Finish.

Clean up the data a little bit.

Data in new column - power query

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.

Data set for a single column

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 Specialist

Now to separate cells using fixed width:

  1. Select the data set.
  2. Go to Data Tab.
  3. Select the Text to Columns option from the Data tools group.
Text to columns options - one cell
  1. Select Fixed Width from the data type.
Fixed width data type
  1. Click Next.
  2. Create a Break Line and move it to the desired width – we placed it at the 5th count.
Data preview section break line
  1. Press Next
  2. Select the Format and Destination.
Data format and destination
  1. Press Finish.

All your data has been split according to the selected width.

Arrange the data to make it look neat.

Final look of all the characters

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

Sample data

We have to separate all the data in column Sr. No. using the delimiter ‘Space.’

  1. Select the data.
  2. Go to Data Tab.
  3. Select the Text to Columns option.
  4. Click Delimited from the data type.
Delimited text data type in first column
  1. Select Space from the delimiters.
Space delimiter selection
  1. Select the Format.
  2. Click Finish.

The data appears separated into different columns:

Split data into two 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.

Split data into two different columns

To do that:

  1. Select the data.
  2. Go to Data Tab.
  3. Select the Text to Columns option.
  4. Click Delimited from the data type.
Delimited data type
  1. Select Space from the delimiters.
Space delimiter
  1. Select the Format.
  2. Click Finish.

The names are separated into two different columns:

Final data set

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 TextTEXTJOINCONCATENATE function, and others.

Frequently asked questions

The Text to Columns tool is very useful when you need to split the text in a row into different columns. You can choose a specific delimiter, and Excel will separate the text strings using that delimiter. You can also separate cells by specifying a string length.

The Text to Column feature is not a formula but a wizard. To enable the wizard, select the cells you want to split.

Go to Data Tab – select the Text to Columns options. A dialog box will appear – choose how you want to split data. Click Finish. The data will be separated into multiple columns.