The `CONCATENATE` function in Excel is used to combine, or concatenate, text from multiple cells into one cell. While it has been replaced by the `CONCAT` and `TEXTJOIN` functions in recent versions of Excel (Excel 2016 and later), `CONCATENATE` is still available for backward compatibility. Here’s how to use it:
Basic Syntax
CONCATENATE(text1, [text2], ...)
- `text1`: This is the first string or cell reference to be joined.
- `text2, …`: These are additional strings or cell references to join. You can include up to 255 items, combining strings, cell references, or a mixture of both.
Examples
- Concatenating Text from Different Cells:
- Suppose you have “Hello” in cell A1 and “World” in cell B1, and you want to create the phrase “Hello World”.
- Formula: `=CONCATENATE(A1, ” “, B1)`
- Result: `Hello World`
- Concatenating with Static Text:
- If you want to add some static text, you can include that in quotes.
- Suppose you have a first name in A2 as “John” and a last name in B2 as “Doe”, and you want to display them in a sentence format like “Hello, John Doe!”.
- Formula: `=CONCATENATE(“Hello, “, A2, ” “, B2, “!”)`
- Result: `Hello, John Doe!`
Using the Ampersand (&) Operator
Excel also allows you to concatenate text using the ampersand `&` operator, which is often easier to use. Here’s how the above examples would look using `&`:
- Concatenating Text from Different Cells:
- Formula: `=A1 & ” ” & B1`
- Result: `Hello World`
- Concatenating with Static Text:
- Formula: `=”Hello, ” & A2 & ” ” & B2 & “!”`
- Result: `Hello, John Doe!`
Important Notes
- `CONCATENATE` does not automatically add spaces between text from different cells. You need to include spaces explicitly if needed, as shown in the examples.
- Consider using `CONCAT` or `TEXTJOIN` functions in modern Excel as they offer more flexibility, such as handling ranges and adding delimiters easily. `TEXTJOIN`, for example, allows you to specify a delimiter and decide whether to ignore empty cells.
By replacing `CONCATENATE` with newer functions, you make sure your formulae are compatible with the latest versions of Excel and take advantage of improved functionality.
Unlock Your Potential
Excel
Access
Power BI
