How to Use the Convert Text to Columns Wizard

Data comes in all shapes and sizes. Sometimes it comes in just the right format. Other times we have to do a bit of work to get the data we have into a usable format. This article will address one such instance. Today we will explore how to use the Convert Text to Columns Wizard.

Let’s suppose you received an Excel file full of names and the data was presented as follows:

Nothing wrong with the above at first glance. However, how would you separate the names in Column A so that the first and last name are in separate columns? If you have a list with hundreds of names it wouldn’t be time efficient to go one by one and enter the names manually.

Enter the Convert Text to Columns Wizard!

Using the Excel Convert Text to Columns Wizard

This Wizard allows you to separate data in a single column into multiple columns. Selecting the Text to Columns command will open the Wizard, which then walks you through the various steps. Let’s do an example together.

First, select the data range that you want to convert to columns and navigate to Data > Data Tools > Text to Columns

Once you click Text to Columns the Wizard will open.

Step 1 of the Wizard asks you whether the text is Delimited or Fixed Width. More on these options below. For now just select Delimited. Click next to go to Step 2.

The Wizard now asks you for additional information on how you want to separate the text. If you selected Delimited in Step 1 the Wizard asks you to select a delimiter. This could be a tab, comma, semicolon, space or other. Play around with the options and view the changes in the Data Preview Window. For the purpose of this example we will select comma.

In Step 3 you can specify the formatting you would like to apply to each column. You can select from General, Date, or Text. Here we will use General.

Finally, click Finish and Excel will convert your selected data to columns. From our starting example we now have first and last names in separate columns!

A word of caution: Excel will overwrite data that already exists in the columns. Be sure to insert enough columns so your existing data is not overwritten. Check out this handy keyboard shortcut for adding columns if you want to avoid losing your data.

The Delimited Text Option

Delimited text is text that is separated by a character. This character could be a semicolon, comma, tab, space, or something else. To separate delimited text you will select Delimited in Step 1 when following the steps outlined above.

Excel will give you the option to select which delimiter it should look for. If your option isn’t available select the Other box and enter the character. Excel is super useful in that you can select more than one delimiter. For example, if you want to separate by comma AND space that is perfectly acceptable.

In Step 3, as described above, you can specify the data format. If you are dealing with numbers be sure to set the format to Text so you don’t lose any leading zeros. However, when setting a column to Text Excel may have difficulty identifying any formulas entered into the column.

Once you click Finish the text will be separated into columns based on the criteria you specified.

The Fixed Width Text Option

Fixed width is the second option you are presented with in Step 1 of the Wizard. Fixed width text is text separated by a set number of characters. In Step 2 when using fixed width you can draw a line separating the groups. Click to add the line, drag to move the line around, or double click to remove the line. You can add more than one line by clicking in each area where you would like the cell contents separated into a column.

Follow the same procedures through Step 3, click Finish, and your text is now separated into one or more columns.

Hopefully you found this tutorial helpful. When working with large data sets in Excel it becomes too time consuming to manipulate data one cell at a time. We try and provide you with useful articles, tips and tricks to ensure you make the most of your time in a spreadsheet.