Let’s assume a scenario where a user is having data in two different columns. In the first column, the user has some text and in the second column, the user has either date or time values and the user wants to manipulate this data in such a way that he/she can combine the data of both of these columns in a third column.

Microsoft Excel provides us with two useful ways using which we can combine these two elements. The first one is theAmpersand (&) symbol/operator and the second one is the CONCAT()function.

Method 1: Combining text with date/time using Ampersand (&) operator

We can use the & operator to combine two elements. Let’s see this with an example.

We can see the formula =A1&” “&B1 in the formula bar, where A1 and B1 are the cell references for both the elements and &” “& in the middle signifies joining both the elements with a space in-between. Hence, our final output can be observed in the C1 cell. This method is useful when the elements we want to combine are of type text.

We can notice from the above image that the date value is treated like a number implicitly by Excel in the backend and hence a different value is shown. Now, to resolve this error, we can convert the date value to a text itself and then join both these elements together. The Date type can be converted to a text using the TEXT() function. In the TEXT() function we pass our cell reference as the first parameter (here B2) and the format of our date as the second parameter.

After converting the date element to the text type we can easily join both the elements and the final result can be seen in the image given above.

Method 2: Using the CONCAT() function

The CONCAT() function can be used to concatenate multiple texts with one another. The syntax for the CONCAT() function is given as:

=CONCAT(text1,text2,text3,….)

Where text1, text2, text3, etc. are different text elements.

We can convert the date element to text type using the same method that we saw in the previous method. Both the elements can be contacted using the CONCAT() function as:

In the formula, =CONCAT(A2,” “,TEXT(B2, “dd/mm/yyyy”)), A2 and B2 are the cell references of the elements. The second parameter ” ” represents a space since we want to join both the elements separated by a space.