In this tutorial, I will teach you how to use CONCATENATE in Google Sheets.
How to use CONCATENATE in Google Sheets
Use the CONCATENATE in Google Sheets to:
- Appending a text to another text
- Appending a text to a cell or a range
- Appending a text to an array
- Appending a cell or range to another cell or range
By using =CONCATENATE([String 1],[String 2])
Combine Texts or Cells Using CONCATENATE in Google Sheets Video Tutorial (incl. Examples)
The CONCATENATE function is a text function that is widely used for text manipulation.
Specifically, it appends the values of a chosen range in a left-to-right, top-to-bottom sequence.
It can also be used to append specific texts like “https://” to the front of another text, or “/page” to the back of a text.
While it is very versatile, one of the more common uses I had for the CONCATENATE function was to generate a unique ID for a row of data.
For example, let’s assume that Column A contains “ticket ID”s while Column B contains “response ID”s. Response IDs in this case goes from 1 upwards for each unique “ticket ID”.
In a setting like this, there will be duplicate ticket and response IDs. This will prove problematic in the long run as there is no practical way to identify which response to drill down on.
Using the CONCATENATE function will allow you to generate a new ID that’s unique and easy to locate using the search feature in Google Sheets.
Or, if you feel that the generated Unique IDs are too long, you might want to consider concatenating only a substring of a cell.
For this tutorial, I will provide a sample file that will have 1 sheet.
This sheet will contain an array of texts and numbers that we will reorganize using the CONCATENATE formula.
You can make a copy of the file by accessing it through this link, then by clicking on “File” from the Menu Bar, then by clicking on “Make a copy”.
A dialog box should appear. Within it, you can provide a filename for the Copy then click “Make a Copy”
Finally, try to remember that the CONCATENATE formula processes the cells in a range from left-to-right, then top-to-bottom.
Method 1 – Appending a text to another text in Google Sheets
This method illustrates how you can append a text to another text using the CONCATENATE function.
To append “I” to “think”, modify the following formula by replacing [String 1], and [String 2] with “I” and “think”, respectively.
=CONCATENATE([String 1],[String 2])
You may notice that the above example results in a text without the logical space between the words.
To remedy this you can add a space after “I” within the formula so that the result should look like the following:
Next, we will append 3 more texts, so that the CONCATENATE function will result in a text that will read “I think therefore I am”.
Going with our previous example, to append the texts “I ”, “think ”, “therefore ”, “I ”, “am”, substitute the texts to their corresponding placeholders in this formula:
=CONCATENATE([String 1],[String 2],[String 3],[String 4],[String 5])
The formula and result should look like this:
Method 2 – Appending a text to a cell or a range in Google Sheets
For this method, please refer to the “Array of text” sheet on your copy of the sample file I provided. The samples can be found under the Method 2 section (A1:B4).
The first use of this method that we will explore is appending a text to a cell.
To append the text “yahoo” to the cell in A2, replace [String 1] with “yahoo”, and replace [String 2] with A2. Enter this modified formula to cell D2.
=CONCATENATE([String 1],[String 2])
The formula and result should look like this:
Next, we will append the text “https://www.” to the ranges A3:B3 and A4:B4 respectively.
=CONCATENATE([String 1],[String 2])
To do this, replace [String 1] with “https://www.”, then replace [String 2] with A3:B3. Enter this modified formula to cell D3.
The formula and result should look like this:
Lastly, replicate the previous formula on cell D4, but replace [String 2] with A3:B3, use A4:B4 instead.
The formula and result should look like this:
Method 3 – Appending a text to an array in Google Sheets
For this method, please refer to the “Array of text” sheet on your copy of the sample file I provided. The samples can be found under the Method 3 section (A6:D8).
We will append the text “This ” with an array based on the samples in range A7:D8.
To do this, replace [String 1] with “This”, then replace [String 2] with the array “{D7:D8,B7:B8;C7:C8,A7:A8}”.
=CONCATENATE([String 1],[String 2])
The formula and result should look like this:
The array provided restructures the data from A7:D8 so the CONCATENATE function will process it in the correct order (left-to-right, then top-to-bottom).
You can check the following tables to see how the array restructures the range:
Method 4 – Appending a cell or range to another cell or range in Google Sheets
For this method in Google Sheets, please refer to the “Array of text” sheet on your copy of the sample file I provided. The samples can be found under the Method 3 section (A10:D18).
The first use of this method that we will explore is appending a cell to another cell.
We will append the text in cell A11 with the text in cell A12, before finally appending the text in cell B11. This should result in “https://www.facebook.com”.
To do this, replace [String 1] with A11, replace [String 2] with A12, then replace [String 3] with B11.
=CONCATENATE([String 1],[String 2],[String 3])
The formula and result should look like this:
One more use of this method is appending a cell to a range.
We will append the texts in range A15:A18 to the texts in range B15:D15, before finally appending the texts in range B16:C16.
This should result in “This could be the start of a beautiful friendship”
To do this, replace [String 1] with A15:A18, replace [String 2] with B15:D15, then replace [String 3] with B16:C16.
=CONCATENATE([String 1],[String 2],[String 3])
The formula and result should look like this:
You now know How to use CONCATENATE in Google Sheets!
Frequently Asked Questions on How to Use the CONCATENATE function in Google Sheets
Like in most programming languages, is there a syntactic alternative to the CONCATENATE function in Google Sheets?
You can use the Ampersand or “&” operation in a formula to replicate what the CONCATENATE function does. Appending A1 with B1 in through the CONCATENATE function would result in this formula: (“=CONCATENATE(A1,B2)”) while the Ampersand operation would result in this formula: (“=A1&B1”).
In Google Sheets, if I append ranges that currently have blank cells, will the CONCATENATE function still work when I finally fill those blank cells?
The result of any formula, including CONCATENATE, will remain dynamically dependent with the cells it refers to. This means that updating the values on the reference, even those that were initially blank, will affect the outcome once they are updated with values.
Conclusion on How to use CONCATENATE in Google Sheets
There are several applications of the CONCATENATE function in Google Sheets.
The first method involves appending a text to another, or more, texts. To append the texts “The good, ”, “the bad, ”, and “and the ugly” with each other, substitute the texts to [String 1], [String 2], and [String 3], respectively:
“=CONCATENATE([String 1],[String 2],[String 3])”
The second method involves appending a text to one or more cells and/or one or more ranges. To append “https://” to the cell A1, then the range B1:D1, substitute the text to [String 1], then substitute the cell A1 to [String 2], then the range B1:D1 to [String 3]:
“=CONCATENATE(“https://”,A1,B1:D1)”
The third method involves appending a text to one or more arrays. To append “https://” to the array {A1:C4}, substitute the text to [String 1], then substitute the array to [String 2]:
“=CONCATENATE(“https://”,{A1:C4})”
The fourth method involves appending a cell or range to one or more cells or ranges. To append the range A2:C2 to the cell D2, then the range E2:G2, substitute the range A2:C2 to [String 1], then substitute the cell D2 to [String 2], then substitute the range E2:G2 to [String 3]:
“=CONCATENATE(A2:C2,D2,E2:G2)”
There are other methods in using the CONCATENATE function, but the 4 methods discussed should give you a foundation on How to use the CONCATENATE function in Google Sheets.
