Skip to content
Reading settings
ExcelIntermediate~25 min practiceLesson 16 of 34

Split and clean a delimited import

Split a pipe-delimited list without breaking names or losing identifier zeros.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Split four fields into an empty area, set their types and compare the result with the source.

Example

Split a staff sales extract

Type Raw in A1 and enter 00127|Maya Chen|West|1200, 00128|Jordan Lee|East|950 and 00129|Sam Patel|North|700 in A2:A4. The pipe separates fields; spaces inside the names belong to the names.

How it works

Choose the delimiter that separates fields. Here it is pipe, not space: splitting on spaces would break Maya Chen into two columns.

Set the output types field by field. StaffCode needs text to retain its zeros; Sales needs numbers for addition and comparison.

Steps

  1. Keep the Raw column unchanged on SourceText. Copy the three lines to A2:A4 on a working sheet and clear enough space for a four-column output.

  2. On desktop, select A2:A4 and choose Data > Text to Columns > Delimited. Under Other, enter |. Turn off the other delimiters.

  3. Check that the preview shows four fields per row. Set StaffCode to Text in the wizard and choose an empty destination, such as C2, before finishing.

  4. Add StaffCode, Rep, Region and Sales above the output. Check codes 00127–00129 and the complete names. Use ISNUMBER on each Sales value.

  5. If your edition lacks the wizard but supports TEXTSPLIT, enter =TEXTSPLIT(A2,"|") in an empty output area. Place each row’s anchor where the four-cell outputs will not overlap. Keep the identifiers as text and convert the Sales output to numbers as needed.

  6. Sum the Sales output and check 2850. Compare each row with SourceText, then note the delimiter, field types and method you used.

Try it yourself

  1. Add 00130|Ana Ruiz|East|1100 to SourceText and repeat the transformation on a fresh working area. Check the record count, column count and total.
  2. Create a disposable line with an extra pipe character inside the name and examine how the preview changes. Explain why a delimiter inside a field needs a documented quoting or escaping convention.
Show expected result
  • The original split contains three records and four fields, with sales total 2850 and unchanged identifiers beginning with zeros.
  • Adding Ana produces four records totaling 3950. An extra unescaped delimiter produces an unexpected field count and requires source-format clarification rather than silent acceptance.

If something goes wrong

Maya Chen splits into two fields.
Turn off Space in the delimiter settings and start again from SourceText. Pipe is the only separator in this extract.
The output replaced notes in neighboring columns.
Undo on the working copy and restore the notes. Choose an empty destination with room for all four fields before splitting again.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. Which delimiter should separate this sample?
2. What is the total after adding Ana 1100?
3. Which precaution prevents accidental overwrite?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Read these pipe-delimited lines and list the four output fields and their types. Flag leading-zero codes or an extra delimiter inside a name.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…