Cleaning imported data
TEXTJOIN function: combine optional address fields without double commas
Join street, optional unit, and city with a comma-space delimiter while automatically skipping genuinely blank cells—and check how zero and space-only cells behave.
Dispatch labels with an optional unit column
A shipping sheet stores Street in column A, an optional Unit in column B, and City in column C. Plain concatenation (A2 & ", " & B2 & ", " & C2) leaves an ugly ", , " whenever column B is blank.
| Street (A) | Unit (B) | City (C) |
|---|---|---|
| 14 Cedar Rd | Varna | |
| 8 Harbour Way | Unit 3B | Burgas |
Set ignore_empty to TRUE
TEXTJOIN(", ", TRUE, A2:C2) places ", " only between non-empty values. Row 2 omits the blank unit cleanly ("14 Cedar Rd, Varna"), while row 3 includes all three parts ("8 Harbour Way, Unit 3B, Burgas"). Setting the second argument to FALSE keeps the empty slot ("14 Cedar Rd, , Varna").
=TEXTJOIN(", ",TRUE,A2:C2)Zeros are kept, and space-only cells are not empty
Ignore_empty skips empty cells and empty formula strings (""), not numeric 0: if B2 is 0 (for ground floor 0), TEXTJOIN returns "14 Cedar Rd, 0, Varna". Conversely, if B2 contains a stray space (" "), it is not empty and produces "14 Cedar Rd, , Varna"—clean imported spaces first if blanks look polluted.
Try the example
Use the original values or change them to test your understanding.