Text strings are sequences of characters, such as words, sentences, or numbers, that are stored in cells or entered as arguments in formulas. Sometimes, you may want to combine or join two or more text strings together to create a new text value. For example, you may want to concatenate first and last names, or combine text with dates and numbers.
There are different ways to join text strings in Excel formula, depending on your needs and preferences. In this article, we will explore the following methods:
- Using the concatenation operator (&)
- Using the CONCATENATE function
- Using the TEXTJOIN function
- Using the CONCAT function
We will also provide a detailed example with real data to demonstrate how to use these methods in practice.
Before we dive into the specific methods, let’s review some basic concepts and terms related to joining text strings in Excel formula.
- A delimiter is a character or a string of characters that separates the text values that are joined together. For example, a comma, a space, or a dash can be used as delimiters. A delimiter can be specified as an argument in some functions, or entered as a text string in a formula.
- An empty cell is a cell that contains no data or formula. Empty cells can be ignored or included when joining text strings, depending on the function or option used.
- A cell reference is a way to refer to a cell or a range of cells in a formula. For example, A1, B2:C5, or D$6 are cell references. Cell references can be used as arguments in functions, or combined with other text strings in a formula.
- A text function is a built-in function in Excel that performs some operation on text values, such as joining, splitting, extracting, or formatting. Text functions usually begin with the word TEXT, such as TEXTJOIN, TEXT, or TEXTVALUE.
Procedures
Now that we have covered the basic theory, let’s see how to join text strings in Excel formula using different methods.
Using the concatenation operator (&)
The simplest and most common way to join text strings in Excel formula is to use the concatenation operator (&). This operator can be used to join two or more text strings, cell references, or a combination of both. The syntax is:
=text1&text2&text3&…
Where text1, text2, text3, and so on are the text values or cell references that you want to join together. You can also use quotation marks to enter text strings directly in the formula, or use other functions to return text values.
For example, to join the text strings in cells A1 and B1 with a space, you can use the following formula:
=A1&” “&B1
To join the text strings in cells A2 and B2 with a comma and a space, you can use the following formula:
=A2&”, “&B2
To join the text string “Hello” with the value in cell A3, you can use the following formula:
=“Hello”&A3
To join the text string “The date is ” with the date value in cell A4, formatted as “mmm d, yyyy”, you can use the following formula:
=”The date is “&TEXT(A4,“mmm d, yyyy”)
The concatenation operator (&) is easy to use and flexible, but it has some limitations. For example, it does not allow you to specify a delimiter for multiple text values, and it does not ignore empty cells. If you want to join a large number of text values or ranges, you may need to use a different method.
Using the CONCATENATE function
Another way to join text strings in Excel formula is to use the CONCATENATE function. This function is similar to the concatenation operator (&), but it allows you to enter up to 255 text values or cell references as separate arguments, separated by commas. The syntax is:
=CONCATENATE(text1, [text2], …)
Where text1, text2, and so on are the text values or cell references that you want to join together. You can also use quotation marks to enter text strings directly in the function, or use other functions to return text values.
For example, to join the text strings in cells A1, B1, and C1 with a space, you can use the following function:
=CONCATENATE(A1, ” “, B1, ” “, C1)
To join the text strings in cells A2, B2, and C2 with a comma and a space, you can use the following function:
=CONCATENATE(A2, “, “, B2, “, “, C2)
To join the text string “Hello” with the value in cell A3, you can use the following function:
=CONCATENATE(“Hello”, A3)
To join the text string “The date is ” with the date value in cell A4, formatted as “mmm d, yyyy”, you can use the following function:
=CONCATENATE(“The date is “, TEXT(A4, “mmm d, yyyy”))
The CONCATENATE function is more convenient than the concatenation operator (&) when you need to join a large number of text values or ranges, but it has some drawbacks. For example, it does not allow you to ignore empty cells, and it may be replaced by the CONCAT function in future versions of Excel.
Using the TEXTJOIN function
A more advanced way to join text strings in Excel formula is to use the TEXTJOIN function. This function allows you to join text values from multiple ranges and/or strings, and specify a delimiter between each text value. It also allows you to ignore empty cells if you want. The syntax is:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
Where:
- delimiter is the text string that you want to use as a separator between each text value. It can be one or more characters, such as a comma, a space, or a dash. You need to enclose the delimiter in quotation marks, or use a cell reference that contains the delimiter. If you want to use no delimiter, you can enter an empty string (“”).
- ignore_empty is a logical value (TRUE or FALSE) that indicates whether you want to ignore empty cells or not. If you enter TRUE, the function will skip any empty cells in the text values. If you enter FALSE, the function will include empty cells in the output.
- text1, text2, and so on are the text values or cell references that you want to join together. You can enter up to 252 text arguments, including text1. Each argument can be a text string, a cell reference, a range of cells, or an array of strings.
For example, to join the text strings in cells A1, B1, and C1 with a space, and ignore any empty cells, you can use the following function:
=TEXTJOIN(” “, TRUE, A1:C1)
To join the text strings in cells A2, B2, and C2 with a comma and a space, and include any empty cells, you can use the following function:
=TEXTJOIN(“, “, FALSE, A2:C2)
To join the text string “Hello” with the value in cell A3, and use a dash as a delimiter, you can use the following function:
=TEXTJOIN(“-”, TRUE, “Hello”, A3)
To join the text string “The date is ” with the date value in cell A4, formatted as “mmm d, yyyy”, and use a colon as a delimiter, you can use the following function:
=TEXTJOIN(“:”, TRUE, “The date is “, TEXT(A4, “mmm d, yyyy”))
The TEXTJOIN function is more powerful and flexible than the concatenation operator (&) and the CONCATENATE function, but it has some limitations. For example, it is only available in Excel 2019 and later versions, and it may return an error if the resulting string exceeds 32,767 characters (the cell limit).
Using the CONCAT function
Another way to join text strings in Excel formula is to use the CONCAT function. This function is similar to the TEXTJOIN function, but it does not allow you to specify a delimiter or ignore empty cells. It simply joins all the text values from the specified ranges and/or strings. The syntax is:
=CONCAT(text1, [text2], …)
Where text1, text2, and so on are the text values or cell references that you want to join together. You can enter up to 253 text arguments, including text1. Each argument can be a text string, a cell reference, a range of cells, or an array of strings.
For example, to join the text strings in cells A1, B1, and C1, you can use the following function:
=CONCAT(A1:C1)
To join the text strings in cells A2, B2, and C2, you can use the following function:
=CONCAT(A2:C2)
To join the text string “Hello” with the value in cell A3, you can use the following function:
=CONCAT(“Hello”, A3)
To join the text string “The date is ” with the date value in cell A4, formatted as “mmm d, yyyy”, you can use the following function:
=CONCAT(“The date is “, TEXT(A4, “mmm d, yyyy”))
The CONCAT function is a simpler and faster alternative to the TEXTJOIN function, but it has less features and options. For example, it does not allow you to add a delimiter between the text values, and it may include empty cells in the output. It is also only available in Excel 2019 and later versions, and it may replace the CONCATENATE function in future versions.
Example
To illustrate how to join text strings in Excel formula using different methods, let’s consider the following scenario:
- You have a table of employee data in cells A1:E6, as shown below:
| First Name | Last Name | Department | Salary | Bonus |
|---|---|---|---|---|
| John | Smith | Sales | 50000 | 10% |
| Jane | Doe | Marketing | 60000 | 15% |
| Bob | Lee | IT | 70000 | 20% |
| Alice | Wong | HR | 80000 | 25% |
| Tom | Jones | Finance | 90000 | 30% |
- You want to create a new column (F) that contains the full name of each employee, separated by a comma and a space.
- You also want to create another new column (G) that contains the total income of each employee, which is the sum of the salary and the bonus, formatted as currency with two decimal places, and preceded by the text “Total income: “.
- You want to use different methods to join text strings in Excel formula, and compare the results.
Here are the steps to follow:
- To use the concatenation operator (&) to join the first name and the last name in column F, enter the following formula in cell F2, and copy it down to the rest of the column:
=A2&”, “&B2
- To use the CONCATENATE function to join the first name and the last name in column F, enter the following formula in cell F2, and copy it down to the rest of the column:
=CONCATENATE(A2, “, “, B2)
- To use the TEXTJOIN function to join the first name and the last name in column F, enter the following formula in cell F2, and copy it down to the rest of the column:
=TEXTJOIN(“, “, TRUE, A2:B2)
- To use the CONCAT function to join the first name and the last name in column F, enter the following formula in cell F2, and copy it down to the rest of the column:
=CONCAT(A2:B2)
- To use the concatenation operator (&) to join the text “Total income: ” with the total income in column G, enter the following formula in cell G2, and copy it down to the rest of the column:
=“Total income: “&TEXT(D2*(1+E2),”$#,##0.00”)
- To use the CONCATENATE function to join the text “Total income: ” with the total income in column G, enter the following formula in cell G2, and copy it down to the rest of the column:
=CONCATENATE(“Total income: “, TEXT(D2*(1+E2), “$#,##0.00”))
- To use the TEXTJOIN function to join the text “Total income: ” with the total income in column G, enter the following formula in cell G2, and copy it down to the rest of the column:
=TEXTJOIN(“”, TRUE, “Total income: “, TEXT(D2*(1+E2), “$#,##0.00”))
- To use the CONCAT function to join the text “Total income: ” with the total income in column G, enter the following formula in cell G2, and copy it down to the rest of the column:
=CONCAT(“Total income: “, TEXT(D2*(1+E2), “$#,##0.00”))
The final table should look like this:
| First Name | Last Name | Department | Salary | Bonus | Full Name | Total Income |
|---|---|---|---|---|---|---|
| John | Smith | Sales | 50000 | 10% | John, Smith | Total income: $55,000.00 |
| Jane | Doe | Marketing | 60000 | 15% | Jane, Doe | Total income: $69,000.00 |
| Bob | Lee | IT | 70000 | 20% | Bob, Lee | Total income: $84,000.00 |
| Alice | Wong | HR | 80000 | 25% | Alice, Wong | Total income: $100,000.00 |
| Tom | Jones | Finance | 90000 | 30% | Tom, Jones | Total income: $117,000.00 |
As you can see, the results are the same for each method, except for the CONCAT function, which does not add a delimiter between the first name and the last name. Therefore, you can choose any method that suits your preference and Excel version, as long as you are aware of the advantages and disadvantages of each one.
