We have two ways in which we can use to combine data in excel i.e using the merging cell option and the concatenate cell value.
What is concatenate in excel?
Concatenate is a process in excel that involves combining a few pieces of text or value that are in different cells. Concatenate focuses on combining the content in the cells. It is different from merging since what merging does is make many cells, a single large one.
Using excel concatenate function
In Excel, the CONCATENATE function is used to combine text and data from several cells into a single cell.
The syntax function of excel CONCATENATE is;
CONCATENATE (text1, [text2], ….)
In which text signifies cell reference, formula-driven value, or text string.
This function applies to all versions of excel i.e. Excel 2019-excel 2007 and Microsoft 365.
How to concatenate the value of several cells
To combine the values of cells A1 and B1 the simple concatenate formula is used.
=CONCATENATE(A1, B1)
The application of this formula will knit together the values without separate text strings. To separate the values with space use " " to the second argument.
=CONCATENATE(A1, " ", B1)
Concatenating a text string and cell value
Excel CONCATENATE function is not limited only to joining cell values it can be used also to concatenate various text strings to make a more meaningful result.
=CONCATENATE(A2, " ", B2, " Done")
As seen in row 2 of the picture above, the formula tells the user that a Math Cat has been completed. To separate the concatenated text strings, a space is placed before the phrase "Done."
Alternatively, the space can be added at the beginning of the middle of concatenating formula.
=CONCATENATE("See ", A1, " ", B1)
Concatenating a text string
You may concatenate the result of a formula with a text string that explains what the value truly is to make it more clear to your users.
For example, the formula =CONCATENATE("Today is ",TEXT(TODAY(), "dd-mmm-yy")) can be used to give the current date.
Points to note
To ensure that CONCATENATE function works effectively some simple guidelines have to be followed.
For the CONCATENATE function to work, there should at least one "text" argument.
If you see the return #VALUE! It means that the CONCATENATE function's argument is invalid.
Even when all the source values are numbers the value of the result of CONCATENATE function is always a text string.
Concatenate with Space, Comma, and Other Characters
Some values when joining may require commas, spaces, or various punctuation marks. They may include;
Concatenating two cells with space:
=CONCATENATE(A2, " " ,B2)
Concatenating two cells with a comma:
=CONCATENATE(A3, ", ", B3)
Concatenating two cells with space:
=CONCATENATE(A4, " – ", B4)
In conclusion, we have seen how to concatenate is a process in excel that involves combining a few pieces of text or value that are in different cells and several examples attached to them. However, in Excel 365, Excel 2019, and excel online instead of CONCATENATE function, the Concat function is used. Thus new versions of excel are most likely to adopt the CONCAT function.