top of page

Excel Text to Columns: how to split data into multiple columns

Updated: Nov 27, 2022

On many occasions in Excel, you have data in one column that you would like to separate into multiple ones.

A few examples are:

  • When you have a .csv file, where data is entirely grouped into a single column and separated by commas.

  • When you are dealing with data from a multiple-response question, and your file as each response separated by a slash.

  • If you have names in the format "Last Name, First Name" and would like last and first names in different columns.

In each of these situations - and many more - you can save yourself the tedious task of manually separating the data by using Excel's powerful Text-to-Columns feature. So how does it work? Let's see with that last example with the Last Name, First Name situation.

1) Select your data

Excel Text to Columns - Column with last name and first name separated by comma and space

2) In the Data tab, go to the Data group and click Text-to-Columns.

Excel Text to Columns - Data tab

3) A dialog box opens as shown below. Choose the "Delimited" radio-button. We are choosing Delimited because there is a specific string of character, a space followed by a comma, that separates each field.

Excel Text-to-Columns - Dialog Box - Delimited

4) In Step 2 of the dialog box, tick both the "Space" and the "Comma" checkbox. Make sure every other checkbox is not ticked like below, and then hit "Next".

Excel Text-to-Columns - Preview

5) A preview will show up, like in the screenshot below. You can then customize the format of each of the column that will be created, but in this case the General format is fine.

Excel Text-to-Columns - Preview and format

6) Hit "Finish". Tadaaa! Your data is now split in 2 different columns.

Excel Text-to-Columns - Data split in 2 different columns

Stay aware that beyond this example, there are many situations in Excel that can be solved with Text-to-Columns. So every time you need to split data, look if there is not a separator like a comma, a tab, a semicolon, a space, a slash, a "-" or any other thing that will uniquely delimit each field within your data.

7,765 views0 comments

Recent Posts

See All
bottom of page