TEXTJOIN Function (LibreOffice Calc)
The TEXTJOIN function in LibreOffice Calc joins text values using a specified delimiter, with optional control over whether to ignore empty cells. It is the modern, flexible replacement for CONCAT and CONCATENATE.
Verification status: Consult the documentation and examples below. Native application testing has not been documented for this guide. How formula examples are checked
What the TEXTJOIN Function Does
- Joins text values using a delimiter
- Accepts ranges and arrays
- Can ignore or include empty cells
- Automatically converts numbers to text
- Ideal for building lists, labels, and structured output
It is designed to be flexible, powerful, and modern.
Syntax
TEXTJOIN(delimiter; ignore_empty; text1; text2; ...)
Arguments
-
delimiter:
The text to insert between joined values. -
ignore_empty:
TRUE → skip empty cells
FALSE → include empty cells as empty strings -
text1, text2, …:
One or more text values, cell references, or ranges.
Basic Examples
Join with a comma
=TEXTJOIN(", "; TRUE; A1; B1; C1)
Join a range
=TEXTJOIN(", "; TRUE; A1:A5)
Join with a space
=TEXTJOIN(" "; TRUE; A1; B1)
Join text and numbers
=TEXTJOIN(" - "; TRUE; "Year"; 2024)
Returns "Year - 2024".
Advanced Examples
Join a list into a CSV string
=TEXTJOIN(", "; TRUE; A1:A10)
Join only non-empty values
=TEXTJOIN(" / "; TRUE; A1:C1)
Join with conditional logic
=TEXTJOIN(" "; TRUE; A1; IF(B1=""; ""; "(" & B1 & ")"))
Build a full name with optional middle name
=TEXTJOIN(" "; TRUE; A1; B1; C1)
Build a file path
=TEXTJOIN("/"; TRUE; "home"; "user"; A1)
Build a structured code
=TEXTJOIN("-"; TRUE; LEFT(A1; 3); MID(A1; 4; 2); RIGHT(A1; 2))
Join cleaned text
=TEXTJOIN(" "; TRUE; TRIM(A1); TRIM(B1); TRIM(C1))
Join with line breaks
=TEXTJOIN(CHAR(10); TRUE; A1:A5)
(Enable wrap text.)
Join with dynamic delimiter
=TEXTJOIN(B1; TRUE; A1:A10)
Join with SUBSTITUTE preprocessing
=TEXTJOIN(", "; TRUE; SUBSTITUTE(A1:A5; "_"; " "))
Join with array filtering (Calc supports array formulas)
=TEXTJOIN(", "; TRUE; IF(A1:A10>0; A1:A10; ""))
Edge Cases and Behavior Details
TEXTJOIN can accept ranges (unlike CONCATENATE)
=TEXTJOIN(", "; TRUE; A1:A10)
TEXTJOIN treats numbers as text
=TEXTJOIN(", "; TRUE; 1; 2; 3) → "1, 2, 3"
ignore_empty = TRUE skips:
- Blank cells
- Empty strings ""
- Cells with formulas returning ""
ignore_empty = FALSE includes empty strings
=TEXTJOIN("-"; FALSE; "A"; ""; "B") → "A--B"
TEXTJOIN of an error propagates the error
=TEXTJOIN(", "; TRUE; "A"; #N/A) → #N/A
TEXTJOIN joins ranges in row-major order
A1:B2 joins as A1, A2, B1, B2.
Delimiter can be empty
=TEXTJOIN("", TRUE; A1:A5)
Equivalent to CONCAT(A1:A5).
TEXTJOIN does not trim automatically
Use TRIM or CLEAN if needed.
Common Errors and Fixes
Err:504 — Parameter list error
Occurs when:
- Missing delimiter
- Missing ignore_empty
- Wrong argument order
TEXTJOIN returns unexpected double delimiters
Cause:
- ignore_empty = FALSE
- Hidden empty strings
Fix:
Set ignore_empty = TRUE.
TEXTJOIN returns unexpected order
Cause:
- Range is joined row-by-row
Fix:
Transpose the range if needed.
TEXTJOIN returns #VALUE!
Cause:
- One of the arguments is an error
- Range contains an error
Best Practices
- Use TEXTJOIN for delimiter-aware joining
- Use CONCAT for simple joining without delimiters
- Use TRIM and CLEAN before joining
- Use SUBSTITUTE to normalize text before joining
- Use TEXTJOIN with IF for conditional assembly
- Use TEXTJOIN with ranges to build lists efficiently
Related Patterns and Alternatives
- Use CONCAT for simple joining
- Use CONCATENATE for legacy compatibility
- Use LEFT, RIGHT, MID for extraction
- Use SUBSTITUTE and REPLACE for editing
- Use TRIM and CLEAN for normalization
By mastering TEXTJOIN and its companion functions, you can build powerful, flexible, and highly structured text‑processing workflows in LibreOffice Calc.