Buzz
In this article, Mytour will introduce you to how to use the Concatenate, Concat, or Textjoin functions to concatenate strings in Excel.
Using the & operator to concatenate strings in Excel
When used to concatenate two text strings, the & operator returns a single text string result, joining the original two text strings.
Below is an example of using the & operator to concatenate strings in Excel spreadsheet:
Formula:
Result:
Note that the formulas in the spreadsheet connect the space ('') between the first name and last name.
Concatenate Function, Concat and Textjoin Function in Excel
Excel comes with built-in functions for string concatenation. These functions include:
- Concatenate Function - Combines 2 or more text strings together (replaced by Concat function in Excel 2016). To understand more about the Concatenate function, readers can explore further in the article on concatenate function, string concatenation of Mytour.
- Concat Function - Combines 2 or more text strings or arrays of text strings (this is a new function in Excel 2016, replacing the Concatenate function).
- Textjoin Function - Combines 2 or more text strings, separated by a delimiter (this is a new function in Excel 2016).
Note: In Excel 2016, the Concat function replaces the Concatenate function (although the Concatenate function's functionality still exists in Excel 2016 to be compatible with older versions of Excel).
The main difference between the Concat function and the Concatenate function is that the Concatenate function only accepts a single text string (or a reference to a cell containing text), whereas the Concat function can accept arrays of text strings (or references to arrays of cells containing text).
Syntax of Concatenate, Concat, and Textjoin Functions
- Syntax of Concatenate Function:
CONCATENATE( text1, [text2], ... )
Where the text argument is 2 or more text strings that you want to combine together.
- Syntax of Concat Function:
CONCAT( text1, [text2], ... )
Where the text argument is 2 or more text strings (or arrays of text strings) that you want to combine together.
- Syntax of Textjoin Function:
TEXTJOIN( [delimiter], [ignore_empty], text1, [text2], ... )
Where:
+ The [delimiter] parameter is an optional delimiter inserted between the text strings.
+ The [ignore_empty] parameter is an optional logical value specifying whether empty cells should be ignored (default value = TRUE);
+ The text parameter is 2 or more text strings (or arrays of text strings) that you want to combine together.
Some Examples of Concatenate, Concat, and Textjoin Functions
Below are examples of string concatenation in Excel, using the Concatenate, Concat, and Textjoin functions:
Formula:
Result:
Note in the above spreadsheet, in the returned text string there is a space inserted between the first name and last name. This is because:
- One of the text parameters in the Concatenate and Concat functions.
- The [delimiter] parameter in the Textjoin function.
Concatenating Dates and Times
Dates and times in Excel are internally stored as numbers. The format of a cell will display different dates and times.
So if one of the string concatenation elements in Excel is a cell containing a date or time (or any other numeric format), essentially that number will be used in the concatenation rather than the date time format.
For example, in the spreadsheet below:
Concatenating text and invalid dates:
If you don't want dates or times (or any other numeric format in Excel) to be part of the concatenated text, you'll need to convert that number into a text string using the required format. To do this, you'll need the assistance of the Excel Text function.
Below is an example of concatenating text and dates:
Note the formula in the spreadsheet above uses the Text function, takes the value (in this case the date in cell B2), defines the format (in this case, the date format is 'dd-mmm-yyyy' - day-month-year), and returns a text string (in this example '29-Jan-1980').
Line Break Concatenation
A common issue many users encounter when using string concatenation formulas in Excel is how to insert a line break.
The simplest way to concatenate a line break is to use the Char function in Excel. On most computer systems, the integer 10 represents the line break character. So, the CHAR(10) function returns a line break. The spreadsheet below illustrates this:
Note that for the line break to be displayed in an Excel cell, the Wrap Text option must be enabled. In newer versions of Excel (since Excel 2007 and higher versions), users can activate it by using the Wrap Text button in the Alignment group on the Home tab on the Excel ribbon (as shown below):
Additionally, you can use the Format Cells dialog box to control the Wrap Text option. If older versions of Excel don't have a shortcut key, follow the steps below to open the Wrap Text option:
- Select the cells you want to wrap text within.
- Open the Format Cells dialog box.
The easiest way to open the Format Cells dialog box is to use the shortcut Ctrl + 1, meaning press and hold the Ctrl key while pressing 1.
- In the Format Cells dialog box, select the Alignment tab.
- Check the box next to the Wrap text option and then click OK.
Concatenating Double Quotes in Excel
This can be confusing if you want to concatenate double quotes in Excel. Excel interprets a double quote when it starts or ends a text string.
- Method 1:
To make Excel understand that you're requesting double quotes to be part of the final concatenated text, you'll need to use 4 double quote characters in the concatenation formula. For example:
='Jim said ' & '''' & 'Hi' & ''''
Returns concatenated string: Jim said 'Hi'.
- Method 2:
Additionally, you can use the Char function in Excel to specify the double quote character. On most computer systems, the integer 34 represents the double quote character. So the CHAR(34) function returns a double quote, as shown in the example below:
='Jim said' & CHAR(34) & 'Hi' & CHAR(34)
The result also returns concatenated string: Jim said 'Hi'.
Here's how to concatenate strings in Excel. If you want to split text strings in Excel, you can read and refer to the article on string splitting in Excel by Tamienphi.vn.
