Try the example, then check the result.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
Quick answer. In a separate clean column use =SUBSTITUTE(B2,CHAR(10)," ") and fill down. The replacement must contain one space: box followed by a line break followed by before becomes box before, not boxbefore.
Use the fictional practice file: wrapped-ticket-notes-practice.csv, plus the independent answer check. Work in a copy. This guide stands on its own without the video.
Scope and limits. The quoted practice CSV contains LF line breaks, CHAR(10). A different export may contain CR, CHAR(13), too. Inspect the raw character before reusing the formula; this exercise handles LF only.
1. Read the raw cells first
Read the three source rows in column B. The CSV quotes the two cells that contain an embedded LF, so the line break is part of each cell, not a new record. Keep B2:B4 intact to make before-and-after comparison possible.
Checkpoint: Two cells have one LF; the third has none.
2. Add a separate clean column
Type Clean note in C1. Leave B2:B4 unchanged. Work in a private practice copy; once all outputs pass, you may choose to copy the results as values for another system, but the source exercise retains both columns.
Checkpoint: Raw B column stays unchanged.
3. Replace LF with one space
Enter =SUBSTITUTE(B2,CHAR(10)," ") in C2 and fill it to C4. The formula changes every LF in each referenced note to one ordinary space. A blank replacement argument would glue adjacent words together and fail the answer check.
Checkpoint: Formula uses CHAR(10) and a one-space replacement.
4. Read each complete output phrase
Check the exact text, not merely the row height. C2 is Check box before packing, C3 is Call customer by Friday, and C4 is Ready for pickup. The original raw notes stay in column B for a reversible comparison.
Checkpoint: All three phrases match the answer check.
Verify the result
Type Clean note in C1 and =SUBSTITUTE(B2,CHAR(10)," ") in C2, then fill to C4. C2 should be Check box before packing; C3 Call customer by Friday; C4 Ready for pickup. B2:B4 remain unchanged. Exactly two embedded LF characters were replaced across the three notes.
If it still does not work
If the break remains, inspect whether the source contains CHAR(13), CHAR(10), or both. Add a targeted second SUBSTITUTE only after checking the actual export. CLEAN may join meaningful words, so it is not the chosen solution here.
Official sources
- SUBSTITUTE function (checked 26 October 2026).
- CHAR function (checked 26 October 2026).
Any instructional panel without a marked actual-app capture is an illustrative diagram of the fictional exercise.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.