Office Dễ Hiểu

How to split text into columns in Excel

Separate delimited text and preserve leading zeros in record codes.

Choose Data → Text to Columns → Delimited, select the separator and specify a safe destination for the output.

Visual walkthrough

DataText to ColumnsDelimited

Before

A2 contains 001|Infrastructure.

After

C2 contains 001 and D2 contains Infrastructure.

Check the result

Set the code column to Text before Finish to preserve 001.

Step-by-step instructions

  1. Copy the source column or make a backup. Keep the destination columns empty.
  2. Select the text and choose Data → Text to Columns → Delimited.
  3. Choose the correct delimiter. For 001|Infrastructure, select Other and enter |. Check the preview.
  4. Choose C2 as the destination. Select the code column in the preview and set its format to Text before choosing Finish.

Frequently asked questions

Why did 001 become 1?

Excel interpreted the code as a number. Repeat the import from the original text and set the code column to Text in the wizard.

Reference: Microsoft Support