The CONCATENATE function with example in Excel

The CONCATENATE function in Excel is used to join two or more text strings into one. While CONCATENATE is still supported in recent versions of Excel, it has been replaced by the CONCAT and TEXTJOIN functions for more advanced scenarios.

Syntax

excel
CONCATENATE(text1, [text2], ...)

• text1, text2, ...: The text items to be joined. You can use up to 255 text arguments.

Example

Suppose you want to combine the text "Hello" and "World" with a space in between.

1. Basic Usage:
   • Formula: =CONCATENATE("Hello", " ", "World")
   • Result: Hello World

Practical Example

Imagine you have a list of first and last names in columns A and B, and you want to combine them into full names in column C.

1. Enter the first name in cell A1: John
2. Enter the last name in cell B1: Doe

To combine these into a full name in cell C1:

• Formula: =CONCATENATE(A1, " ", B1)
• Result: John Doe

Step-by-Step Example

1. Input Data:

   • In cell A1, enter: Apple
   • In cell A2, enter: Banana
   • In cell A3, enter: Cherry
   • In cell B1, enter: Red
   • In cell B2, enter: Yellow
   • In cell B3, enter: Red

2. Use CONCATENATE:

   • In cell C1, enter the formula:

     excel
     =CONCATENATE(A1, " is ", B1)
     

   • Drag the fill handle from C1 to C3 to apply the formula to other cells.

3. Results:

   • C1: Apple is Red
   • C2: Banana is Yellow
   • C3: Cherry is Red

Example with a Range

If you have multiple cells to concatenate together, you can use CONCATENATE but note that it doesn't handle ranges directly. You would need to specify each cell individually.

1. Input Data:

   • In cell A1, enter: Hello
   • In cell A2, enter: Excel
   • In cell A3, enter: World

2. Use CONCATENATE:

   • In cell B1, enter the formula:

     excel
     =CONCATENATE(A1, " ", A2, " ", A3)
     

   • Result in B1: Hello Excel World

Using CONCATENATE with Numbers and Text

You can also use CONCATENATE to combine numbers with text.

1. Input Data:

   • In cell A1, enter: Product
   • In cell B1, enter: 123

2. Use CONCATENATE:

   • In cell C1, enter the formula:

     excel
     =CONCATENATE(A1, " ID: ", B1)
     

   • Result in C1: Product ID: 123

Notes

• CONCATENATE does not automatically include spaces between text strings. You need to manually add spaces within the formula as shown in the examples.
• For Excel 2016 and later, consider using the CONCAT function, which can handle ranges, or TEXTJOIN, which offers more flexibility with delimiters.

These examples demonstrate how the CONCATENATE function can be used to combine text strings in various ways in Excel.