How to Combine Text with Date or Time in Excel?

Microsoft Excel allows its users to store and manage different varieties of data in the form of rows and columns and store it inside an Excel workbook. Since Microsoft Excel allows its users to not only store data but also manipulate that data later on as per their needs. 

Combine Text with Date or Time in Excel

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.

Now, we can observe that our goal is to append data of one column with data of another column. Microsoft Excel provides us with two useful ways using which we can combine these two elements. The first one is the Ampersand (&) 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. But when we want to combine a text with a date or time then an ambiguous output will be shown this method because Excel treats date/time values as a number in the back-end. We can observe the image given below: 

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.

Now, as we can see this function can also concatenate only text values, hence we need to convert the date element to the text type and then pass that converted value as a parameter inside the CONCAT() function. 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.

saurabh48782

Follow

Improve

Article Tags :

• Excel
• Geeks-Premier-League-2022
• Excel-functions
• ExcelGuide