Text to Columns - Split an import into fields
Use the desktop wizard to separate pipe-delimited records while preserving four-character employee references and protecting destination cells.
By the end, you can
- Choose Delimited and the actual separator.
- Set an identifier field to Text before conversion.
- Understand a one-time conversion versus a refreshable result.
Before you start: Know the source delimiter and the required field types. This walkthrough uses Windows desktop Excel.
In this lesson
Split according to the import's format
Text to Columns converts a text column into separate fields through a wizard. A people export may join name, employee reference, and department with pipes. The delimiter and field-type choices determine the result; the wizard does not know that 0042 is an ID rather than the number 42.
Before: one field holding three values
Anika|0042|Sales| A | |
|---|---|
| 1 | Imported line |
| 2 | Anika|0042|Sales |
| 3 | Dev|0071|Finance |
| 4 | Meera|0105|Operations |
Convert the records with explicit types
- Prepare empty destination columns C:E with headers Employee, Reference as text, and Department in row 1. Select only A2:A4 as the source records.
- Choose Data > Text to Columns. Select Delimited and then Next.
- Select Other and enter | as the delimiter. Clear unrelated delimiters and inspect the three-field preview.
- Select Next. In the data preview, click the second field and choose Text for its Column data format so 0042 does not become numeric 42.
- Set Destination to $C$2. Verify that C2:E4 contains no existing records that would be overwritten.
- Select Finish and compare all three output fields with the expected table. Check each zero-prefixed reference.
Anika| C | D | E | |
|---|---|---|---|
| 1 | Employee | Reference as text | Department |
| 2 | Anika | 0042 | Sales |
| 3 | Dev | 0071 | Finance |
| 4 | Meera | 0105 | Operations |
Choose the method that matches the source
- Use Delimited when a known separator marks fields; use Fixed width only when character positions genuinely define boundaries.
- Preview quote qualifiers and consecutive delimiters carefully. Skipping a meaningful empty field can shift the following attributes into the wrong columns.
- For date fields, choose the correct date order when the wizard offers it; General parsing may depend on regional settings.
- For a repeatable import, Power Query can record transformations. TEXTSPLIT offers a recalculated formula result in supported newer Excel versions.
Common mistakes and troubleshooting
The workspace does not implement the native wizard or its type-conversion options. No TEXTSPLIT or positional-text challenge is presented as a Text to Columns simulation.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.