What is the Excel CONCATENATE Function?
The excel CONCATENATE function combines text or numbers into a text.
CONCATENATE Syntax
CONCATENATE(text1, [text2], ...)
text1, required, the first data to combine, it could be text, number or cell reference
text2, optional, additional data to combine, handle up to 255 data and up to 8,192 characters in a result.
How to Use CONCATENATE Function in Excel
Below is an example of the CONCATENATE function usage and the results.
CONCATENATE Function #1
=CONCATENATE(A2,B2,C2)
The result is redgreenblue; no spaces are separating them. You must enter the space as a separator in the text argument.
CONCATENATE Function #2
=CONCATENATE(A3:C3)
You can’t use a range address in the CONCATENATE function arguments, only individual cell addresses are allowed.
No range address is the CONCATENATE function limitation. Excel fix this by providing two new functions in Excel 2016. Read “Other Alternative for CONCATENATE Function“
CONCATENATE Function #3
=CONCATENATE(A4," ",C4)
You can combine a text and a number. The result is Lotus 123, a text.
By the way, do any of you know what Lotus 123 is?
CONCATENATE Function #4
=CONCATENATE(A5,B5,C5)
You can combine numbers (not adding). The result is 123456, but a text.
Use the excel VALUE function to convert text to number.
CONCATENATE Function #5
=CONCATENATE(A6," ",B6," ",C6)
Why did 8/17/1945 change to the number 16666?
Excel stores date values in an integer number. If a date is combined with a text, then the integer number appears, to maintain the mm/dd/yyyy format, use the TEXT function.
For a more detailed explanation of how Excel stores date and time, read the following article
Other Alternative CONCATENATE Function
Use ampersand & sign
The result is the same as the CONCATENATE function; there is no separator between words. If you want to have a separator, you must type one by one for each word.
Use the CONCAT function, a better alternative than CONCATENATE function
One advantage of the CONCAT function compared to the CONCATENATE function, you can use the range address in the text argument.
If there is text in cells to join, use CONCAT function and the range address, but CONCAT function still has a limitation, you can’t add a separator between two words.
For a more detailed explanation, read the following article
Use TEXTJOIN function, the best choice for joining the text
The TEXTJOIN function is the best choice for combining the text. You can use the range address, and you can also add separators between words without having to input them repeatedly.
For a more detailed explanation, read the following article
