Display is mistaken for stored text
A line break is an actual character. A space is nonempty text. A case-insensitive comparison can conceal a capitalization difference.
Build and inspect line-separated address labels
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill E2:E10 separately. B2:B5 accept true blanks or nonempty Unicode text up to 40 code points, without embedded control characters. Preserve every literal space. B6 is a numeric whole printable ASCII code 33..126. E2 is CHAR(B6), E3 is its CODE round trip. E4 joins nonempty address slots with LF; E5 retains four fixed slots. E6 counts compact lines (zero for an empty result), E7 fixed line positions and E8 the first stored LF code read from E5 with MID after the first source line. E9 joins the marker and compact address with LF and E10 replaces those LF separators with space-pipe-space. Wrapped cells show the text in the actual grid; stored results and exports are checked independently.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: E2, E3, E4, E5, E6, E7, E8, E9, E10. Browser calculations may differ from your Excel version.
Inspect current stored results and formulas
| Cell | Current value | Applied formula |
|---|---|---|
| E2 | — | No formula |
| E3 | — | No formula |
| E4 | — | No formula |
| E5 | — | No formula |
| E6 | — | No formula |
| E7 | — | No formula |
| E8 | — | No formula |
| E9 | — | No formula |
| E10 | — | No formula |
Change an actual source value
B2:B5 accept true blanks or nonempty Unicode text up to 40 characters without control characters. Spaces stay literal. B6 is a stored whole printable ASCII marker code from 33 through 126; CODE is not applied to the Unicode address text.
This draft matches the current cell.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Use actual LF characters, compare compact and fixed-slot joins, then inspect an ASCII marker without losing the original text.
THE QUICK ANSWER01
Use actual LF characters, compare compact and fixed-slot joins, then inspect an ASCII marker without losing the original text.
E2
=CHAR(B6)E3
=CODE(E2)E4
=TEXTJOIN(CHAR(10),TRUE,B2:B5)E5
=TEXTJOIN(CHAR(10),FALSE,B2:B5)E6
=IF(E4="",0,1+LEN(E4)-LEN(SUBSTITUTE(E4,CHAR(10),"")))E7
=1+LEN(E5)-LEN(SUBSTITUTE(E5,CHAR(10),""))E8
=CODE(MID(E5,LEN(B2)+1,1))E9
=TEXTJOIN(CHAR(10),TRUE,E2,E4)E10
=SUBSTITUTE(E9,CHAR(10)," | ")For the example data
E2 AE3 65E4 Harbor Studio
14 Willow Road
Bristol BS1 2ABE5 Harbor Studio
14 Willow Road
Bristol BS1 2ABE6 3E7 4E8 10E9 A
Harbor Studio
14 Willow Road
Bristol BS1 2ABE10 A | Harbor Studio | 14 Willow Road | Bristol BS1 2AB
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Keep the original source intact
Compare the derived outputs beside the original text. Capitalization, spaces and actual missing cells are different decisions; a readable display alone does not prove that the stored text is correct.
Use an explicit output policy
Every output follows a declared case or line-separator rule. Change the actual source or control cell and inspect the recalculated report before exporting your current workbook.
Check the complete current report
Independent source-only checks cover eight retained populations and three changed-source probes. Both the formula copy and values-only copy retain the applied source and current results; teaching demonstrations alone do not award credit.
Read the full step-by-step walkthrough
- Inspect the original address slots
B2:B5 are four text-or-blank source slots. Spaces are text and must remain; missing cells stay truly empty. Unicode address text is retained without applying a character-code conversion to it.
- Turn a numeric ASCII marker into text
B6 is a whole printable ASCII code from 33 through 126. CHAR creates its one-character marker; CODE reads the first character and must return the original numeric code. This is not a general Unicode encoding lesson.
- Join nonempty address lines with a real LF
CHAR(10) is the separator. TRUE skips genuine missing cells; a source containing a space remains a nonempty line. The result is one text value with stored LF characters.
- Keep four physical slots for a fixed-layout export
FALSE retains separators around empty source slots. Even four missing slots produce three LF characters and four empty line positions. This is a different output contract.
- Count stored separators and reconcile line positions
Subtracting lengths before and after removing LF counts the actual separators. The compact empty string has zero address lines; the fixed-slot string always has four positions. E8 reads the first separator from the actual fixed-slot output, just after the first original line.
- Build a marker label and inspect the same text on one line
E9 places the marker before the compact address, skipping an empty address block. E10 substitutes a visible divider for LF to inspect spacing and blank policy without changing E9.
Things to watch
A transformation is treated as a correction
A consistent case policy is useful only when it fits the destination. Preserve reviewed identity spellings and inspect punctuation or acronym changes.
A fixed result replaces a source-dependent formula
Current data, all retained sources and independent changed controls must produce the complete report.
About this example
Original bounded browser practice. Native regional rendering, calculation and edit/save are not certified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.