A comparison of options to concatenate in spreadsheets

Introduction:

We often came across the requirement of joining texts or concatenation in spreadsheets. This requirement might arise due to need to creating a key for lookup operations or might be to create full name, with first, second and third name present in various column of the sheets. The procedure of joining text is help in creating custom messages where information is present in various columns and we want to join them (concatenate means joining) to produce a meaningful message.

Before we proceed to the comparison of the functions here is an example of how text join can be a really handy option to master.

Consider a situation where you want to inform a customer regarding the points he has gained by his purchase over the last year. The following table show the breakup of the points gained by dollar purchases.

We can set up a formula to produce automated text like above. We can see from the description of the formula that a VLOOKUP is used to fetch the purchases from the customer and the purchase table and the discount is found using a LOOKUP function.

The final result is presented as a consolidated sentence in cell B13.

We will be examining the text joining options available in Google Sheets, MS Excel and Open Office.

MS Excel:

There is no doubt that this one is the most used spreadsheet program currently. When it comes to MS Excel, we have following options available to use:

1. Ampersand to join Text (use of Dollar sign)
2. Concatenate() function
3. Textjoin() function
4. VBA or use of User Defined Functions (UDF)

The use of ampersand is one of the oldest example of concatenation. Whenever you don’t have an option available to join text you can revert to use of this one – The use is as simple as follow:

“A”&”B” …will give  you AB

 The next option is the use of function Concatenate(). This function has been available in Excel for a long time and we can use it join text available in cells, by selecting them individually or directly entering text within double quotes. Thus its use is like:

=CONCATENATE(“A”,A1,”B”)

 If A1 contains an underscore sign, the result will be A_B.

 The disadvantage of using this function is that you have to select individual cells, you can’t work with a range.

The use of Textjoin() function makes up for the disadvantage of selecting individual cells. Here you can work by selecting an entire range and a delimiter to join text. For example if range A1:A3 contains A, B and C respectively, the function would have worked like:

=TEXTJOIN(“,”,,A2:A4)

..where comma is the delimiter. Thus you can simply select a range and delimiter and an option where to select the empty cells within that range or not (most of the time, you won’t select them!)

The last option is to use VBA to write a UDF to do concatenation. There are various udfs already available online – one of my favourite is by Chandoo – the concat() function. But there are lot of others as well giving you more control over what you want to achieve. Simply google it!.

Open Office Calc:

OO Calc is one of the open source spreadsheet programs that does not requires any buying charges. This software comes with similar features like MS Excel and functions that are compatible with other spreadsheet programs.

In order to join text, we have following options available in Open Office:

1. An Ampersand to Join the text.
2. The CONCATENATE() function to join the text.

Use of ampersand is the most common choice if you want to join just a couple of texts, for larger sets, you may revert to CONCATENATE() function. However, this use is again limited by selecting individual cells. You can not select an array. (Open Office Version 3.2.0)

Google Sheets:

Google markets its online spreadsheet program as Google Sheets. Besides being free for a common user, it provides excellent sharing features that makes it best for collaborate working. But here we will discuss the text joining options available with it.

We have four options available with Google Sheets:

1. The use of Ampersand
2. Using CONCAT() function
3. Bringing in CONCATENATE() function
4. Use of TEXTJOIN() function

We have already seen how we can use the ampersand sign, the case is same here again. We put an ampersand or dollar sign between texts or cell references to join them, (See cell B1, in the picture).

The CONCAT() is a function (usage shown in cell B2) that is unique to google sheets, it is used to join just two cells. Taking them up one by one, separated by commas. So it is just a limited-value version of a more used function CONCATENATE().

The third option available at our disposal is the CONCATENATE() function itself. The use of this function is shown is cell B3.

The last option we have available is the TEXTJOIN() function that gives us the more control over this operation, we can use a delimiter, an option to skip or include blank cells and facility to select a range rather than individual cells.

The use is shown in cell B4.

Comparison Table:

Here is a comparison of all the functions that we have discussed.

Conclusion:

We hope you will like this post, if you like any other topic discussed please leave it as a comment. Thank you.