Combine date and text

This tutorial shows how to combine a date and text through the use of an Excel formula, with the TEXT function or VBA

Excel

Combine date and text using the TEXT function

VBA

Combine date and text using VBA

EXCEL FORMULA 1. Combine date and text using the TEXT function

EXCEL

Hard coded formula

Cell reference formula

="Today is the "&TEXT(B5,"dd mmmm yyyy")

=C5&" "&TEXT(B5,"dd mmmm yyyy")

GENERIC FORMULA

="string"&TEXT(date,"dd mmmm yyyy")

ARGUMENTS
string: A text string that you want to combine with a date.
date: A date that you want to combine with text.

GENERIC FORMULA

="string"&TEXT(date,"dd mmmm yyyy")

ARGUMENTS
string: A text string that you want to combine with a date.
date: A date that you want to combine with text.

EXPLANATION

This formula uses the TEXT function with the "dd mmmm yyyy" format and an & sign to combine date and text.

Click on either the Hard Coded or Cell Reference button to view the formula that either has the text directly entered in the formula or referenced to a cell that captures the text that you want to combine with a date.

In this example we are combining a date captured in cell B5 with a specific text that is either directly entered into the formula or reference to cell C5.

VBA CODE 1. Combine date and text using VBA

VBA

Hard coded against single cell

Sub Combine_date_and_text()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'combine date and text
ws.Range("C5") = "Today is the " & WorksheetFunction.Text(ws.Range("B5"), "dd mmmm yyyy")

End Sub

Cell reference against single cell

Sub Combine_date_and_text()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'combine date and text
ws.Range("D5") = ws.Range("C5") & " " & WorksheetFunction.Text(ws.Range("B5"), "dd mmmm yyyy")

End Sub

Hard coded against range of cells

Sub Combine_date_and_text()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'combine date and text

For x = 5 To 8

ws.Range("C" & x) = "Today is the " & WorksheetFunction.Text(ws.Range("B" & x), "dd mmmm yyyy")
Next x

End Sub

Cell reference against range of cells

Sub Combine_date_and_text()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'combine date and text

For x = 5 To 8

ws.Range("C" & x) = ws.Range("C" & x) & " " & WorksheetFunction.Text(ws.Range("B" & x), "dd mmmm yyyy")
Next x

End Sub