Advanced Text to Columns (Delimited and Fixed Width)0%

Data Consolidation and 3D Sheet Formulas

Advanced Text to Columns (Delimited and Fixed Width)

Beginner12 min read•Updated: Jul 11, 2026

Advanced Text to Columns (Delimited and Fixed Width)

Exported mainframe files, CSV logs, and banking statements often arrive as concatenated strings crammed into a single column. Text to Columns parses these strings into structured tabular columns using custom delimiters or fixed character positions.

1. Delimited vs. Fixed Width

Visual Architecture Blueprint

+-----------------------------------+-----------------------------------+
| Delimited Parsing                 | Fixed Width Parsing               |
+-----------------------------------+-----------------------------------+
| Fields are separated by specific  | Fields align at consistent        |
| characters: commas, tabs, pipes   | character counts (e.g., chars 1-10|
| (|), or semicolons.               | is ID, chars 11-30 is Name).      |
+-----------------------------------+-----------------------------------+

2. Step-by-Step: Delimited Parsing

1
Select the single column containing your combined data (e.g., 'A2:A500').
2
Go to Data > Data Tools > Text to Columns.
3
Step 1 of 3: Choose Delimited > Click Next.
4
Step 2 of 3 (Delimiters):

Check Comma, Tab, or check Other and type custom delimiters like pipe (|) or hash (#).
Check Treat consecutive delimiters as one (prevents empty blank columns when multiple spaces separate words).

1
Step 3 of 3 (Column Data Formats):

Highlight each preview column to set its specific data format:
Text: Critical for Employee IDs, Zip Codes, and phone numbers with leading zeros (e.g., "00451")! If left as "General", Excel converts "00451" into the number "451", destroying the leading zeros!
Date: Select format matching incoming data (e.g., DMY or MDY).
Do not import column (skip): Excludes unwanted fields from being pasted.
Destination: Select '$B$2' so your original raw Column A remains intact as a backup!

1
Click Finish.

3. Fixed Width Parsing

When data originates from legacy mainframe COBOL systems without delimiters:

1
Select Fixed width in Step 1.
2
In Step 2, Excel displays a visual ruler.
3
Click the ruler to insert break lines with arrows.
4
Drag break lines to adjust column widths, or double-click a line to remove it.
5
Click Finish.

4. Secret Pro Tip: Rapid Date Format Conversion

Did you import dates formatted as text (e.g., "2026.11.25" or "25-10-2026") that Excel fails to recognize as valid dates?

1
Select the column.
2
Open Text to Columns.
3
Click Next, then Next to jump directly to Step 3.
4
Select Date and pick the source format (e.g., YMD).
5
Click Finish.

Excel instantly converts all text strings into true, mathematical Excel serial dates without breaking them into columns!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Where on the Ribbon is the Text to Columns tool located?

Question 2

What happens if you parse a column containing leading-zero postal codes (e.g., "01234") and leave the column format as 'General'?

Question 3

What does checking 'Treat consecutive delimiters as one' do during delimited parsing?

Question 4

How can you ensure that Text to Columns does not overwrite your original source data column?

Question 5

How can Text to Columns be used to repair a column of stubborn text dates without splitting them?

Next Lesson

Data Consolidation and 3D Sheet Formulas

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Removing Duplicates and Cleaning Invisible Spaces	Data Consolidation and 3D Sheet Formulas

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Data Consolidation and 3D Sheet Formulas

Advanced Text to Columns (Delimited and Fixed Width)

Beginner12 min read•Updated: Jul 11, 2026

Study Materials Cheatsheet

Advanced Text to Columns (Delimited and Fixed Width)

1. Delimited vs. Fixed Width

Visual Architecture Blueprint

+-----------------------------------+-----------------------------------+
| Delimited Parsing                 | Fixed Width Parsing               |
+-----------------------------------+-----------------------------------+
| Fields are separated by specific  | Fields align at consistent        |
| characters: commas, tabs, pipes   | character counts (e.g., chars 1-10|
| (|), or semicolons.               | is ID, chars 11-30 is Name).      |
+-----------------------------------+-----------------------------------+

2. Step-by-Step: Delimited Parsing

1
Select the single column containing your combined data (e.g., 'A2:A500').
2
Go to Data > Data Tools > Text to Columns.
3
Step 1 of 3: Choose Delimited > Click Next.
4
Step 2 of 3 (Delimiters):

Check Comma, Tab, or check Other and type custom delimiters like pipe (|) or hash (#).
Check Treat consecutive delimiters as one (prevents empty blank columns when multiple spaces separate words).

1
Step 3 of 3 (Column Data Formats):

Highlight each preview column to set its specific data format:
Text: Critical for Employee IDs, Zip Codes, and phone numbers with leading zeros (e.g., "00451")! If left as "General", Excel converts "00451" into the number "451", destroying the leading zeros!
Date: Select format matching incoming data (e.g., DMY or MDY).
Do not import column (skip): Excludes unwanted fields from being pasted.
Destination: Select '$B$2' so your original raw Column A remains intact as a backup!

1
Click Finish.

3. Fixed Width Parsing

When data originates from legacy mainframe COBOL systems without delimiters:

1
Select Fixed width in Step 1.
2
In Step 2, Excel displays a visual ruler.
3
Click the ruler to insert break lines with arrows.
4
Drag break lines to adjust column widths, or double-click a line to remove it.
5
Click Finish.

4. Secret Pro Tip: Rapid Date Format Conversion

Did you import dates formatted as text (e.g., "2026.11.25" or "25-10-2026") that Excel fails to recognize as valid dates?

1
Select the column.
2
Open Text to Columns.
3
Click Next, then Next to jump directly to Step 3.
4
Select Date and pick the source format (e.g., YMD).
5
Click Finish.

Excel instantly converts all text strings into true, mathematical Excel serial dates without breaking them into columns!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Where on the Ribbon is the Text to Columns tool located?

Question 2

What happens if you parse a column containing leading-zero postal codes (e.g., "01234") and leave the column format as 'General'?

Question 3

What does checking 'Treat consecutive delimiters as one' do during delimited parsing?

Question 4

How can you ensure that Text to Columns does not overwrite your original source data column?

Question 5

How can Text to Columns be used to repair a column of stubborn text dates without splitting them?

Next Lesson

Data Consolidation and 3D Sheet Formulas

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Removing Duplicates and Cleaning Invisible Spaces	Data Consolidation and 3D Sheet Formulas

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next