Text & String Cleaning
Add Line Breaks in a Formula with CHAR(10) & TEXTJOIN (Multi-Line Address Labels)
Insert multi-line carriage breaks inside spreadsheet formulas using CHAR(10) and TEXTJOIN(CHAR(10), TRUE, ...) so blank apartment or suite rows collapse automatically without empty gap lines.
Avoid ugly double blank lines when Suite or Apt Line 2 is empty!
When building shipping labels, invoice "Bill To" blocks, or multi-line KPI cards, concatenating cells manually with B2 & CHAR(10) & B3 & CHAR(10) & B4 creates an ugly empty blank line in the middle of the label whenever B3 (Apartment / Suite / Floor) is blank! Even worse, older Macintosh tutorials sometimes mention CHAR(13) (carriage return), whereas modern Windows Excel, Excel for Mac (365/2019+), Excel for the web, and Arte Sheets ALL standardize on ASCII line feed CHAR(10) with Wrap Text enabled.
Passing CHAR(10) as the delimiter inside TEXTJOIN(CHAR(10), TRUE, B2:B5) with ignore_empty = TRUE solves both headaches in one shot: non-blank lines stack cleanly with newline separators while blank Suite/Attention cells vanish automatically! Combine city, state, and postal code on the final line with B5 & ", " & B6 & " " & B7 so your courier address block formats cleanly every time.
| Recipient Field | Example A (With Suite) | Example B (Blank Suite) | TEXTJOIN(CHAR(10), TRUE, ...) Behavior |
|---|---|---|---|
| Recipient Name (B2) | Elena Rostova | Elena Rostova | Line 1 |
| Street Address (B3) | 742 Evergreen Terrace | 742 Evergreen Terrace | Line 2 |
| Suite / Apt (B4) | Suite 400 | (Blank) | Included in A; skipped cleanly in B! |
| City, State ZIP (B5) | Seattle, WA 98101 | Seattle, WA 98101 | Line 4 in A; shifts up to Line 3 in B! |
Multi-Line Address Block Skipping Blank Lines Automatically
Joins B2:B5 separated by ASCII newline CHAR(10), ignoring any empty optional lines.
=IF(COUNTA(B2:B5) = 0, "Empty Address", TEXTJOIN(CHAR(10), TRUE, B2:B5))Count Rendered Non-Empty Lines in the Output Label
Counts line breaks via LEN - LEN(SUBSTITUTE(..., CHAR(10), "")) + 1 to verify label height.
=IF(COUNTA(B2:B5) = 0, 0, LEN(TEXTJOIN(CHAR(10), TRUE, B2:B5)) - LEN(SUBSTITUTE(TEXTJOIN(CHAR(10), TRUE, B2:B5), CHAR(10), "")) + 1)Try the example
Use the original values or change them to test your understanding.