How To Concatenate In Excel

There are a lot of situations where you may need to combine two or more values together in Excel. It could be for joining names and addresses together, forming a sentence to better represent the information, or for any other reason.

The Concatenate function does just that in excel. You can combine any value in the order of the formula, allowing you to display the information in your Excel sheet in a more concise manner. Further, you can combine up to 30 text items using this formula.

Table of Contents

How to Concatenate in Excel?

To use the Concatenate function in Excel, you will first need to know the structure and syntax of the formula. The syntax is =Concatenate (value1, value2, value3…).

Also, you should remember a few things before using this function.

The = sign is there to let Excel know it is a formula.

The Concatenate string in the formula represents the function you’re trying to invoke.

The values inside the parenthesis are the values you want to join together.

When you concatenate a text string, you will have to include them within quotation marks.

When you concatenate a numeric value or cell reference, you won’t need to include them within quotation marks.

For joining different values, you will need to add commas after every value except the last one.

If you do not have specific spaces in text values, this function will join the values together without spaces.

The Concatenate function will always return a text value regardless of its input data type.

In a real-case scenario, the Concatenate formula may look like this: =CONCATENATE ("The population of ", A1, " is ", B1).

In the example, the “The population of “ is a text string. The two text strings above specifically have extra spaces placed in them to accommodate for the cell references.

If you need space between two reference cells, you will need to add a text string containing space. Here’s how it looks like:

=Concatenate (A1, “ ”, B1)

Alternatives to Concatenate in Excel

Using Concat Function

The Concat function is supposed to be the replacement for the Concatenate function in Excel. The Concat function has not entirely replaced Concatenate because the latter has been kept for backward compatibility. Older versions of Excel do not have the Concat function. Both functions perform the same functions, but Concat also has some extra features.

Microsoft recommends using Concat, as Concatenate may not be available in future versions of Excel.

Also, Concat can recognize and combine arrays, which the others can’t. For example, the =Concat (A1:A5) formula will combine all the values within that range. To achieve the same result in Concatenate, you will need to go through the long process of typing =Concatenate (A1, A2, A3, A4, A5).

Using Ampersand

You can use the ampersand (&) operator as a more simple alternative to using Concatenate. The ampersand (&) operator isn’t a function but works similarly to Concatenate. Its syntax is:

=“value”&“value”&value…

For example, ="The population of "&A1&" is "&B1. For joining text and numeric values, the ampersand operator follows the same principles as Concatenate.