What is the Excel Concatenate Function and How Does It Work?

The CONCATENATE function joins two or more text strings together into one string. For example, if you have the customer’s first name in Column A and the last name in Column B, you could use “=CONCATENATE(A3,“ ”,B3)” to produce a string containing first name and last name.

Be aware that when you join two strings, Excel does not insert a space or any punctuation between the two. You must do it by inserting “ ” between the two strings, as shown above, or by replacing that space with a hyphen or other punctuation. The quotation marks are required.

Concatenating Numbers and Dates

The CONCATENATE function converts numbers to text. If you are joining the number 1047 and the string “Maple Street,” the formula “=CONCATENATE(1047,“ ”,“Maple Street”)” works just fine, producing “1047 Maple Street.”

With decimals or with dates, however, the result might be unintelligible. Take this example:

Note the spaces and the conjunction “and” added between the variables. Nevertheless, the formula results in “Beginning 41640 and ending 42004.” The two dates 1/1/2014 and 12/31/2014 turned into numbers, 41640 and 42004.

Why it happens – Excel stores all dates as numbers internally, displaying them as dates to make it easier for humans to read. The CONCATENATE function knows nothing about date formats, however, so it displays the date in Excel’s internal date-time code.

The solution – Use an additional function, TEXT, which converts a number to text using a specified format. While a thorough discussion of TEXT is a discussion for another post, you should be aware of two format codes that you’ll want to use with the CONCATENATE function:

• To format a number rounded to a specified number of decimal places, use the format string “#,##0.00”. Each 0 is a number that must be displayed, even if it is zero. Each # is a number that can be displayed if there is a digit, but will be left out if there is no digit. Using this format string, “=TEXT(39401.03895,“#,##0.00”)” will be displayed as “39,401.04,” and “=TEXT(10,“#,##0.00”)” will be displayed as “10.00.”
• To format a date, use “m” for the month, “d” for the day, and “y” for the year, or you can spell out the month names with “mmmm.” “=TEXT(41640,“m/d/y”) will appear as “1/1/14,” and “=TEXT(41640,“mmmm d, yyyy”) will appear as “January 1, 2014.”

Returning to the example above, enter the formula with TEXT functions on the dates:

CONCATENATE as shown above produces “Beginning 1/1/14 and ending December 31, 2014.”

A Trick to Bypass CONCATENATE

Do you really need CONCATENATE? Not really, because Excel provides a nifty shortcut.

You can join strings together by placing an ampersand (“&”) between them. This works exactly like the CONCATENATE function, but without having to type an eleven-character function name.

This produces exactly the same result as the previous version with the CONCATENATE function, but it’s shorter and easier.

Next Steps

Now that you know how to join strings together, you can use them anyplace you would use any other text—report fields, headers, or even conditions for IF or VLOOKUP statements. Concatenation is a convenient way to join multiple variables in one string or to join a label with a variable.