Using the CONCATENATE function in Excel for string concatenation. Instead of using CONCATENATE, you can also use the & operator.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
How to use the Concatenate function in Excel
1. The CONCATENATE function below concatenates the string in cell A1 and the string in cell B1.
2. Using the & operator gives the same result.
3. The CONCATENATE function below concatenates the string in cell A1, adding the word “and” (putting the text in quotes) and the string in cell B1.
4. Using the & operator gives the same result.
5. The CONCATENATE function below concatenates the string in cell A1, adding a space and the string in cell B1.
6. Using the & operator gives the same result.
The CONCAT function in Excel 2016 and later versions is similar to the CONCATENATE function. Just replace CONCATENATE with CONCAT in the formulas shown above.
7. The CONCAT function can also concatenate different strings.
The TEXTJOIN function in Excel 2016 and later combines a range of strings using a delimiter (first parameter).
8. First, see all the extra spaces in column E below when you drag the CONCATENATE function in cell E2 down to cell E11.
9. The beauty of the TEXTJOIN function is that it can ignore empty cells (if the second parameter is set to TRUE).
10. When using the CONCATENATE function, use the TEXT function to format numbers.
Note: use 0 to display the nearest integer value. Use 0.0 for one decimal place. Use 0.00 for two decimal places,…
11. Use CHAR(10) to insert line breaks.
12. Let’s see one more interesting example of the TEXTJOIN function. First, the IF function below finds all members of Team 1.
Explanation: the IF function checks each cell in the Team area. If equal to the value in cell E3, it returns the equivalent in the Names area, otherwise it returns an empty string (double quotes with nothing in between).
13. The IF function returns an array constant stored in Excel’s memory. Select the formula in the formula bar and press F9 to see the array unchanged.
14. Add the TEXTJOIN function to concatenate these names. Use comma and space as separator (first parameter) and set second parameter to TRUE to remove empty strings. End the array formula by pressing CTRL + SHIFT + ENTER.
In addition, you can also view many other excel articles here.
