Excel Text to Columns: Splitting & Parsing Masterclass

Text to Columns separates combined data stored in a single column into multiple individual columns.

Task: Separate the Full Name column into First Name and Last Name using the Space delimiter:

Task: Parse location records separated by a comma (Mumbai, Maharashtra) into distinct City and State columns:

Task: Split product SKU codes (LAP-2026-001) using the Hyphen (-) delimiter into Product, Year, and Number:

Scenario: The raw column contains Amit Sharma, Mumbai. First separate the city by comma, then split the name by space into First Name and Last Name:

Problem: When opening a CSV, Excel sometimes drops all comma-separated values into a single column. Parse it into structured table columns:

Understand when to choose Delimited vs Fixed Width in Step 1 of the wizard:

Task: Parse fixed-width legacy data where column breaks are placed at character positions 5 and 14:

Text to Columns expands horizontally into destination cells to the right of your source column.

In Step 2 of the wizard, check "Treat consecutive delimiters as one" when working with space-delimited text. This prevents multiple consecutive spaces from creating unwanted blank columns!

Match each data format with its correct delimiter:

Task: Parse customer CRM records separated by the Pipe (|) custom delimiter into 4 clean columns:

Scenario: You received a 5-row sales ledger where all fields are combined with pipe delimiters. Execute Text to Columns to generate a production table:

Test your understanding of delimiters, fixed width rules, and column safety:

Checklist for enterprise data parsing workflows:

• Column Destination Setting: In Step 3 of the wizard, customize the Destination cell (e.g., $C$2) to preserve the original raw column in Column A/B.
• Text vs General Column Data Formats: In Step 3, set columns with leading zeros (such as ZIP codes or employee IDs like 00123) to Text format so Excel does not drop leading zeros.
• Non-Destructive Parsing: When automated formula reactivity is needed, companion functions like TEXTSPLIT() are preferred; Text to Columns is ideal for static one-off cleanups.