How to Use CONCATENATE in Google Sheets

Combining data in Google Sheets can often simplify complex tasks, whether you’re working with text, numbers, or a mix of both. The CONCATENATE function allows you to easily combine cells in Google Sheets, making it an essential tool for creating unified datasets or custom strings of information.

1. CONCATENATE in Google Sheets

The Google Sheets CONCATENATE function is a powerful tool used to combine multiple strings of text from different cells into one. Whether you're merging names, addresses, or any other data, CONCATENATE simplifies the process of combining text for easy display or analysis. It's an essential function for improving data presentation and streamlining workflows in Google Sheets.

How It Works

The CONCATENATE function takes several text inputs or cell references and merges them into a single string. It helps when you need to join names, dates, or any other values in a spreadsheet into a continuous string without manually typing them out. This function is commonly used in data manipulation tasks and automating text-related processes in Google Sheets.

CONCAT Function in Google Sheets Syntax

The syntax for the CONCATENATE function is simple:

=CONCATENATE(text1, [text2, ])

• Text 1: The first piece of text or cell reference.
• Text 2: Additional text or cell references to be merged (optional).

2. CONCATENATE vs. CONCAT in Google Sheets

3. How to Concatenate in Google Sheets 

Learning how to use the CONCATENATE function is essential when you need to combine cells in Google Sheets. This powerful tool allows you to merge text or data from multiple cells into a single string, making your data cleaner and easier to manage. Follow these steps to combine cells quickly and effectively.

Step 1: Select the Cell for the Result

• Choose the cell where you want the combined text with spaces to appear.
• For example, select C2 if you want the result to be displayed in this cell.

Step 2: Identify the Text to Combine

Identify the cells containing the text strings you want to combine. For instance:

• A2 contains "Hello"
• B2 contains "World"

Step 3: Insert the CONCATENATE Formula

In the selected cell (e.g., C2), enter the following formula:

=CONCATENATE(A2, " ", B2)

• A2: The first text string (e.g., "Hello").
• " ": A space between the two text strings.
• B2: The second text string (e.g., "World").

Step 4: Press Enter

• Press Enter to apply the formula.
• The result will display "Hello World" in C2, combining the text from A2 and B2 with a space in between.

Step 5: Apply the Formula to Other Rows (Optional)

• If you have multiple rows to combine, hover over the bottom-right corner of C2 (the fill handle).
• Drag the fill handle down to copy the formula to other rows, so that each pair of text strings from columns A and B is combined with a space.

4. How To Combine Text And Numbers In Google Sheets

The CONCATENATE function in Google Sheets allows you to combine or join together text and numbers from different cells into a single cell, making it a powerful text merging function. This is especially useful for text and number concatenation, such as creating labels, generating custom reports, or organizing data in a more readable format. Follow the steps below to get started.

Step 1: Select the Cell for the Result

• Choose the destination cell where you want the combined result of text and numbers to appear.
• For example, if you're combining information from cells A2 (containing a number) and B2 (containing text), you would select C2 as the cell where you want the result.

Step 2: Identify the Number and String to Combine

Identify the cells containing the number and text that you want to combine.

• Column A contains the numbers (Product ID) for each item.
• Column B contains the text (product names) for each item.

Step 3: Write the CONCATENATE Formula

• Enter the CONCATENATE formula in the result cell (C2) to combine the number and text.
• The basic formula to combine the values from columns A and B with a separator (like a dash) between them would look like this:

=CONCATENATE(A2, " - ", B2)

Explanation:

• A2 refers to the product ID in column A (for example, 1 in cell A2).
• " - " is the separator (you can change this to a space, comma, or any other character as needed).
• B2 refers to the product name in column B (for example, Shirt in cell B2).

Result: The combination of these three elements will create a label like "1 - Shirt" in C2.

Step 4: Press Enter

After typing the formula in C2, press Enter. The cell will display the combined result, such as:

1 - Shirt

Now, you will see the product ID and name combined into a single label in C2.

Step 5: Apply the Formula to Other Rows

To apply the formula to the other rows, click on the fill handle (the small square at the bottom right corner of C2) and drag it down to fill the cells below. Google Sheets will automatically adjust the formula for each row. For example:

In C3, the formula will combine the product ID 2 and product name Pants, resulting in

2 - Pants

In C4, the formula will combine 3 and Shoes, resulting in:

3 - Shoes

This will continue for all rows.

Additional Notes:

Adjusting Separator: If you want to change the separator, simply edit the " - " part of the formula. For example:

For a comma: =CONCATENATE(A2, ", ", B2)

5. Alternatives to CONCATENATE in Google Sheets

If you’re looking for alternatives to the CONCATENATE function, there are simpler and more versatile options available in Google Sheets. One popular method is Using & operator in Google Sheets, which allows you to merge text quickly without a function. Another powerful tool is the TEXTJOIN function, which combines text from ranges with a delimiter and handles empty cells effortlessly. These alternatives make text merging tasks easier and more efficient, especially for complex datasets.

1. TEXTJOIN

Combines multiple strings with a specified delimiter.

Example:

=TEXTJOIN(" ", TRUE, A1, B1, C1)

2. AMPERSAND (&) OPERATOR

Joins text directly without a function.

Example:

=A1 & " " & B1

3. ARRAYFORMULA with JOIN

Useful for combining arrays of data.

Example:

=ARRAYFORMULA(A1:A5 & " " & B1:B5)

4. TEXT Function

Combines values with formatting options.

Example:

=TEXT(A1, "0.00") & " is the value"

Also Read:

• How to Add Bullet Points in Google Sheets
• How to Add Date Automatically in Google Sheets
• How to Automate Google Sheets with Python?