How to use the CONCATENATE Function in Google Sheets

Google Sheets is a powerful tool for managing and analysing data, and one of its most useful features is the CONCATENATE function. This function allows you to combine two or more text strings, making it a valuable tool for creating labels, combining data from multiple cells, and more. This article will explore how the CONCATENATE function works in Google Sheets and provide some example use cases.

Syntax of the CONCATENATE function

The CONCATENATE function is a built-in function in Google Sheets that joins two or more text strings together. Its syntax is relatively simple:

=CONCATENATE(string1, [string2, …])

The function takes one or more text values as arguments and returns a single string of text resulting from joining them together. You can use up to 30 arguments with this function, and each argument can be a cell reference or a string of text enclosed in quotation marks.

Example use cases of the CONCATENATE function

Combining first and last names

Suppose you have a list of employees in a spreadsheet with separate columns for first and last names. You can use the CONCATENATE function to combine these two columns into a single column for full names. Here's an example:

=CONCATENATE(A2, " ", B2)

In this formula, A2 and B2 are the cell references for the first and last name columns, respectively. The space enclosed in quotation marks separates the first and last names.

Creating custom labels

If you need to create custom labels for a set of products, you can use the CONCATENATE function to combine the product name, size, and price into a single label. Here's an example:

=CONCATENATE(A2, " - ", B2, " oz - £", C2)

In this formula, A2, B2, and C2 are cell references for the product name, size, and price columns. The hyphen and pound (£) sign enclosed in quotation marks separates the different pieces of information.

Building URLs

You can also use the CONCATENATE function to build URLs that link to specific web pages. For example, if you have a list of product IDs or blog posts and want to link to their respective pages, you can use the CONCATENATE function to build the URLs. Here's an example:

=CONCATENATE("https://www.spreadsheetwise.com/", A2)

In this formula,"https://www.spreadsheetwise.com/" is the base URL, and A2 is the cell reference for the blog page. The CONCATENATE function joins the two pieces of information to create the full URL.

Creating a Sentence

The selected link from the image above is from a blog post about creating a ChatGPT Custom Function; the CONCATENATE function is used in conjunction with the custom ChatGPT function to join multiple cells together to form a sentence.

Here is the function:

=GPT(CONCATENATE("Write a ",A2, " post about ",B2," in a ",E2," tone of voice"),D2,1)

Let’s break this down to understand what’s happening here. It’s best to start with the CONCATENATE part first.

CONCATENATE("Write a ",A2, " post about ",B2," in a ",E2," tone of voice")

You can CONCATENATE text in double quotes and use cell references. Each string, whether it’s text in a double quote or a cell reference, can be joined as separate strings; these are individual strings that form one joined or concatenated string. Each string is separated by a comma.

The CONCATENATE collects everything written in double quotes and cell references to form a complete text string.

You’ll notice there are intentional spaces before and after the double quotes, such as "Write a " - after the letter ‘a’ is a space before the double quotes, and the same for " post about ", which has a space before ‘post’ and after ‘about’. This is necessary since cell A2 contains the word ‘Twitter’; without this space, the CONCATENATE would create the following string of text:

Write aTwitterpost about…

Adding these spaces here ensures the string is constructed correctly:

Write a Twitter post about…

The complete string created by the CONCATENATE function is:

Write a Twitter post about Health and fitness tips in an Inspiring tone of voice.

Once the CONCATENATE function has constructed this sentence, it is passed as the prompt argument of the GPT Custom Function, which wraps around this CONCATENATE function.

It is the equivalent of writing this sentence for the prompt of the GPT Function, followed by the D2 for the maxTokens argument and 1 for the temperature argument.

=GPT("Write a Twitter post about Health and fitness tips in an Inspiring tone of voice",D2,1)

Of course, it’s much better to make these scenarios as dynamic as possible; hence, the CONCATENATE function comes to the rescue.

=GPT(CONCATENATE("Write a ",A2, " post about ",B2," in a ",E2," tone of voice"),D2,1)

Other ways to join text

It's possible to join text without the CONCATENATE function by using the '&' sign, like the example below.

The syntax is straightforward as you only really need the ampersand (&) sign to join cells or strings of text together, along with some empty spaces to separate them, just like before.

=A2 & " " & B2

Take a look at this post on comparing percentage change, which uses the ampersand (&) sign to join everything together to display an arrow (▲ or ▼) based on whether the value increases or decreases.

Conclusion

In conclusion, the CONCATENATE function in Google Sheets is a powerful tool for combining text strings. This function allows you to streamline your workflow, create custom labels, build URLs, and create custom sentences quickly and easily. Try using the CONCATENATE function in your own spreadsheets to see how it can help you save time and be more productive.

Here is the example CONCATENATE Function spreadsheet used in this example, but if you want to learn more about the ChatGPT Custom Function, check out this post.