CONCATENATEThe CONCATENATE function in Google Sheets is used to combine text strings from different cells or inputted directly in the formula. It takes one or more text strings as input and returns a single concatenated string. This function is commonly used to combine first and last names, or to merge data from multiple cells into a single cell.
- How to use
- Examples of using
CONCATENATEformula not working?
- Similar formulas to
CONCATENATE formula with the syntax shown below, it has 1 required parameter and 1 optional parameter:
- string1 (required):
The first string to concatenate. It can be a string, cell reference or range of cells.
- string2 (optional):
Additional strings to concatenate. You can add up to 30 strings, each separated by a comma.
ExamplesHere are a few example use cases that explain how to use the
CONCATENATE formula in Google Sheets.
Combine first and last names
You can use the CONCATENATE function to combine first and last names from separate cells into a single cell. For example, if you have a first name in cell A2 and a last name in cell B2, you can use the formula =CONCATENATE(A2, " ", B2) to combine them into a single cell.
Create a custom message
You can use the CONCATENATE function to create a custom message for your spreadsheet. For example, if you have a message in cell A1 and a name in cell B1, you can use the formula =CONCATENATE(A1, ", ", B1, "!") to create a message that includes the name.
Combine multiple cells into one
You can use the CONCATENATE function to combine the contents of multiple cells into a single cell. For example, if you have data in cells A1, B1 and C1, you can use the formula =CONCATENATE(A1, " ", B1, " ", C1) to combine them into a single cell.
CONCATENATE not working? Here are some common mistakes people make when using the
CONCATENATE Google Sheets Formula:
Forgetting to separate strings with commas
When using the
CONCATENATE formula, it is important to separate each string with a comma. If you forget to include a comma between strings, the formula will return an error.
Not including enough strings
CONCATENATE formula requires at least one string argument. If you don't include at least one string, the formula will return an error.
Mismatched data types
If you try to combine strings with numerical values, or if you try to combine text with dates or times, the
CONCATENATE formula may return unexpected results. Make sure that all of the data types are consistent before using the formula.
The following functions are similar to
CONCATENATE or are often used with it in a formula:
TEXTJOINformula concatenates (joins together) multiple text strings using a delimiter of your choice. This can be useful when combining lists, creating sentences, or formatting data for export. You can choose to ignore empty cells or include them as part of the output.
LEFTformula is used to extract a specific number of characters from the beginning of a text string. It is most commonly used to extract the first name or last name from a full name or to extract a date from a text string.
RIGHTformula in Google Sheets returns the rightmost characters from a string, based on the number of characters specified. It is commonly used to extract a certain number of characters from the end of a string. The formula takes two arguments: the string from which to extract the characters, and the number of characters to extract.
You can learn more about the
CONCATENATE Google Sheets function on Google Support.