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.
This guide provides syntax, examples, edge cases, and best practices, making it one of the most complete TEXTJOIN references available for LibreOffice Calc.
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.