How to Use the CONCAT Function in Excel
Today, we’re going to delve into a useful function available in both Ms Excel and Google Sheets named CONCAT. The CONCAT function is designed to merge multiple strings into one continuous text string. This is particularly valuable for amalgamating text from various cells or incorporating specific text into existing cell values.
Basic Syntax
The standard formula structure for the CONCAT function is as follows:
=CONCAT(text1, [text2], ...)text1: This is the initial string or cell reference you wish to concatenate.text2(optional): These are additional strings or cell references you may include in the concatenation.- The function can accept up to 253 text arguments with each argument allowing a maximum of 32,767 characters.
Examples
Example 1: Basic Usage
Consider two cells where cell A1 contains “Apple” and cell B1 contains “Banana”. To merge these two into one cell using CONCAT:
| A | B | C |
|---|---|---|
| Apple | Banana | =CONCAT(A1, B1) |
The formula =CONCAT(A1, B1) in cell C1 will produce “AppleBanana”.
Example 2: Adding Spaces
To insert a space between “Apple” and “Banana”, modify the formula as follows:
| A | B | C |
|---|---|---|
| Apple | Banana | =CONCAT(A1, ” “, B1) |
Now, the formula =CONCAT(A1, " ", B1) in cell C1 will yield “Apple Banana”.
Example 3: Combining Multiple Cells
If you need to merge content from more than two cells, simply add additional arguments to the CONCAT function:
| A | B | C | D | E |
|---|---|---|---|---|
| Hello | from | Excel | and | Sheets |
To combine these cells into cell F1, utilize the following formula:
=CONCAT(A1, " ", B1, " ", C1, " ", D1, " ", E1)The resulting text in cell F1 will be “Hello from Excel and Sheets”.
The CONCAT function in Excel and Google Sheets proves to be a versatile tool for merging text from different cells or interspersing specific text between values. Remember, you can enhance the function’s utility by incorporating separators or constants as necessary.
More information: https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2