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.
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…).
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).
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:
="The population of "&A1&" is "&B1. For joining text and numeric values, the ampersand operator follows the same principles as Concatenate.