Excel Text to Columns: Splitting & Parsing Data

1. What Is Text to Columns & Why Is It Used?

Text to Columns is a core data-parsing utility on Excel's Data tab. When raw data is imported into Excel from CSV files, web API outputs, or legacy databases, multiple values (like First Name, Last Name, Department, and Salary) are often squashed together into a single column.

Text to Columns takes that single combined string and splits it across separate adjacent columns in seconds, making the data ready for formulas, Pivot Tables, and charts.

RAW SINGLE CELL:
A2: "Rahul,Sharma,Analytics,85000"

PARSED INTO SEPARATE COLUMNS:
A2: "Rahul" | B2: "Sharma" | C2: "Analytics" | D2: 85000

2. Delimited Mode vs. Fixed Width Mode

Excel gives you two distinct ways to split text:

• Delimited Mode: Used when text fields are separated by a specific character such as a comma (,), semicolon (;), space ( ), tab, or hyphen (-). This is the most common mode for CSV files.
• Fixed Width Mode: Used when text fields align in vertical columns with spaces between each field at fixed character lengths (common in legacy mainframe text reports).

3. Step-by-Step Wizard Walkthrough

1. Highlight the column containing the merged text.
2. Go to Data tab → Data Tools → Text to Columns.
3. Step 1 of Wizard: Select Delimited or Fixed Width and click Next.
4. Step 2 of Wizard: Check the box for your delimiter (e.g. Comma or Space) and review the live data preview window.
5. Step 3 of Wizard: Select individual destination columns to specify data formats (General, Text, Date) or skip unwanted columns.
6. Click Finish.

4. Column Data Formats & Preserving Leading Zeros

In Step 3 of the wizard, you can click on any column in the data preview to set its explicit format:

• Text Format: Essential for employee IDs or postal codes (e.g. "00123"). Setting format to Text prevents Excel from dropping leading zeros!
• Date Format (MDY/YMD): Tells Excel how to interpret text dates so they convert into real Excel serial dates.
• Do Not Import Column (Skip): Drops unwanted columns automatically during split.