Excel String Concatenation

Excel string concatenation (i.e. joining together text strings in Excel) can be performed by either using using the Excel & Operator, or using the Excel Concatenate Function. These methods are each discussed below.

Excel String Concatenation Index:
Concatenate Using the & Operator
The Excel Concatenate Function
Concatenate a Date and Time
Concatenate a Line Break

Excel String Concatenation Using the & Operator

The & Operator, when used between two text strings, tells Excel to return a single text string, consisting of the concatenation of the two original strings.

The spreadsheet below shows three examples of Excel string concatenation, using the & operator.

 Formulas:
 ABC
1ForenameSurnameForename + Surname
2  = "Bill" & " " & "Brown"
3JohnSmith= A3 & " " & B3
4JimJones= A4 & " " & B4
 Results:
 ABC
1ForenameSurnameForename + Surname
2  Bill Brown
3JohnSmithJohn Smith
4JimJonesJim Jones

Note that the formulas in column C of the above spreadsheet concatenate a space (" ") between the forenames and surnames.


The Excel Concatenate Function

The Excel Concatenate function returns the contenation of two or more supplied text strings. The syntax of the function is:

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

where the text arguments are two or more text strings that you want to join together.

Three examples of Excel string concatenation, using the Concatenate function, are shown in the spreadsheet below.

 Formulas:
 ABC
1ForenameSurnameForename + Surname
2  =CONCATENATE( "Bill", " ", "Brown" )
3JohnSmith=CONCATENATE( A3, " ", B3 )
4JimJones=CONCATENATE( A4, " ", B4 )
 Results:
 ABC
1ForenameSurnameForename + Surname
2  Bill Brown
3JohnSmithJohn Smith
4JimJonesJim Jones

Once again, in the above example, a space (" ") is also supplied to the function, so that this will be inserted between the forename and surname of the returned text string.

The Excel Concatenate function is described in more detail on the Excel Concatenate Function page.


Concatenate a Date and Time

Dates and Times in Excel are stored internally as numbers. It is just the formatting of a cell that causes a number to be displayed as the date and/or time that it represents.

Therefore, if one of the elements of your Excel string concatenation is an Excel cell containing a date or time (or any other formatted number) it is the underlying number, that will be used in your your concatenated string. An example of this is shown in the following spreadsheet:

Incorrect Concatenation of a Text String and a Date

 Formula:
 AB
1NameDOB
2John Smith29-Jan-1980
3=CONCATENATE( A2, " ", B2 ) 
 Result (Oops!):
 AB
1NameDOB
2John Smith29-Jan-1980
3John Smith 29249 

If you want a date or a time (or any other formatted number in Excel) to form a part of your concatenated text string, you need to convert the number to a text string using the required formatting. This can be done using the Excel Text function.

An example of the correct concatenation of text and a date is shown below:

 Formula:
 AB
1NameDOB
2John Smith29-Jan-1980
3=CONCATENATE( A2, " ", TEXT( B2, "dd-mmm-yyyy" ) )
 Result (as required!):
 AB
1NameDOB
2John Smith29-Jan-1980
3John Smith 29-Jan-1980 

Note that the formula in the above example spreadsheet uses the Excel Text function, which receives a value (in this case the date in cell B2), and a formatting definition (in this case, the date format "dd-mmm-yyyy"), and returns a text string (in this example "29-Jan-1980").

For more details on the Text function, and the formatting options available to this function, see the Excel Text function page.


Concatenate a Line Break

A common problem that many users encounter, when using Excel string concatenation formulas, is how to insert a line break.

The easiest way to concatenate a line break is to use the Excel Char function. On most computer systems, the integer 10 represents the line break character. Therefore, the function CHAR(10) returns a line break. This is illustrated in the example spreadsheet below:

 Formula:
 A
1first line of text
2second line of text
3=CONCATENATE( A1, CHAR(10), A2 )
 Results:
 A
1first line of text
2second line of text
3first line of text
second line of text

Note that, for a line break to be displayed in an Excel cell, the Wrap Text option must be enabled. In recent versions of Excel (2007 and later), this can be controlled by the Wrap Text button, which is located in the 'Alignment' group of the Home tab of the Excel ribbon (see below).

Excel Wrap Text Button
Excel Wrap Text Option

The Wrap Text option is also available in the Format Cells dialog box. Therefore, if you have an older versions of Excel, which does not have the shortcut button, you can access the option as follows: