How to quickly combine text and date into same cell in Excel?

When working with datasets in Excel that contain text values in one column and corresponding dates in another, you may need to combine both into the same cell for improved clarity or further processing. A common problem is that directly joining these columns may convert the date into a serial number rather than displaying it in a familiar date format. For example, combining "Meeting" and a date cell could output "Meeting 45361" instead of the desired "Meeting 05/01/2024". Ensuring the date maintains its readable format during concatenation is essential, particularly when preparing reports or sharing files. In this article, you will learn practical and efficient ways to correctly combine text and date into one cell in Excel, avoiding formatting issues and providing clear, user-friendly results.

Combine text and date into one cell with formulas

Combine text and date into one cell with Kutools for Excel

Combine text and date into one cell using VBA (keep date formatting)

Combine text and date into one cell with formulas

Excel formulas provide a fast and flexible way to join text and date values together while preserving readable date formats. This approach is particularly useful when you want to automate the process for multiple rows, or if you need to choose a specific separator between the text and date. The formulas shown below allow you to control date display and avoid the issue of dates appearing as serial numbers.

1. Enter the following formula in a blank cell next to your data (e.g., enter in C2 if your data is in columns A and B):

=A2 & " " & TEXT(B2,"mm/dd/yyyy")

2. Press Enter to confirm the formula. Then, drag the fill handle down to apply the formula to other rows as needed. You will see that the text and date are now combined in each cell, with the date displayed in a standard format.

Notes and tips:

• You can use the alternative formula below, which works in the same way:

  =CONCATENATE(A2," ",TEXT(B2,"mm/dd/yyyy"))

• If you prefer a different separator (such as a dash, comma, or custom text), simply change the value inside the quotation marks. For example, to use a dash:

  =A2 & " - " & TEXT(B2,"mm/dd/yyyy")

