This tutorial will teach you how to concatenate two or more strings in LibreOffice Calc with easy examples.
Often you need to join or concatenate two strings. They can be isolated, fixed strings or the values from cells. I will talk about using various examples because it’s easier to understand.
I will use two functions – a) CONCATENATE and b) TEXTJOIN. Also, I will use the “&” operator to join. Let’s take a look at the syntax:
CONCATENATE(string 1, string 2....) // You can refer to up to 255 strings or cells
CONCAT(string 1, string 2.....) // You can refer to up to 255 strings
Table of Contents
Concatenate using functions – CONCATENATE
Example 1
The following example joins two cell values – A1 and B1.
=CONCATENATE(A1,B1)
You can also get the same result using the “&” operator. The & operator also joins multiple cells.
=A1&B1
Example 2
You can also add a fixed string while joining strings, as I have done in this example.
=CONCATENATE(A1, " is ", B1)
Similarly, the following formulas return the exact same result.
=A1 & " is " & B1 //Returns Earth is beautiful
=CONCATENATE(A1, " ", "is", " ", B1) //Returns Earth is beautiful
Now, I think you get the idea of how to do the basic concatenation. Let’s talk about the next function i.e. TEXTJOIN.
Using TEXTJOIN to Concatenate Strings
The following function is TEXTJOIN which is also a powerful function. Here’s the syntax.
TEXTJOIN(delimiter, skip_empty_flag, String 1[; String 2][; … ;[String 253]] ) // you can refer up to 253 strings
- delimiter: The delimiter you want to add while joining between strings (it can be a fixed character or a range)
- skip_empty_flag: When set to 1 or TRUE, the empty strings will be ignored. If set to 0 or FALSE, empty strings or cells can be taken into consideration while joining
- String1 to ….253: The list of cells, ranges or strings to join
Example 3
The following example joins all the name components to create a complete name. Note the second parameter is TRUE (i.e. 1) , which means the empty cells can be ignored. Also, a single space should be added between name parts as a delimiter.
=TEXTJOIN(" ",1,A2:D2)
Now, if you make the second parameter FALSE, you can see empty cells are considered hence some extra space is added.
=TEXTJOIN(" ",FALSE(),A2:D2)
See the difference?
Let’s do a complex example.
Example 4
I have the following table with the Names with Teams numbers. How can you find the names of persons who belong to a team?
To do that, we need two concepts. They are named ranges and Array functions.
Named ranges are just the names which you give to a range of cells so that you can refer to them from any formula. I am assigning “Names” to cells B3:B9 and “Teams” to cells at C3:C9.
Once that is done, type the following formula on cell F3. Don’t press enter. Instead, press SHIFT+CTRL+ENTER after you complete typing the below formula. This will create an array formula with curly braces.
=TEXTJOIN(",",1,IF(Teams=E3,Names,""))
The array formula will evaluate the entire range instead of a single cell. Once you are done, double-click on the Cell handle to fill it up (don’t drag the handle).
And you should see the results below.
But how does it work? Let me break it down for you.
IF(Teams=E3,Names,""): It searches the E3, E4 and E5 in the entire range C3:C9, which we named as “Teams”, if found, it returns the corresponding values in an array from Names.
Then the TEXTJOIN function helps to add a comma, and the second parameter as 1, helps to skip the empty search results. And finally, you get the desired result.
Preety neat? Isn’t it?
Wrapping up
That wraps up the tutorial on concatenating strings in LibreOffice Calc with two functions and some examples. I hope you understand the concept of string concatenation and are ready to find solutions to your problems.
Do let m know in the comment box if you have any questions.
Cheers.