How to Concatenate Cells and keeping Date Format in Excel

scott @ March 4, 2023 Excel Examples

This post will guide you on how to combine text and date into the same cell in Excel while keeping the date format in Excel 2010/2013/2016 or Excel 365. We will show you how to concatenate text and date while preserving the date format, using both formula and VBA code methods.

In some cases, you may want to merge text with a date to create a more informative label or heading for your worksheet. However, when you concatenate cells that contain dates, the resulting string may not retain the original date format. This can be problematic when you need to sort or filter data by date.

Table of Contents

1. Video: Concatenate Cells and keeping Date Format
2. Concatenate Cells and Keeping Date Format using Formula
3. Concatenate Cells and Keeping Date Format with VBA Code
4. Conclusion
5. Related Functions

1. Video: Concatenate Cells and keeping Date Format

If you want to learn how to concatenate cells and keep the date format in Excel, you can explore both the formula and VBA code methods in this video.

2. Concatenate Cells and Keeping Date Format using Formula

Assuming that you have a list of data in range A1:B4, which contain product names and dates. And you want to concatenate product name and date value into one cell and keeping a certain date format. How to do it. You can use a formula based on the CONCATENATE function and the TEXT function to achieve the result. Like this:

=CONCATENATE(A1," ",TEXT(B1,"m/dd/yyyy"))

Type this formula into a blank cell（D1) and press Enter key. And then drag the AutoFill handle over to other cells to apply this formula.

Note:

The “Format Code” can be used in the excel Text function are shown in the below table.

Format Code	Description	Examples
0	only display digits in its place #.00 – Forces the function to display two decimal places	=Text(34.234,”$##.00″) result: $34.23
#	Display the placeholder	=Text(4.527,”#.##) result: 4.53
.	the position of Decimal Point	=Text(342.2,”0.00″) result: 342.20
d	Day of the month or day of week d– one or two digit number (1-31) dd– two digit number (01-31) ddd-abbreviated day of week (Mon to Sun) dddd-full name of day of week(Monday to Sunnday)	=Text(TODAY(),”DDDD”) result: Monday
m	The Month of the Year m– one or two digit number mm-two digit number mmm-abbreviated month(Jan to Dec) mmmm-full name of month(January to December))	=Text(TODAY(),”MM/DD/YY”) result:11/06/17
y	year yy-two digit representation of year(e.g.01,17) yyyy-four digit representation of year(e.g. 2001,2017)	=Text(TODAY(),”MM/DD/YY”) result:11/06/17
h	Hour h-one or two digit number (e.g. 1,23) hh-two digit number (e.g. 01,23)	=Text(14:16,”hh:mm”) result: 14:16
m	Minute m-one or two digit representation (e.g. 1,59) mm-two digit representation (e.g. 01,59)	=Text(14:16,”hh:mm”) result: 14:16
s	Second s-one or two digit representation (e.g. 1,59) ss=two digit representation (e.g. 01,59)

3. Concatenate Cells and Keeping Date Format with VBA Code

You can also use an VBA code to concatenate the selected cells and keeping all date as the original format in Excel. You just need to do the following steps:

Step1: Open your worksheet where you want to concatenate the text and date.

Step2: Press Alt + F11 to open the Visual Basic Editor.

Step3: In the Visual Basic Editor, go to Insert > Module.

Step4: In the module window, paste the following VBA code.

Sub ConcatenateDateAndText_excelhow()
    Dim rng As Range
    Dim destCell As Range
    Dim rowRange As Range
    Dim cell As Range
    
    On Error Resume Next
    Set rng = Application.InputBox("Select the range of cells to concatenate", "Select Range", Type:=8)
    Set destCell = Application.InputBox("Select the destination cell", "Select Destination", Type:=8)
    On Error GoTo 0
    
    If rng Is Nothing Then
        MsgBox "No range selected"
        Exit Sub
    End If
    
    If destCell Is Nothing Then
        MsgBox "No destination cell selected"
        Exit Sub
    End If
    
    ' Loop through each row in the selected range
    For Each rowRange In rng.Rows
        ' Concatenate the values in each row while preserving the date format
        destCell.Value = rowRange.Cells(1, 1).Value & " " & Format(rowRange.Cells(1, 2).Value, "dd/mm/yyyy")
        Set destCell = destCell.Offset(1, 0)
    Next rowRange
End Sub

Step5: Press Alt + F8 to open the Macro dialog box. And select the ConcatenateDateAndText_excelhow macro and click the Run button.

Step6: select the range of cells that you want to concatenate.

Step7: select a destination cell for the concatenated text and date. click OK.

Step8: The macro will run and concatenate the text and date in each row of the selected range while preserving the date format. The concatenated values will be written to the column starting from the selected destination cell.

4. Conclusion

Concatenating cells while preserving the date format can be easily achieved in Excel using either a formula or a VBA code.

The formula method involves using the TEXT function to convert the date value into a text string with a specific date format, then concatenating it with the other text values.

The VBA method involves using a macro that loops through each row of a selected range of cells, concatenates the text and date values in each row while preserving the date format.

5. Related Functions

Excel Text function
The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…