• The TEXT function is essential for converting date values to readable strings. If omitted, dates will display as numbers (Excel's internal date codes).
• If your source dates are in a different format (such as day-month-year or ISO8601), you can update "mm/dd/yyyy" inside the TEXT function to the desired display ("yyyy-mm-dd", "dd-mmm-yyyy", etc.).
• If you want to combine two date columns with a separator, use one of these formulas:

  =TEXT(A2,"mm/dd/yyyy") & " - " & TEXT(B2,"mm/dd/yyyy")

  =CONCATENATE(TEXT(A2,"MM/DD/YYYY")," - ",TEXT(B2,"MM/DD/YYYY"))

This solution is most suitable for relatively small to medium datasets and is highly customizable. However, if you need to concatenate cells in bulk across an extensive dataset or require different separators for different rows, alternatives like VBA or dedicated add-ins may be more efficient.

If you encounter errors such as #VALUE! when referencing blank cells or formatted cells that are not dates, check that the data source is consistent and formatted correctly.

Combine text and date into one cell with Kutools for Excel

For users seeking a more visual and straightforward approach without entering complex formulas, Kutools for Excel provides a convenient solution. The Combine feature enables you to merge columns, rows, or cells while keeping, deleting, or merging content just by choosing your preferences in a user-friendly dialog. This is especially valuable for users who frequently need to combine columns, wish to avoid manual formula input, or need to process a large range of cells efficiently.

If you have installed Kutools for Excel, follow the steps below:

1. Select the range of data you want to combine, such as two or more adjacent columns.

2. Go to Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data, as shown below:

3. In the Combine Columns or Rows dialog box, set your options as follows:

• (1.) Select Combine columns to merge data from two columns into one.
• (2.) Under Specify a separator, choose the symbol or text to separate the combined contents (such as a space, dash, or comma).
• (3.) Decide whether the combined result will appear in the leftmost or rightmost cell of each row.
• (4.) Choose an option to either keep or delete original cell content after combining, and, if desired, merge the cells containing the combined result.

4. After configuring the settings, click OK to apply. The specified columns will be combined in each row using your chosen separator and the result will be displayed according to your preferences.

Keep contents of combined cells	Delete contents of combined cells	Merge the combined cells

Notes and best practices:

• This function also works for combining two cells containing dates, and the results will follow your specified separator and position.
• Remember to preview your selections and separator style before clicking OK, especially for large datasets, to avoid unwanted merging or deletions.
• If your cells contain various data types (numbers, dates, and text), Kutools will combine them according to cell display format, avoiding most formatting issues encountered with formulas.

Click to know more details about this Combine function of Kutools for Excel.

With the Combine feature, you can also quickly accomplish:

Merge and Combine Columns without Losing Data in Excel

Merge and Combine Rows without Losing Data in Excel

How to combine cells into a cell with space, commas and semicolon in Excel?

Download and free trial Kutools for Excel Now!

Combine text and date into one cell using VBA (keep date formatting)

For users who frequently need to concatenate text and date columns in bulk, or work with very large datasets that would be time-consuming to process with manual formulas, a custom VBA macro can be a practical solution. This approach not only enhances productivity but also gives full control over the formatting of the combined results. The macro below ensures that dates maintain a desired format during the concatenation process, preventing the common issue of dates turning into serial numbers.

Applicable scenarios: This method is ideal when you want to automate the combination of text and date columns for entire tables, apply flexible date formatting, or include this functionality in routine Excel tasks. VBA also allows easy adjustment to separators and date formats, catering to different reporting standards.

Advantages: High efficiency for large volumes of data, customizable, and easy to adapt for repetitive tasks. Disadvantages: Requires macro-enabled workbooks and some knowledge of the VBA editor.

1. Click Developer > Visual Basic to open the Microsoft Visual Basic for Applications window. In the editor, go to Insert > Module, and paste the following VBA code into the module window:

Sub CombineTextAndDate()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim textCol As Range, dateCol As Range
    Dim resultCol As Range
    Dim i As Long
    Dim dateFormat As String
    Dim separator As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set rng = Application.InputBox("Select the data range (including text and date columns):", xTitleId, Selection.Address, Type:=8)
    Set textCol = Application.InputBox("Select the text column (single column):", xTitleId, rng.Columns(1).Address, Type:=8)
    Set dateCol = Application.InputBox("Select the date column (single column):", xTitleId, rng.Columns(2).Address, Type:=8)
    Set resultCol = Application.InputBox("Select where to output the result (single column with same number of rows):", xTitleId, rng.Columns(rng.Columns.Count).Offset(0, 1).Address, Type:=8)
    
    separator = Application.InputBox("Enter separator (e.g. space, dash, comma):", xTitleId, " ")
    dateFormat = Application.InputBox("Enter date format (e.g. mm/dd/yyyy):", xTitleId, "mm/dd/yyyy")
    
    If textCol.Rows.Count = dateCol.Rows.Count And textCol.Rows.Count = resultCol.Rows.Count Then
        For i = 1 To textCol.Rows.Count
            resultCol.Cells(i, 1).Value = textCol.Cells(i, 1).Value & separator & Format(dateCol.Cells(i, 1).Value, dateFormat)
        Next i
        MsgBox "Text and date successfully combined!", vbInformation, xTitleId
    Else
        MsgBox "Ranges not matched in size!", vbExclamation, xTitleId
    End If
    
    On Error GoTo 0
End Sub

2. Close the VBA editor. In Excel, press Alt + F8, select CombineTextAndDate, and click Run. Follow the prompts to select your text column, date column, output range, and desired formats/separators. The macro will populate the results automatically in the chosen destination.

Practical tip: When prompted, you may enter different separators (such as a dash -, comma ,, or any custom text) and any standard date format (like dd-mmm-yyyy) to match the style of your report. If your results do not appear as expected, ensure that all selected ranges align exactly in row count, and that the date column contains actual date values.

If you encounter errors stating "Ranges not matched in size," double-check your selections and ensure the text, date, and output columns all have the same number of rows. After combining, review the result column for any unexpected formatting and adjust the date format or separator as needed.

Overall, VBA automation is especially efficient for recurring or bulk tasks where manual operations would be time-consuming, and can be reused by simply running the macro for new data.