Concatenation. What on earth does that mean? Put simply, this is putting different pieces together to create a whole. We can have values in different cells and use the CONCATENATE function to join them together to create one combined piece. Here I’ll go through some examples of how to use this function, building up from the very basics to more sophisticated ways to use it, combing it with other functions. So, let’s dive straight in.
Example 1 – Joining two names together
In the first example, we have some names which from our system have been stored in separate cells. One column for their first name and one for their surname. What we want to do is combine the two to create their full name and store it in column C.
In cell C2, we write a CONCATENATE function to join the contents of column A and B.
As we write the function, we can see that the function needs some strings (text, numbers, etc) to put together.
In the brackets we refer to cell A2 (Fred), add a comma, then refer to cell B2 (Flintstone).
As you can see it’s added the two names together, the only problem is, is that there’s no space between the two.
To remedy this, we need to add a space in between the cells A2 and B2 in the formula, like this:
Now we have the words with a space in the middle.
Once we’ve written the formula for one cell, we can drag it down to the rows below to fill the table.
Example 2 – Making website URLS from information in different cells
We can use the same principle to create individual URLs. Here we have a table where we have the main website address and the various pages on it. On the right we’re going to join them together.
This time instead of add the individual cell references, we can write a range in between the brackets:
This create the specific page links which we can now share and use.
Example 3 – Combining a range of cell values to make a complete part number
We can continue the idea of using a range of values to create, for example, here a part number. The part number is made up of a location, parent type, subset and the part and are combined to create an individual part number.
If we just want to combine all the numbers together, we can just refer to the range where the values are:
As the number is long and a little difficult to read, we may want to add some dashes to identify the individual parts of the number. Like the spaces we added above, we just add the dashes in between the cell references:
Example 4 – Concatenating formulas
Here we want to show on a website the quantity of each product we have, and also the total of all the things we have. In cell C12, we write the following formula:
First, we are summing the contents of column A (the quantities), we add a space, then add the word “Things”.
Example 5 – Making sentences and adding dates in the current format
Here we’d like to show today’s date as part of a complete sentence.
To do this, we first write the text we want (the fixed part of the sentence), in this example, “Today’s date is ” (notice the space at the end). Then we need to get the date which is in cell E7. If we just write E7, then the date won’t appear in a date format, rather it will appear as a number, like this:
So, we need to adjust the formula, so that it gets the text in E7 and puts it into a date format:
Example 6 – Creating addresses on different lines
Here we want to create some addresses but we want each cell to appear on its own line, ready to be printed. These are UK style addresses and we have the street number and name, the town, county and then the post code.
To add a line break after each line of the address, we need to use the character number 10-char (10). We add a cell reference, then add the char(10) after each one.
This puts the full addresses in column O, with each part of the address on a different line.
CONCAT v CONCATENATE
If you’ve typed out a CONCATENATE function, you may have noticed that there’s another one called CONCAT. This works in a similar way…so what’s the difference? Basically, in Sheets CONCAT is only used to combine two values, whereas there is no limit with CONCATENATE (within reason). Note, in Excel 2016, CONCAT now replaces CONCATENATE but CONCATENATE is still backwards compatible with earlier versions of Excel.
We can also use the AND operator instead of the CONCATENATE function. In the first example, we had the first name in column A and the last name in column B.
We write the cell reference, then the ampersand symbol(&), add a space (” “), then another ampersand, then the second cell reference:
Exactly as before it joins the two names together with a space in the middle.
The limitation is that you can’t use ranges, like in examples 2 & 3 above, with the ampersand (&). Plus in longer formulas, you have to continually add the ampersand, whereas with the CONCATENATE function you only write it once. Finally, the ampersand is used in other ways and so using it, sometimes can make longer formulas more difficult to read and understand.
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” – iBooks store / Kindle store
- “Beginner’s Guide to Google Sheets” – iBooks store / Kindle store
- “Beginner’s Guide to Google Docs” – iBooks Store / Kindle store
- “Beginner’s Guide to Google Slides” – iBooks Store / Kindle store
- “Google Sheet Functions – A step-by-step guide” – iBooks Store / Kindle Store