CONCATENATE Function in Excel

The CONCATENATE function allows you to combine multiple values into a single value. It can take up to 30 values of text, numbers or cell references to merge and return as a single output text string. However, in the latest version of Excel (2019 or newer), Excel introduced newer functions CONCAT or TEXTJOIN which offer more flexibility and are recommended.

Why should you use CONCATENATE?

The CONCATENATE function is useful to join multiple values into one. It lets you merge the data into a single cell where the output will be in text form.

• Functionality: To bring together data from multiple cells into one cell.
• Outcome: A combined text that displays all selected values in a single cell, making your data easier to manage and present.

Syntax of CONCATENATE Function

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

• text1 (mandatory): the first value to join or merge.
• text2, text3, … (optional): additional values to join or merge. You can add up to 30 values.

How to Use CONCATENATE in Excel?

The function joins the text, numbers or any other type of value in the order you pass the parameters. You can combine data from multiple cells, different worksheets or workbooks and even values that are result of any other formula within Excel.

=CONCATENATE(A2, " ", B2) 'Get full name from first and last name
=CONCATENATE(A2, " ", B2, ", ", C2) 'Merge cells that contains address
=CONCATENATE("Due date on ", TEXT(A2, "dd-mmm-yyyy")) 'Combining Text and Date

Key Features of CONCATENATE

• You can join texts, numbers, dates and cell references.
• If you are combining cells that contain dates or percentages, the output from Excel may not keep the formatting. For example, in cell A1 you have a date and with the use of the CONCATENATE function, the result might appear as a number.

=CONCATENATE("Completed on ", A2) 'Returns as Completed on 45657

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

=CONCATENATE("The date is ", TEXT(A1, "dd-mmm-yyyy"))

Step-by-Step Examples

Example 1: Get full name from first and last name

Assume column A contains the list of first names and column B contains the list of last names and you want to create full names in column C.

1. Click on cell C2.
2. Type this formula:
   =CONCATENATE(A2, ” “, B2)
3. Press Enter.

Result: If A2 contains “Rahul” and B2 contains “Sharma”, the formula in cell C2 will return Rahul Sharma.

Example 2: Merge cells that contains address

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

1. Select cell D2.
2. Enter the formula:
   =CONCATENATE(A2, ” “, B2, “, “, C2)
   We have separated with a space for the house number and street name and with a comma for the street name and city name.
3. Press Enter.

Result: If A2 contains “123”, B2 has “MG Road”, and C2 as a city name like “Mumbai”, the formula in cell D2 will return 123 MG Road, Mumbai.

Example 3: Combining Text and Date

If you have a list of dates in column B and you want to add text at starting of the date and also keep the date format, you can follow the below steps:

1. Select the cell where you want the result.
2. Enter the formula:
   =CONCATENATE(“Due date on “, TEXT(A2, “dd-mmm-yyyy”))
3. Press Enter.

Result: This will return something like the Due date on 31-Dec-2024 rather than the unwanted serial number instead of the date format.

What about Ranges?

The CONCATENATE function doesn’t work with ranges like example (A1:A5). If you want to join a range, Excel has introduced CONCAT and TEXTJOIN function that handle ranges along with delimiters.

Ampersand (&) as an alternative

If you want a simple and very short formula, you can use the ampersand symbol instead of the CONCATENATE function. For example:

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

Key Insights about CONCATENATE

• You can merge up to 30 individual cell references or text values
• When combining numbers with text, those are automatically converted to text format, which means you will lose any original format like dates or currency etc.
• For more heavy and complex tasks such as combining a range of cells or inserting similar delimiters between values, consider using CONCAT or TEXTJOIN functions which are available in newer Excel versions.

Conclusion

The CONCATENATE function is a perfect function helpful to perform simple text or number joining tasks which still remains as a handy tool for quick text merging!