Excel String Concatenation

Excel String Concatenation Using the & Operator

When used between two text strings, the & Operator returns 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:   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 the formulas in the above spreadsheet concatenate a space (" ") between the forenames and surnames.

The Excel Concatenate, Concat and Textjoin Functions

Excel provides three built-in functions to concatenate text strings. These are:

Concatenate	-	Joins together two or more text strings (Replaced by the Concat function in Excel 2019)
Concat	-	Joins together two or more text strings, or arrays of text strings (New in Excel 2019 - replaces the Concatenate function)
Textjoin	-	Joins together two or more text strings, separated by a delimiter (New in Excel 2019).

Note that the Concat function has replaced the Concatenate function in Excel 2019 (although the Concatenate function is still available in Excel 2019, for compatibility with older versions of Excel).

The only difference between these two functions is that the Concatenate function can only accept single text strings (or references to single cells containing text), whereas the Concat function can also accept arrays of text strings (or references to arrays of cells containing text).

Syntax of the Concatenate, Concat and Textjoin Functions

The syntax of the Concatenate function is:

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

The syntax of the Concat function is:

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

The syntax of the Textjoin function is:

where:

• The [delimiter] argument is an optional delimiter, to be inserted between each text string;
• The [ignore_empty] argument is an optional logical value that specifies whether empty cells should be ignored (default value = TRUE);
• The text arguments are two or more text strings (or arrays of text strings) that you want to join together.

Examples of the Concatenate, Concat and Textjoin Functions

The following spreadsheet shows three examples of Excel string concatenation, using the Concatenate, Concat and Textjoin functions.

Formulas:   A B C 1 Forename Surname Forename + Surname 2 Bill Brown =CONCATENATE( A2, " ", B2 ) 3 John Smith =CONCAT( A3, " ", B3 ) 4 Jim Jones =TEXTJOIN( " ", TRUE, A4:B4 )

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

Note that, in the above spreadsheet, a space is inserted between the forename and surname of the returned text string. This is supplied to the functions as:

• One of the text arguments in the Concatenate and Concat functions;
• The [delimiter] argument in the Textjoin function.

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, not the formatted date/time. An example of this is shown in the following spreadsheet:

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:   A B 1 Name DOB 2 John Smith 29-Jan-1980 3 = 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 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").

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 1 first line of text 2 second line of text 3 = A1 & CHAR(10) & A2

Results:   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. 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).

The Wrap Text option can also be controlled from within the Format Cells dialog box. Therefore, if you have an older versions of Excel, which does not have the shortcut button, you can wrap text as follows:

• Select the cell(s) that you want to wrap text in;

• Open the 'Format Cells' dialog box.

  (The easiest way to do this is to use the keyboard shortcut Ctrl + 1   - i.e. press the Ctrl key and while holding this down press 1).
• Select the Alignment tab in the 'Format Cells' dialog box.
• Check the box next to the Wrap text option and click OK.

Concatenate Quotes in Excel

It can be confusing if you want to concatenate quotes in Excel, as Excel will interpret a double quotation mark as the start or end of a text string.

Method 1

In order to make Excel understand that you require the double quotes to be part of the final concatenated text, you need to use four double quote characters in your concatenation formula. For example:

returns the concatenated string Jim said "Hi".

Method 2

Alternatively, you can use the Excel Char function to specify the quotes character. On most computer systems, the integer 34 represents the double quotation mark character. Therefore, the function CHAR(34) returns a double quote and the example above becomes:

which again, returns the concatenated string Jim said "Hi".