Sometimes, instead of splitting cells, you need to merge cells containing different information. In this tutorial, we will learn three ways using three functions to merge cells into a single cell, with additional formatting, in Google Sheets.
Combining Strings - CONCATENATE() Function
The CONCATENATE() function is a simple but powerful function that can combine both cell content and user-specified string. Let us have a simple example:
The formula used is =CONCATENATE(C2,B2). We can improve it by adding extra characters. Let us try the formula =CONCATENATE(C2,”, “,B2) :
Combine Strings and Numbers Together Using TEXTJOIN() Function
The real challenge comes from combining both strings and numbers together. Google Sheets store numbers in different ways and that gives us a wide range of styles depending on what the numbers are for.
This means that the original number that was entered into the spreadsheet can be wildly different from what is presented to us by Google Sheets.
One quick way to combine string and numbers from different cells together is by using the TEXTJOIN() function. The TEXTJOIN() function requires you to specify a separator and the range of cells to combine. Its syntax looks as follows:
The first portion is where you specify the delimiter between the string and numbers to combine, enclosed in double-quotes.
The <ignore_empty></ignore_empty> is where you specify whether to skip empty cells or not. This is not optional; if you did not leave a portion for <ignore_empty>, the formula will return an error. This is set to FALSE by default. There are cases that you might have to set it to TRUE. To see how it affects the final output, check the image below.</ignore_empty>
The last portion is where you can specify the specific cells or an array of cells to include.
Let us have an example involving both names and currency. We want to write down the name of the person written in column A, followed by his/her payment listed in column E. The two data are separated with a “ - “ delimiter. We will use the following formula:
=TEXTJOIN(" - ", true, A2, E2)
The result will look like this:
There is a disadvantage to TEXTJOIN(), however. It uses the same delimiter to separate the entries to be merged. You will learn another technique in the next section. Scroll on!
Combine String and Numbers Together with More Flexibility
While the TEXTJOIN() function allows you to seamlessly combine strings and numbers into one cell, the main disadvantage is that it uses the same delimiter to combine different cells. If you need more flexibility in delimiters, here is another technique.
Let us have an example involving currency. In order to correctly print numbers involving money, we need to use the TEXT() function. The TEXT() function requires the cell where the number is stored and the format that the function will use to convert the number into a string. For our example below, we used TEXT(E2,"$#.00") to convert the number into a string. We used it as part of the formula for combining strings and numbers:
=A2&" - "&TEXT(E2,"$#.00")
To further demonstrate its flexibility, we will merge all three payments with the same name into one cell using the following formula: