How To Use CONCATENATE in Google Sheets

CONCATENATE Formula in Google Sheets

CONCATENATE means “to link (things) together in a chain or series“.

As a marketer it means, help me be lazy by writing all my meta descriptions for me.

Whilst it’s not quite that good, it does let you easily combine values in Google Sheets from multiple cells.

This seriously speeds things like adding tracking to URLs, managing ad campaigns and writing meta descriptions in bulk.

What is the CONCATENATE Formula?

The CONCATENATE function helps you to link data from multiple cells together.

Concatenation means the combination of the contents of two or more ​cells in a worksheet into a third, separate cell.

The most typical use case is joining the first names and last names, but it allows you to quickly combine any values in cells.

The syntax for the CONCATENATE function is:

=CONCATENATE(string1, [string2, ...])

string1 is the initial string, and string2, string3, … are additional strings that you want to append in sequence.

You can use text or numbers, blank spaces enclosed in quotation marks or cell references to the location of data in the sheet.

When to use CONCATENATE for marketing stuff?

• Creating ad copy in bulk
• Bulk writing title tags and meta descriptions.
• Doing keyword research in bulk (with modifiers)

How To Use The CONCATENATE Formula in Google Sheets

You can use the CONCATENATE function with two variables, which can be cell references to the two contents that you want to join together.

The function in the first line would look like this:

=CONCATENATE(A3, B3)

As you can see, the function doesn’t leave a blank space between the words or numbers you concatenate.

However, you’re more likely to need a space character between the joined contents.

You can do it with the CONCATENATE function by adding a third argument that contains a space character.

For example:

• Let’s say you have one column for a first name and another for the last name,
• You want to join these two columns together in a single cell with both names.
• In this case, you don’t just need to concatenate the cell contents, but you also need to add a space between the two names.
• To do this, you simply add this space (” “) to your formula between the cell references of the names:

=CONCATENATE(A3, “ “, B3)

CONCATENATE Formula Examples

Suppose you have a similar dataset, but instead of names, it has cities and countries that you want in the following format separated by a comma and space: city, country.

Instead of putting a space character between the two cell references, you need to put a comma and space enclosed in double quotes: “, “.

Here is the whole formula:

=CONCATENATE(B2, ", ", A2)

In this example, we added an extra comma to the data.

But you can add any text or punctuation marks. You can even create whole sentences to make your data more visible and easier to understand.

For example:

Say you have a sheet with details about people, such as their name, age, experience level, expertise, and so on.

You just need to add these details one by one to the CONCATENATE function together with the additional words and punctuations you need.

The following function creates a whole sentence with the details of the sheet:

=CONCATENATE(A2," (",B2,") has ",C2," year(s) of experience in ",D2)

You can also concatenate numbers, such as: =CONCATENATE(3, 5)

Being an absolute maths pro, I can tell you that will result in 34 35. But there is an issue with this…

Google Sheets always formats the results of the CONCATENATE function as text.

If your entries are text, this is totally fine.

But if you concatenate numbers, you can’t include the result in math functions like SUM and AVERAGE because they will be treated as text and math functions ignore the text.

Summary

CONCATENATE is really useful for speeding up writing meta descriptions and page titles in bulk. Especially when a similar format will suffice.

Check out the Google Sheets Templates in the directory for inspiration.