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

Returns the text string that is a concatenation of several strings.

text1The first text string.
text2(Optional) The second text string.

Remarks

* CONCAT was added in Excel 2016 to replace this function.
* This function is equivalent to the "&" character.
* The "text" can be a single cell reference or a range of cells.
* The "text" can be a text string or number.
* If the "text" is a range of cells, the values are not concatenated but are spilled over
* If you concatenate a cell that contains a numerical value, the result is converted to text. See Example 5
* If you concatenate a cell that is formatted as a date, the date serial number will be used.
* You can have a maximum of 255 arguments.
* If the resulting string has more than 32,767 characters, then #VALUE is returned.
* You can use the IF to return a value based on a condition.
* You can use the TEXT function to avoid the date serial number problem and convert the date into a recognisable date format before it gets concatenated.
* You can use the TEXTJOIN function to combine text from multiple ranges and add a delimiter.
* For the Microsoft documentation refer to support.office.com

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

1 - What is the combined string when you concatenate "the start" and "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 :" 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 :" with the contents from cell B5. The date needs to be explicitly formatted.
7 - What is the combined string when you concatenate the contents from cells "B1", "B2", "B3" combined with text strings " ".
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 line break delimiter character in between.
11 - What is the combined string when you concatenate a text string that has more than 32,767 characters.

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