/

Concatenate Two or More Strings in LibreOffice Calc (With Examples)

1861 views
2

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

Concatenate using functions – CONCATENATE

Example 1

The following example joins two cell values – A1 and B1.

=CONCATENATE(A1,B1)
CONCATENATE example 1
CONCATENATE example 1

You can also get the same result using the “&” operator. The & operator also joins multiple cells.

=A1&B1
CONCATENATE example 2
CONCATENATE example 2

Example 2

You can also add a fixed string while joining strings, as I have done in this example.

=CONCATENATE(A1, " is ", B1)
CONCATENATE example 3
CONCATENATE example 3

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)
textjoin example 1
textjoin example 1 to concatenate strings in Calc

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)
textjoin example 2
textjoin example 2

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?

Example 4 - the problem
Example 4 – the problem

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.

Named ranges - assign name to cells
Named ranges – assign name to cells

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.

Final Result
Final Result

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.


Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.


Join our Telegram channel and stay informed on the move.


arindam

Creator of libreofficehelp.com. The aim is to help as many people with easy-to-understand tutorials. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Yman

Hi there…
I’m trying to make ONE formula (single cell) do 3 things at a time…

I need to have a COLUMN which will give me a SINGLE group. I need to SORT them. Like 1, 2, 3, 4, etc… There will be MANY of each number… Simple enough… BUT:

Once then the sorting is done, I need to have EACH NUMBER assigned BY ORDER (more EXPENSIVE going down to the LEAST EXPENSIVE) a “1 to x” number.

Once this is done, it should eliminate the errors completely.

This is the formulas that I’m trying to get… It is not obvious… I’ve been looking for 3 weeks, and I just cant do it.

ANY help would be MUCH appreciated.

Thanks!

2
0
Would love your thoughts, please comment.x