ExcelFunctions.net

Excel String Concatenation

Related Page:

Split a String in Excel

Home » Excel-Formulas » 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. The formulas are shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.

 Formulas:

  A B C
1 Forename Surname Forename + Surname
2     = "Bill" & " " & "Brown"
3 John Smith = A3 & " " & B3
4 Jim Jones = A4 & " " & B4

 Results:

  A B C
1 Forename Surname Forename + Surname
2     Bill Brown
3 John Smith John Smith
4 Jim Jones Jim Jones

Note that, as well as concatenating the forename and surname, the formulas in column C of the above spreadsheet add a space (" ") between these names.


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. Again, the formulas are shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.

 Formulas:

  A B C
1 Forename Surname Forename + Surname
2     = CONCATENATE( "Bill", " ", "Brown" )
3 John Smith = CONCATENATE( A3, " ", B3 )
4 Jim Jones = CONCATENATE( A4, " ", B4 )

 Results:

  A B C
1 Forename Surname Forename + Surname
2     Bill Brown
3 John Smith John Smith
4 Jim Jones Jim 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 concatenate function is described in more detail on the Excel concatenate function page.


Concatenate a Date and Time

Dates and Times are simply numbers in Excel, that are formatted to look like the dates that they represent. Therefore, if one of the elements of your Excel string concatenation is an Excel cell containing a date and time (or any other formatted number) you will simply get the unformatted number, as shown in the spreadsheets below.

 Formula:

  A B
1 Name DOB
2 John Smith 29-Jan-1980
3 = CONCATENATE( A2, " ", B2 )  

 Result (NOT as required!):

  A B
1 Name DOB
2 John Smith 29-Jan-1980
3 John Smith 29249  

If you want a date and 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 concatenation of text and a date is shown below:

 Formula:

  A B
1 Name DOB
2 John Smith 29-Jan-1980
3 = CONCATENATE( A2, " ", TEXT( B2, "dd-mmm-yyyy" ) )

 Result (as required):

  A B
1 Name DOB
2 John Smith 29-Jan-1980
3 John Smith 29-Jan-1980  

Note that the formula in the above example spreadsheet uses the 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 string containing the formatted text "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 have with 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
1 first line of text
2 second line of text
3 =CONCATENATE( A1, CHAR(10), A2 )

 Result:

  A
1 first line of text
2 second line of text
3 first 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. To access this option:

  • Right click on the cell, using the mouse
  • Select the option Format Cells... from the right mouse-click menu
  • Select the tab Alignment from the Format Options window that pops up
  • Check the box next to the Wrap text option and click OK
 Excel Wrap Text Option