Choose Data → Text to Columns → Delimited, select the separator and specify a safe destination for the output.
Visual walkthrough
Data›Text to Columns›Delimited
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
- Copy the source column or make a backup. Keep the destination columns empty.
- Select the text and choose Data → Text to Columns → Delimited.
- Choose the correct delimiter. For 001|Infrastructure, select Other and enter |. Check the preview.
- 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