Excel’s CONCATENATE Function: Connecting Strings of Data

The Concatenate function is a critically important tool that is used to connect strings of data in a spreadsheet.

The word ‘concatenate’ means “to join or link things together” and this function is used when creating text sentences, such as footnotes, in a spreadsheet.

For example, let’s say you add a footnote to a summary table that says, “A tax rate of 35% was used to calculate the summary values.”

You don’t want to type the 35% directly into the sentence because if the tax rate used in the analysis ever changes, you may forget to manually change the footnote.

The syntax for the Concatenate function is as follows:

= CONCATENATE(“text1”,A1,”text2″…)

You can join up to 30 things together in one concatenate function

Text entries should be surrounded by quotation marks, while cell references or calculations should be surrounded by commas

The one issue with the concatenate function is that it does not extract any formatting from cells being referenced

In the previous example, if cell A1 contains the tax rate of 35%, the concatenate function will look like:

=CONCATENATE(“A tax rate of “,A1,” was used to calculate the summary values.”)

However, when entered into the cell, the output will look like:

A tax rate of .35 was used to calculate the summary values.

The concatenate function will not have applied the percent formatting to the tax rate.

There are two ways to re-apply the formatting when using the concatenate function:

The Manual Way to re-apply the formatting is as follows:

=CONCATENATE(“A tax rate of “,A1*100,”% was used to calculate the summary values.”)

Excel’s TEXT Function is a more flexible way to re-apply the formatting and is used as follows:

=CONCATENATE(“A tax rate of “,TEXT(A1,”0.0%”),” was used to calculate the summary values.” )

The ampersand (“&”) can be used instead of the concatenate function.

The ampersand performs exactly the same functionality as the concatenate function.

The syntax for using the ampersand in the above example is as follows:

=”A tax rate of “&A1*100&”% was used to calculate the summary values.”

To properly apply the syntax, text entries should still be surrounded by quotation marks, however, cell references or calculations should be surrounded by ampersands, not commas.