How to Concatenate Text in LibreOffice Calc

Combining text from multiple cells is a fundamental spreadsheet task that can be accomplished in several ways. This article provides a clear overview of the text concatenation methods available in LibreOffice Calc, including the ampersand (&) operator, the classic CONCATENATE function, the modern CONCAT function, and the advanced TEXTJOIN function designed for ranges and custom delimiters.

The Ampersand (&) Operator

The ampersand operator is the quickest and most common method for joining two or more text strings or cell references in LibreOffice Calc.

• Syntax: =text1 & text2 & text3
• Example: =A1 & " " & B1
• Use Case: Best for quickly merging a few cells or combining cell values with fixed text strings.

The CONCATENATE Function

The CONCATENATE function is the traditional formula used to combine individual text strings into a single string.

• Syntax: =CONCATENATE(text1, text2, ...)
• Example: =CONCATENATE(A1, " - ", B1)
• Characteristics: It accepts up to 255 text arguments. It does not automatically insert spaces or delimiters, nor does it natively support contiguous cell ranges (e.g., CONCATENATE(A1:A5) does not join the range into a single cell).

The CONCAT Function

The CONCAT function serves as a newer, more versatile alternative to CONCATENATE.

• Syntax: =CONCAT(text1, text2, ...) or =CONCAT(range)
• Example: =CONCAT(A1:A5)
• Characteristics: Unlike CONCATENATE, CONCAT accepts entire cell ranges directly without requiring each cell to be separated by a comma. However, it still combines items without delimiters.

The TEXTJOIN Function

The TEXTJOIN function is the most powerful concatenation tool in LibreOffice Calc, designed to handle cell ranges, delimiters, and blank cells efficiently.

• Syntax: =TEXTJOIN(delimiter, skip_empty, text1, [text2], ...)
• Arguments:
  • delimiter: The text string (such as a comma, space, or hyphen) to place between each combined value.
  • skip_empty: A boolean value (TRUE or FALSE). If TRUE, empty cells in the range are ignored.
  • text1, text2, ...: The text values or cell ranges to join.
• Example: =TEXTJOIN(", ", TRUE, A1:A10)
• Use Case: Best when creating formatted lists from large ranges of data while automatically skipping blank cells.