CONCAT(text1 [,text2] [..])

Returns the text string that is a concatenation of cell ranges and strings.

text1The first text string or cell range.
text2(Optional) The second text string or cell range.

Remarks

* Added in Excel 2019.
* This function replaces the CONCATENATE function.
* This function is equivalent to the "&" character.
* The "text" can be a range of cells (as opposed to CONCATENATE which must have single cell references).
* The "text" can be a text string or number.
* The "text" can be an array of strings or numbers.
* If the arguments are numbers they do not have to be enclosed in speech marks.
* If the resulting strings has more than 32,767 characters, then #VALUE! is returned.
* You can concatenate text with line breaks using CHAR(10) on Windows or CHAR(13) on Mac.
* You can include a quotation mark in a text string by using double quotes.
* If the function is not available, then #NAME? is returned.
* You can have a maximum of 253 text arguments.
* You can use the IF to return a value based on a condition.
* You can use the TEXT function to return a number as a formatted text string.
* You can use the TEXTJOIN function to include a delimiter and/or remove empty arguments.
* For the Microsoft documentation refer to support.office.com

 AB
1=CONCAT("the start"," and the end") = the start and the endone
2=CONCAT("ab","cd","ef","gh") = abcdefghtwo
3=CONCAT(1,2,3,4) = 1234three
4=CONCAT(B3,B4) = three200200
5=CONCAT("Date is : ",B5) = Date is : 438311/1/2020
6=CONCAT("Date is : ",TEXT(B5,"dd-mmm-yy")) = Date is : 01-Jan-20 
7=CONCAT(B1," ",B2," ",B3) = one two three 
8=CONCAT("one","two")&"three" = onetwothree 
9=B1&"-"&B2 = one-two 
10=B1&CHAR(10)&B2 = one two 
11=CONCAT({"one"," ","two"," ",20}) = one two 20 
12=CONCAT(B1:B3) = onetwothree 
13=CONCAT(B1,"""",B2) = one"two 
14=CONCAT(REPT("a",32768)) = #VALUE! 

1 - What is the combined string when you concatenate "the start" with "and the end".
2 - What is the combined string when you concatenate "ab", "cd", "ef" and "gh".
3 - What is the combined string when you concatenate the numerical values 1, 2, 3 and 4.
4 - What is the combined string when you concatenate the contents from cells "B3" and "B4".
5 - What is the combined string when you concatenate the text string "Date is :" with the contents from cell B5. Notice the date is the date serial number with no formatting.
6 - What is the combined string when you concatenate the text string "Date is :" with the contents from cell B5. The date has been explicitly formatted.
7 - What is the combined string when you concatenate the contents from cells "B1", "B2", "B3" with spaces in between.
8 - What is the combined string when you concatenate "one", "two" and "three".
9 - What is the combined string when you concatenate the contents from cells "B1" and "B2" with a dash delimiter character in between.
10 - What is the combined string when you concatenate the contents from cells "B1" and "B2" with a linefeed delimiter character in between.
11 - What is the combined string when you concatenate an array of string values.
12 - What is the combined string when you concatenate the cell range "B1:B3".
13 - What is the combined string when you concatenate the contents from cells "B1" and "B2" with a double quote, special character in between.
14 - What is the combined string when you concatenate a text string that has more than 32,767 characters.

© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited Top