Text to columns in Excel - Step-by-Step Guide

Introduction

Text to Columns helps you split data in one column into multiple columns. It is useful when you have combined data like full names or addresses and want to separate them into parts.

When you have a list of full names in one column and want to split first and last names into separate columns.

When you receive data with values separated by commas or tabs and want to organize each value into its own column.

When you want to separate dates or times stored as text into day, month, and year columns.

When you import data from another system and all information is in one column but needs to be split.

When you want to clean up data by breaking combined fields into individual pieces for easier analysis.

Steps

Step 1: Select

- the column with combined data

The column is highlighted and ready for splitting

Step 2: Click

- Data tab on the ribbon

Data tools options appear

Step 3: Click

- Text to Columns button in the Data Tools group

The Convert Text to Columns Wizard opens

Step 4: Choose

- Delimited option in the wizard

You can select the character that separates your data (like comma or space)

💡 Use Delimited if your data uses characters like commas, tabs, or spaces to separate values

Step 5: Click

- Next button

You see delimiter options

Step 6: Select

- the delimiter(s) that match your data (e.g., Comma, Space)

The data preview shows how your data will split

Step 7: Click

- Next button

You can choose the data format for each column

Step 8: Click

- Finish button

The data splits into separate columns based on your settings

Before vs After

Before

One column with full names like 'John Smith' in each cell

After

Two columns: one with 'John' and the next with 'Smith' separated

Settings Reference

Delimited

📍 Convert Text to Columns Wizard, Step 1

Choose this when your data is separated by specific characters

Default: Tab

Fixed width

📍 Convert Text to Columns Wizard, Step 1

Choose this when your data has fixed-width fields without delimiters

Default: None

Column data format

📍 Convert Text to Columns Wizard, Step 3

Set how Excel treats the data in each new column

Default: General

Common Mistakes

Choosing the wrong delimiter or forgetting to select the delimiter

The data will not split correctly and may stay combined or split incorrectly

Carefully check your data to identify the correct delimiter and select it in the wizard

Not selecting the correct data format for columns

Dates or numbers may be misinterpreted or formatted incorrectly

Use the Column data format step to set the correct format like Date or Text

Not having enough empty columns to the right before splitting

Existing data may be overwritten when the split data fills adjacent columns

Insert empty columns to the right of your data before using Text to Columns

Summary

Text to Columns splits combined data in one column into multiple columns.

Use delimiters like commas or spaces to separate data parts.

Always check delimiter and data format settings to avoid errors.