The CONCATENATE function in Google Sheets concatenates multiple pieces of data. This feature is useful when working with large sets of information, each requiring the same processing.
For example, you can use the CONCATENATE function if your spreadsheet has one column for first name and another for last name, but you want them to be concatenated into one cell with both names. You can do this manually by entering each name, or you can use CONCATENATE for automation.
There are many other examples of the CONCATENATE function, so we’ll look at some of them below.
Simple example
In its simplest form, the CONCATENATE function concatenates two datasets without any other parameters. This is possible with this simple form:
= CONCATENATE (A1, B1)
Of course, in this example, we assume the first name is in cell A1 and the second is in cell B1. You can adapt this to your spreadsheet by replacing these links with your own.
Pressing Enter in this particular example will bring up MaryTruman. As you can see, the first name is right next to the last name. The CONCATENATE function did its job in this scenario, but there are other options you can include to enhance its capabilities, such as adding a space or data from other cells.
– /
Use a space in the CONCATENATE formula
It is important to know how to use spaces with CONCATENATE, because datasets are often not configured the way you want. As in our example above, we want the name to look presentable by adding a space between the two cells.
Spaces are included in this Google Sheets feature using double quotes:
= CONCATENATE (A1, ””, B1)
If you don’t see here, there is a space in the quotes. The idea behind using quotes is that you enter data manually, rather than fetching data from a spreadsheet.
In other words, A1 and B1 are clearly already part of the spreadsheet, so you can reference them by entering them as they are (cell letter plus cell number). However, to include your own data in a formula, you must enclose it in quotation marks.
Add text to the CONCATENATE formula
The CONCATENATE function can do more than just concatenate a pair of cells and put a space between them. Below is an example of using CONCATENATE to form an entire sentence using cell data.
In this example CONCATENATE function, we concatenate the county and its ordinal, but instead of leaving it as it is, we use spaces and our own hand-entered data to create a complete sentence:
= CONCATENATE (A2; is “; “ranked”; C2; “compared to other ceremonial districts.”)
To make the formula work like regular English, be sure to put spaces where necessary. You cannot add a space immediately after a cell reference (like C2 above), but you can use double quotes. As you can see above, we used the space in quotes a few times to make the sentence read normally.
Apply the CONCATENATE formula elsewhere
Finally, the only real use of the CONCATENATE function is when working with enough data is saved rather than manually entering the data. So, all you have to do to make the formula work with other cells is drag it down.
Click a cell once so it is selected. At the bottom right corner of the cell, you should see a small rectangle, for example:
Click and hold this field while dragging it down to apply to the dataset. Stop dragging as soon as you get to the last item to which you want to apply the formula. You can always drag it from there again if you later need to add more cells.
Interestingly, Google Sheets has a similar feature called SPLIT. However, instead of concatenating cells, it splits one cell into multiple cells depending on which character you selected to mark as the split point.
–