The CONCATENATE Function

• Introduction
• Method
• Advanced Cell Referencing with INDIRECT

How to add or combine text or cells in Google Sheets.

Introduction

Combining text (or ‘strings’ in programming parlance) is more useful than you might initially imagine. It can typically be used to combine names (e.g. Given name & Family name) into a display name or salutation. It can also be used to help identify duplicates entries within a list of rows (e.g. duplicate customers, records or products) by create a unique single value for each row.

You can do this by using the concatenate function, or it’s more basic concat form.

Method

Inputs & Outputs

Like many other functions, concatenate can take either a string or a range as an argument. An argument is the word we use (commonly also referred to as a parameter) to describe the data that we provide to a function, in other words, the input. What we get back (the output) will be displayed in the cell. In the short example below, we could use the formula to add two numbers together (our inputs) to get the answer (our output).

2+2

This formula could be rewritten using the add function, with our numbers being provided as arguments.

ADD(2, 2)

We can also provide the address of a cell containing our values, rather than the explicit values themselves. Imagine we had these numbers in cells A1 and A2. Then our formula would read as follows.

ADD(A1, A2)

Sheets understands that A1 is the address of a cell (a reference to the value within that cell) because it is not a number. References can either be to an individual cell (e.g. A1) or to a range of cells (e.g. A1:A2). A range is an inclusive list of cells, in that it will include the first cell specified, the last cell specified, and all the cells in between. The range A1:A3 would, therefore, include A1, A2 and A3. A1:B3 would include A1, A2, A3, B1, B2 and B3.

Most formulas (e.g. sum and concatenate) allow you to use cells ranges as inputs (e.g. add all the numbers in this range of cells) but some, simple versions, of formulas (e.g. add and concat) will only allow you to use references to individual cells. Functions with a fixed number of arguments (both add and concat will only accept two inputs) tend to only allow single cell references, whereas those that accept n arguments (sum and concatenate will take as many inputs as you provide) will normally accept ranges. If in doubt, try it!

Using the formula

Concatenate combines the arguments you supply, even if they are numbers. The formula below will output the string John Doe, but the one below that will output 22 (remember, combine rather than add). All the inputs will be treated as text, regardless or whether they are numbers or not.

CONCATENATE("John", " ", "Doe")

CONCATENATE(2, 2)

If you have a sheet with some data on people, laid out below, then you can use this to generate display names (e.g. Mr John Doe) in a single cell, which is useful for mailings & merges. To do this, we need to concatenate the constituent name parts with spaces (“ “).

CONCATENATE(A2, " ", B2, " ", C2)

If you are importing data into sheets, you can also do the opposite (‘decomposing’ or splitting a display name into given name, family name etc) by using the split function.

Shorthand Version

In the same way that ‘+’ is a shorthand operator for the add function (or perhaps the function is a longhand version of the operator), you can also use ‘&’ as a shorthand operator for concat. The example below will give the same output as the previous formula but in a more compact form. This is particularly useful when embedding in a longer formula to avoid too many confusing nested brackets.

A2&" "&B2&" "&C2

Mixing Strings, Cell References and Functions

Remember that you can ‘mix and match’ between text and references (as we did above with spacing characters), so you can use the function to append or prepend a specific string to your cell values. This is helpful if you can to include a conditional or optionally append an initial to a display name. Consider the following sheet of data, now with an optional middle name.

We can include the first letter of the middle name (using the left function) into our concatenation as follows.

CONCATENATE(A2, " ", B2, " ", LEFT(D2, 1), " " C2)

This will work perfectly when the middle name is present, giving us Mr John P Doe for our first record. However, when we use the same formula on our second record, we get an awkward visual double space because no middle name is present, e.g. Ms Jane  Doe. To avoid this, we can insert a conditional if to test the length of the middle name cell, and only output the initial (and space) if the length is greater than zero.D2

CONCATENATE(A2, " ", B2, " ", IF(LEN(D2)>0, CONCAT(LEFT(D2, 1), " "), ""), C2)

Advanced Cell Referencing with INDIRECT

Concatenation can also be used with the indirect function to produce cell references. Whilst this is not common, it can be very helpful if you are programmatically populating cell references and are therefore unable to take advantage of autofill referencing (when references automatically increment when you ‘drag fill’).

INDIRECT(CONCAT("A", ROW()))

The formula above concatenates a known column reference (e.g. ‘A’) with the current row number (e.g. 1) to produce a cell reference string (‘A1’). This is then used by indirect to give you the value of that cell.