How to Concatenate Cells and keeping Date Format in Excel
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
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:
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.
The “Format Code” can be used in the excel Text function are shown in the below table.
|0||only display digits in its place |
#.00 – Forces the function to display two decimal places
|#||Display the placeholder||=Text(4.527,”#.##) |
|.||the position of Decimal Point||=Text(342.2,”0.00″) |
|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)
|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))
yy-two digit representation of year(e.g.01,17)
yyyy-four digit representation of year(e.g. 2001,2017)
h-one or two digit number (e.g. 1,23)hh-two digit number (e.g. 01,23)
m-one or two digit representation (e.g. 1,59)mm-two digit representation (e.g. 01,59)
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.
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)…
CommentsSo empty here ... leave a comment!