How to Convert Text to Columns in Microsoft Excel

One of the most common frustrations in Microsoft Excel occurs when you paste data from another program, or open a poorly formatted CSV file, and discover that hundreds of rows of distinct data have been mashed into a single column. For example, instead of having one column for “First Name” and another for “Last Name,” you might have a single cell that reads John,Doe.

Attempting to manually retype or copy-paste thousands of names into separate columns would take hours. Fortunately, Excel has a dedicated tool built specifically to solve this exact problem: the “Text to Columns” wizard. This tool allows you to instantly split the data inside a single cell into multiple distinct columns based on a specific character, like a comma or a space.

This guide explains how to use the Text to Columns feature to split your data.

Step 1: Select Your Data

Before you begin, ensure you have empty columns immediately to the right of your messy data. The wizard is going to push the split text out to the right. If you have other valuable data in the adjacent columns, the wizard will overwrite and permanently delete it. (Pro tip: Right-click the column letter at the top of the screen and select “Insert” a few times to create a safe buffer of blank columns).

1. Highlight the entire column of mashed-up data that you want to split. (You can click the column letter at the very top to select the whole column instantly).
2. Click on the Data tab in the main Excel ribbon at the top of the window.
3. Look in the “Data Tools” group and click the button labeled Text to Columns.

Step 2: Choose Your Delimiter

The “Convert Text to Columns Wizard” will appear. It is a simple three-step process.

1. In Step 1 of the wizard, you must tell Excel how your data is separated. In almost all cases, you should choose Delimited. (This means characters such as commas or tabs separate each field). Click Next.
2. In Step 2, you must define the specific “Delimiter.” Look at your messy data. Is it separated by a comma (John,Doe)? Or is it separated by a space (John Doe)?
   • Check the box next to the appropriate character (e.g., Comma or Space).
   • As you check the box, look at the “Data preview” window at the bottom of the dialog box. You will see vertical lines appear, showing you exactly where Excel is going to split the data. Ensure it looks correct.
3. Click Next.

Step 3: Format the New Columns

In the final step, Excel gives you the opportunity to define what kind of data is going to end up in these new columns before it executes the split. Most of the time, you can safely leave this set to the default “General” format.

1. However, if you are splitting data that contains long product codes or zip codes that start with a zero (e.g., 00123), you must click on that specific column in the Data Preview window and change the “Column data format” radio button to Text. If you leave it as General, Excel will treat it as a number and automatically delete the leading zeros.
2. Once you are satisfied with the preview, click Finish.

Excel will instantly process the entire column, perfectly splitting your mashed data into beautifully organized, separate columns ready for sorting and analysis.