CONCAT Function (LibreOffice Calc)
The CONCAT function in LibreOffice Calc joins multiple text values into a single string. It is the modern replacement for CONCATENATE and is essential for building dynamic text, labels, codes, and structured output.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✖ |
| Wps | ✔ |
| Zoho | ✔ |
What the CONCAT Function Does ▾
- Joins multiple text values into one string
- Accepts ranges and arrays
- Automatically converts numbers to text
- More modern and flexible than CONCATENATE
- Does not insert delimiters (use TEXTJOIN for that)
It is designed to be simple, modern, and widely compatible.
Syntax ▾
CONCAT(text1; text2; ...)
Arguments
- text1, text2, …:
One or more text values, cell references, or ranges.
Basic Examples ▾
Join two strings
=CONCAT("Hello"; "World")
Returns "HelloWorld".
Join with a space
=CONCAT("Hello"; " "; "World")
Join cell values
=CONCAT(A1; B1)
Join a range
=CONCAT(A1:A3)
If A1:A3 = {“A”; “B”; “C”}, returns "ABC".
Join text and numbers
=CONCAT("Year: "; 2024)
Returns "Year: 2024".
Advanced Examples ▾
Build a full name
=CONCAT(A1; " "; B1)
Build a file path
=CONCAT("/home/user/"; A1; "/"; B1)
Build a SKU code
=CONCAT(LEFT(A1; 3); "-"; RIGHT(B1; 4))
Build a dynamic label
=CONCAT("Total for "; A1; ": "; B1)
Join cleaned text
=CONCAT(TRIM(A1); " "; TRIM(B1))
Join variable-length components
=CONCAT(A1; IF(B1=""; ""; " - " & B1))
Join extracted substrings
=CONCAT(LEFT(A1; 2); MID(A1; 4; 3); RIGHT(A1; 2))
Join with conditional logic
=CONCAT(A1; IF(C1>0; " (Active)"; " (Inactive)"))
Join a range of codes
=CONCAT(A1:A10)
(Produces a continuous string.)
Edge Cases and Behavior Details ▾
CONCAT does not insert delimiters
Use TEXTJOIN when you need separators.
CONCAT treats numbers as text
=CONCAT(1; 2; 3) → "123"
CONCAT ignores empty strings
=CONCAT("A"; ""; "B") → "AB"
CONCAT includes blank cells as empty strings
=CONCAT(A1; A2)
If A2 is blank, it contributes nothing.
CONCAT of an error propagates the error
=CONCAT("A"; #N/A) → #N/A
CONCAT of a range joins in row-major order
A1:B2 joins as A1, A2, B1, B2.
CONCAT cannot specify an instance or pattern
Use SUBSTITUTE or REPLACE for targeted editing.
Common Errors and Fixes ▾
CONCAT returns unexpected order
Cause:
- Range is joined row-by-row
- User expected column-by-column
Fix:
Transpose the range or specify cells individually.
CONCAT returns too many characters
Cause:
- Hidden spaces
- TRIM not applied
- CLEAN not applied
CONCAT returns #VALUE!
Cause:
- One of the arguments is an error
- Range contains an error
Best Practices ▾
- Use CONCAT for simple joining without delimiters
- Use TEXTJOIN when you need separators
- Use TRIM and CLEAN before concatenation
- Use LEFT/RIGHT/MID to build structured strings
- Use SUBSTITUTE to clean text before joining
- Use CONCAT with IF for conditional assembly
Related Patterns and Alternatives ▾
- Use TEXTJOIN to join with delimiters
- 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 CONCAT and its companion functions, you can build clean, flexible, and powerful text‑processing workflows in LibreOffice Calc.