How to Use Excel Text to Columns to Split Messy Data

The Nightmare of Combined Data

If you regularly export data from a third-party application, such as a CRM system, an email marketing platform, or an archaic accounting database, you have almost certainly encountered the nightmare of combined data.

You open the exported CSV file in Excel, hoping to find a clean spreadsheet with neat columns for First Name, Last Name, and City. Instead, you find a single column where all that information is jammed together. It might look like this: Smith, John - London.

If you need to sort the list alphabetically by Last Name, or filter it to only show customers in London, you are completely stuck. Excel cannot sort by a word buried in the middle of a string of text. The instinct is to manually retype the data into separate columns, but if you have a list of 5,000 customers, that will take days.

You can solve this problem in less than ten seconds using a powerful, built-in feature called Text to Columns. This tool is designed to instantly slice a single column of messy text into multiple, clean columns based on specific rules.

Understanding Delimiters

Before using the tool, you must look at your messy data and identify the “delimiter.” A delimiter is a specific character (like a comma, a space, a dash, or a semicolon) that consistently separates the pieces of information you want to split.

In our example, Smith, John - London, there are two distinct delimiters. The Last Name and First Name are separated by a comma. The Name and the City are separated by a dash (and spaces).

Using the Text to Columns Wizard

Once you have identified your delimiters, the splitting process is incredibly straightforward.

Step 1: Prepare the Worksheet

Crucial Warning: Text to Columns will overwrite any data sitting in the columns immediately to the right of your target column. Before you begin, ensure you have inserted enough blank, empty columns to the right to accommodate the new data you are about to create.

Click the letter at the top of the column containing your messy data (e.g., Column A) to highlight the entire column.

Step 2: Launch the Tool

Go to the Data tab on the Excel ribbon.
In the “Data Tools” group, click Text to Columns.
The Convert Text to Columns Wizard will appear.

Step 3: Define the Split Rules

The wizard has three steps.

Step 1 of 3: Ensure Delimited is selected (this is the default). This tells Excel you want to split the data based on specific characters like commas or tabs. (The other option, “Fixed width”, is only used if every single piece of data is exactly the same number of characters long, which is rare). Click Next.
Step 2 of 3: This is the most important screen. In the “Delimiters” section, check the boxes that correspond to the characters separating your data.
- For our example (Smith, John - London), you would check the Comma box, the Space box, and then check Other and type a dash (-) into the small box next to it.
- Look at the “Data preview” window at the bottom of the dialogue box. Excel will draw vertical black lines exactly where it intends to split the text. Ensure these lines look correct. Click Next.
Step 3 of 3: This screen allows you to format the newly created columns. By default, Excel sets them all to “General”, which is usually fine. If one of your new columns contains dates or Zip codes starting with a zero, you should select that column in the preview window and change its format to “Text” or “Date” to prevent Excel from accidentally removing the leading zeros.
Click Finish.

The Result

Instantly, Excel will execute the split. Your single, useless column containing Smith, John - London will vanish, replaced by three clean, sortable columns: one for “Smith”, one for “John”, and one for “London”. You can now add headers to these new columns and filter your data exactly as you originally intended.

Conclusion

Excel’s Text to Columns tool is the ultimate antidote to poorly formatted data exports. By learning how to identify delimiters and use the wizard effectively, you can transform thousands of rows of unusable text into a pristine, analyzable database in a matter of seconds, saving you hours of tedious manual data entry.