CONCATINATE formula in Excel with examples

In Excel, the CONCATENATE function allows you to combine (join) two or more text strings into a single cell. However, in recent versions of Excel (2016 and later), you can also use the CONCAT function, which achieves the same result. Let’s explore both options:

1. Using CONCATENATE:

   • To concatenate the string in cell A1 with the string in cell B1, use either of the following formulas:

     =CONCATENATE(A1, B1)
     

     or

     =A1 & B1
     

   • This combines the contents of cells A1 and B1 without any additional characters.

2. Adding Delimiters:

   • If you want to include a delimiter (e.g., space, comma, dash) between the strings, you can modify the formula:

     =CONCATENATE(A1, " and ", B1)
     

     or

     =A1 & " and " & B1
     

   • This adds the phrase " and " between the strings.

3. Ignoring Empty Cells:

   • The TEXTJOIN function (available in Excel 2016 or later) is even more powerful. It can concatenate a range of strings while ignoring empty cells:

     =TEXTJOIN(", ", TRUE, A1:A10)
     

     • In this example, it joins the contents of cells A1 to A10 with a comma and space as the delimiter, ignoring any empty cells.

4. Formatting Numbers:

   • When using the CONCATENATE function, you can format numbers using the TEXT function. For instance:

     =CONCATENATE("Total: ", TEXT(A1, "0.00"))
     

     • This displays the value in cell A1 with two decimal places.

5. Inserting Line Breaks:

   • To insert a line break, use CHAR(10) and enable text wrapping in the cell:

     =A1 & CHAR(10) & B1
     

Remember, the choice between CONCATENATE and CONCAT depends on your Excel version and personal preference. Happy Excel-ing!