The CONCATENATE function in Google Sheets joinstogether multiple chunks of data. This function is helpful when managing largesets of information that each need a similar treatment.
For example, you might use the CONCATENATEfunction if the spreadsheet has one column for a first name and another for alast name, but you want them joined together to form a single cell with bothnames. You could this manually by typing each name, or you can use CONCATENATEto automate it.
Many other examples of the CONCATENATE function couldbe given, so we’ll look at a few below.
A Simple Example
At its simplest form, the CONCATENATE functionpieces together two sets of data without any other options. That’s possiblewith this simple form:
Of course, in this example, we’re assuming thefirst name is in cell A1 and the second in cell B1. You can adapt this to yourown spreadsheet by replacing those references with your own.
Pressing Enter with this particularexample would produce MaryTruman. As you can see, the first name isbutted right up against the last name. The CONCATENATE function did its job inthis scenario, but there are other options you can include in it to expand itscapabilities, like to add a space or data from other cells.
Using a Space in the CONCATENATE Formula
Knowing how to use spaces with CONCATENATE isimportant because datasets often aren’t set up exactly how you want them to be.Like in our example above, we want the name to look presentable by adding aspace between the two cells.
Spaces are included in this Google Sheetsfunction using double quotes:
If you can’t see here, there’s a space withinthose quotes. The idea behind using the quotes is that you’re entering datamanually and not choosing spreadsheet data.
In other words, A1 and B1 areclearly part of the spreadsheet already, so you’re referencing them by enteringthem as they are (the cell letter plus the cell number). However, to includeyour own data within the formula, you need to surround it in quotes.
Adding Text to a CONCATENATE Formula
The CONCATENATE function can do more than justjoin a couple cells and put a space between them. Below is an example of how touse CONCATENATE to form an entire sentence using cell data.
In this example of the CONCATENATE function, we’re stringing together the county and its rank number, but instead of leaving it at that, we’re using spaces and our own manually-entered data to create a full sentence:
To make the formula work like regular English,don’t forget to put spaces where necessary. You can’t add a space right after acell reference (like C2 above), but you can when using double quotes. Asyou can see above, we used a space multiple times in our quotes to make thesentence read normally.
Applying the CONCATENATE Formula Elsewhere
Lastly, the only real use of the CONCATENATEfunction is when dealing with enough data that time is being saved versusentering the data manually. So, all you need to do to make the formula workwith the other cells is drag it downward.
Click the cell once so that it’s highlighted.You should see a small box on the bottom right-hand corner of the cell, likethis:
Click and hold that box whiledragging it downward to apply it to the dataset. Stop dragging once you’vereached the last item you want the formula to be applied to. You can alwaysdrag it again from there should you need to include more cells later.
Interestingly,Google Sheets has a similar function called SPLIT. However, instead of joiningthe cells, it splits one cell into multiple cells depending on which characteryou choose to mark as the split-off point.