CONCAT Function in Excel

The CONCAT function was introduced in Excel 2019 and is an upgrade to the CONCATENATE function that allows you to combine text, numbers or even both into a single output. It also allows you to work with ranges and is more flexible making a preferred and recommended choice in a newer version of Excel.

Why should you use CONCAT?

The CONCAT function is very useful when you merge a range of values into one. It simplifies combining data across rows, columns, and even ranges, which results in a clean and clear output.

• Functionality: To merge the data from different cells, ranges, or values.
• Outcome: A combined text that makes your data more organized and easier to work with.

Syntax of CONCAT Function

=CONCAT(text1, [text2], [text3], …)

• text1 (mandatory): the first value to join or merge.
• text2, text3, … (optional): additional values to join or merge and you can use 255 arguments.

How to Use CONCAT in Excel?

The function is straightforward and joins texts, numbers or any other type of value in the order you pass. It is handy for combining data from a range without a requirement to specify each cell individually.

=CONCAT(A1, " ", B1) 'Get first name and last names in cell A1 and B1
=CONCAT(C2, ", ", D2) 'Get full name from first and last name
=CONCAT(A2, ", ", B2, ", ", C2) 'Combine cells that contain the address
=CONCAT("Your next appointment is on ", TEXT(B2, "dd-mmm-yyyy")) 'Combining Text and Date by retaining the format
=CONCAT(A1:A5) 'Combine rows from cell A1 to A5
=CONCAT(A1:E1) 'Combine columns from cells from A1 to E1
=CONCAT(A1:C10) 'Combine both rows and columns from A1 to C10

Key Features of CONCAT

• You can combine any type such as text, numbers, and cell reference and also merge an entire range of cells.
• If any cell contains a date or any other number formatted text, they might lose their format even in the CONCAT function as it removed the format in the CONCATENATE function.

=CONCAT("The meeting is on ", A2) 'Returns as Completed on 45657

To keep the format, use the TEXT function along with CONCAT like this:

=CONCAT("The meeting is on ", TEXT(A1, "dd-mmm-yyyy"))

Step-by-Step Examples

Example 1: Get full name from first and last name

Assume column C contains the list of last names and column D contains the list of first names and you want to create full names in the format as last name, first name in column E.

1. Click on cell E2.
2. Type this formula:
   =CONCAT(C2, “, “, D2)
3. Press Enter

Result: If C2 contains “Verma” and D2 contains “Anjali”, the formula in cell E2 will return Verma, Anjali.

Example 2: Combine cells that contain the address

You want a full address separated by a comma where column A contains the house number, column B contains the name of the street and column C has city names.

1. Select cell D2.
2. Enter the formula:
   =CONCAT(A2, “, “, B2, “, “, C2) //We have added a comma as a delimiter between each cell followed by a space
3. Press Enter.

Result: If A2 contains “Street No. 50”, B2 has “MG Road”, and C2 has a city name like “Delhi”, the formula in cell D2 will return Street No. 50, MG Road, Delhi.

Example 3: Combining Text and Date by retaining the format

When merging the cells that contain any date format, the CONCAT function will return data without the format. To avoid this, use the TEXT function along with CONCAT:

1. Select the cell where you want the output to be displayed, for example, D2.
2. Enter the formula:
   =CONCAT(“Your next appointment is on “, TEXT(B2, “dd-mmm-yyyy”))
3. Press Enter.

Result: If B2 has the date 01-Dec-2024, the output will be “Your next appointment is on 01-Dec-2024” instead of any unwanted serial number.

Special Notes: Using CONCAT with Ranges

The main feature of the CONCAT function compared to CONCATENATE is it allows you to combine rows or columns without manually selecting each cell. For example:

• Combine rows from cell A1 to A5
  =CONCAT(A1:A5)
• Combine columns from cells from A1 to E1
  =CONCAT(A1:E1)
• Combine both rows and columns from A1 to C10
  =CONCAT(A1:C10)

Ampersand (&) as an alternative

As with CONCATENATE, you can still use the ampersand (&) to combine the text in Excel. The shorter and more flexible way as an alternative. For example:

• A1 & ” ” & B1
• A1 & “, ” & B1 & “, ” & C1

Key Insights about the CONCAT

• Range Support: CONCAT supports range compared to the CONCATENATE function which makes it easier to merge larger datasets.
• Text Formatting: Numbers or dates will lose the formatting when passing them as a parameter in the function. Use the TEXT function to retain the required format.
• Alternative Functions: CONCAT is an excellent upgrade, however, it cannot add the delimiter such as commas or spaces or even ignore blank cells. If you need more control over formatting, consider using the TEXTJOIN function.

Conclusion

The CONCAT function is an upgrade over CONCATENATE, which provides more flexibility mainly when dealing with ranges either just in columns, rows or even both. It is an essential tool for combining text, numbers, or both in Excel, making your data look cleaner and more organized.