The Excel CONCATENATE Function

Basic Description

The Excel CONCATENATE function joins together a series of supplied text strings or other values, into one combined text string.

The format of the function is :

CONCATENATE( text1, [text2], ... )

where the text arguments are a set of one or more text strings or other values that you want to join together.

In Excel 2007 and Excel 2010, you can supply up to 255 text arguments, but in Excel 2003, the Concatenate function can only accept up to 30 text arguments.


Concatenate Function Examples

The following spreadsheets show the Concatenate function, used to join together the strings in columns A - C of the example spreadsheet.

The formulas are shown in the top spreadsheet and the results are shown in the spreadsheet below.

 Formulas:
Examples of use of the Excel Concatenate Function
 Results:
Excel Concatenate Function Results

Note that, in the examples above, text blocks have been added to separate the different fields. For example, in cell D2, a space (encased in quotes) has been used to separate the forename and surname.

Also note that in cell D3, the Text function has been used to convert the date value in cell C3 into a string, using the date format "dd/mm/yy". If the date was not converted in this way, the date part of the returned string would be displayed as the date's underlying value, which is the number 31810. This is explained further in the 'Trouble Shooting' section below.

Further information and examples of the Excel Concatenate function can be found on the Microsoft Office website.


Trouble Shooting

Some users encounter the following problem when using the Concatenate function:

Common Problem

The Concatenate function inserts a number in place of a date or time, as shown below:

 Formulas:
Example of Common Problem when using the Excel Concatenate Function
 Results:
Results of Common Problem when using the Excel Concatenate Function
Likely Reason

Dates and times within Excel are represented by numbers, and it is simply the formatting of a cell that causes these numbers to be displayed as dates and times. Therefore, when an Excel date or time is supplied to the Concatenate function the underlying numeric value is shown, instead of the formatted date or time (read more about Excel data types on the Excel Formatting page)

Solution

This problem can be solved by using the Excel Text function to convert the date or time value into a text string. This is shown in the example spreadsheet below:

 Formulas:
Solution to Common Problem when using the Excel Concatenate Function
 Results:
Results of Solution to Common Problem when using the Excel Concatenate Function
Return to the Excel Text Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net