TEXTJOIN: combine values without messy separators

Joining names, addresses or codes is not difficult until empty cells start leaving doubled-up punctuation. TEXTJOIN lets you decide whether blanks should disappear.

Formula syntax

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

What goes into it

A quick example first

=TEXTJOIN(", ",TRUE,"East","West")

=TEXTJOIN(" | ",TRUE,A2:A10)

Build a readable address without empty separators

Enter these address components in A2:D2, leaving C2 truly empty.

TRUE skips empty cells; it does not clean a cell containing spaces. If a component contains only an ordinary space, clean it before joining. The separator is presentation text, so the comma inside quotation marks must survive any locale conversion.

Before you copy this into a real sheet

For dates or amounts, use TEXT with an explicit display format before joining; cell formatting alone is not carried into the result. Keep postal codes and IDs as text where leading zeros matter. A joined address is useful for labels, but it is a poor replacement for separate fields in a database because the separator can occur inside a street name. TEXTJOIN is available in Excel 2019 and newer supported versions; older workbooks may need concatenation with explicit blank handling.

Useful habits

• Set ignore_empty deliberately so blanks do not create repeated delimiters.
• Use TEXT with numbers/dates first when display formatting matters.
• For dynamic lists, TEXTJOIN can combine FILTER results.

Where people get caught out

• Expecting cell number formatting to carry into the joined text automatically.
• Using a delimiter that can also appear inside source values without considering ambiguity.

Other functions worth a look

A detail worth remembering

Decide what a blank means. Ignoring it can be useful in a label, but an export that expects a fixed number of fields may need placeholders instead.