CONCATENATE Function or Ampersand Operator in Excel

The CONCATENATE Function joins several text strings into one text string. You can also use the Ampersand (&) calculation operator instead of the CONCATENATE Function.

The CONCATENATE Function has one required argument and up to 255 arguments, all separated by commas. The arguments can be text strings, numbers, or single-cell references.

CONCATENATE Function in Action

In my spreadsheet example, cell B2 has the Month and Day (Jun 30), while cell C2 has the Year (2010). The following formula will combine these two in cell D2:

=CONCATENATE(B2,”, “,C2)

Notice that “, ” is the second argument, which is a comma followed by the space character, all in quotes. The result is: Jun 30, 2010. You get a better sense of the arguments by looking at the Function Arguments dialog box.

CONCATENATE Function Argument Dialog Box

While the CONCATENATE Function is all nice and proper and well documented, the Ampersand (&) operator is much easier to use in practice.

Ampersand Operator in Action

The same formula in cell E2 using the Ampersand operator:

=B2 & “, ” & C2

The Ampersand operator separates the different arguments to be combined in a text string. Much easier to use than typing out CONCATENATE, don’t you think?

In any event, both the CONCATENATE Function and Ampersand Operator return a Text String. Excel will recognize this text string as a Date, but there are problems associated with Dates entered as Text. You can’t change it’s numeric formatting, to name just one.

In my next post I’ll show you how to tell if the result of this formula is numeric or text by using the TYPE Function.  After all, Excel’s Date System shows Dates as integers, and that’s what I’m really after.