Syntax for merging referenced columns

Sheet_1 has columns A to F (yellow).

On Sheet_2 the cells of these should be referenced to in columns A to E, with column E referencing both columns E and F of Sheet_1, separated by the word “and” (red). The result should look like this:

What syntax do I use to achieve that?

For A1 in Sheet2: =$Sheet1.A1 (and similar for columns B to D).
For E1 in Sheet2: =$Sheet1.E1&" and "&$Sheet1.F1 or =TEXTJOIN(" and ",1,$Sheet1.E1:F1).

See LibreOffice Help on TEXTJOIN function.

Tested with version 7.0.6.2 (x64) on Linux 4.12.

6.4.10 Infix Operator “&”

Summary: Concatenate two strings.
Syntax: Text Left & Text Right
Returns: Text
Constraints: None
Semantics: Concatenates two text (string) values.
Note: The infix operator “&” is equivalent to CONCATENATE(Left,Right).
Source: ODFF (OpenDocument Format Formula) specification from the OASIS web site (2021-09-28).

Fantastic, thank you!

1 Like

Note that link points to the previous v1.2 specification, the current spec is v1.3 (part4-formula).

2 Likes