Excel VBA: Get the Date to the Right of a String

VBA & macros · Updated November 21, 2026

If a cell contains a text label and the date is in the cell immediately to its right, use VBA’s Find method to locate the label, then read the adjacent cell with Offset(0, 1). Check the value with IsDate before converting it with CDate; the example below shows the complete pattern.

What “the date to the right” means

This approach treats the string as a label in one cell and the date as a value in the next cell on the same row. For example, a worksheet might have “Invoice date” in D7 and a date in E7. The macro searches for the label, then moves one column to the right, regardless of which row contains the match.

That is different from extracting a date written inside the same cell as a string, such as “Invoice date: 3/14/2026”. In that case, Offset would read the neighboring cell, not the date inside the text. The two tasks need different methods, so first check whether your label and date occupy separate cells.

Find the label and read the adjacent cell

For Microsoft Excel for Microsoft 365 on Windows, this macro searches the active worksheet for an exact match to “Invoice date.” If it finds the label, it checks the cell on its right and reports the result in a standard year-month-day format.

Sub GetDateRightOfLabel()
    Dim ws As Worksheet
    Dim foundCell As Range
    Dim dateCell As Range
    Dim rawValue As Variant
    Dim extractedDate As Date

    Set ws = ActiveSheet

    ' Find the label in displayed cell values.
    Set foundCell = ws.Cells.Find( _
        What:="Invoice date", _
        After:=ws.Cells(ws.Rows.Count, ws.Columns.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "The label was not found."
        Exit Sub
    End If

    If foundCell.Column = ws.Columns.Count Then
        MsgBox "The label is in the last column; there is no cell to its right."
        Exit Sub
    End If

    Set dateCell = foundCell.Offset(0, 1)
    rawValue = dateCell.Value

    If IsDate(rawValue) Then
        extractedDate = CDate(rawValue)
        MsgBox Format$(extractedDate, "yyyy-mm-dd")
    Else
        MsgBox "The cell to the right does not contain a recognizable date."
    End If
End Sub

Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. On Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the code, and run the macro while the intended worksheet is active. On a Mac, Option+F11 (sometimes Fn+Option+F11) opens the editor, depending on the keyboard settings; the VBA editor and macro logic are also available in desktop Excel for Mac. Excel for the web can open a macro-enabled workbook but cannot run VBA macros.

How Find and Offset work

Range.Find searches a range for a specified value and returns a Range object for the matching cell. In the example, ws.Cells means the search covers the worksheet. LookAt:=xlWhole requires the cell value to match the label exactly. If the cell says “Invoice date (local)” instead, that search will not match it.

To match a label that appears as part of a longer cell value, change LookAt:=xlWhole to LookAt:=xlPart. That can be useful, but be aware it may match an unintended cell—for example, searching for “Date” could find “Payment date” before a more specific label. Match case is turned off in the example, so uppercase and lowercase variations are treated alike.

Excel remembers some Find dialog settings between searches. Specifying options such as LookIn, LookAt, search order, direction, and format in the VBA call makes the macro’s search behavior more predictable. Offset(0, 1) means zero rows down and one column to the right. To read the cell one row below instead, the corresponding offset would be Offset(1, 0).

Use a known cell address when you do not need a search

If the label is always in a known location, such as D7, searching the whole sheet is unnecessary. Set a range directly and read the adjacent cell:

Sub GetDateFromKnownCell()
    Dim ws As Worksheet
    Dim dateCell As Range
    Dim rawValue As Variant
    Dim extractedDate As Date

    Set ws = ActiveSheet
    Set dateCell = ws.Range("D7").Offset(0, 1)
    rawValue = dateCell.Value

    If IsDate(rawValue) Then
        extractedDate = CDate(rawValue)
        MsgBox Format$(extractedDate, "yyyy-mm-dd")
    Else
        MsgBox "E7 does not contain a recognizable date."
    End If
End Sub

Here, D7 is the assumed label location and E7 is the cell being checked. If the workbook structure is stable, using a known address is simpler and avoids ambiguity when the same label appears multiple times. If rows can be added or labels move, searching is more flexible.

Excel dates, date serials, and text

Excel stores ordinary dates as numeric serial values, with a number format controlling how they appear. A cell displaying “Mar 14, 2026” may therefore hold a numeric date value rather than those words as text. A cell that looks the same can instead contain text imported from another system. The display alone does not always reveal the underlying type.

IsDate checks whether a value can be interpreted as a date, and CDate converts a recognizable value to VBA’s Date type. If the neighboring cell contains a valid Excel date, this is usually straightforward. If it contains text such as “March 14, 2026,” VBA may interpret it according to the computer’s regional date settings. Text such as “03/04/2026” is ambiguous: it can mean March 4 or April 3 depending on the date convention.

For reliable workbooks, store dates as actual Excel date values rather than locale-dependent text. If imported text has a known fixed order, parse its parts explicitly instead of assuming that CDate will interpret every string the same way on every computer. Do not treat every number as a date merely because it passes a broad check; ordinary numbers can also resemble valid date serials. If the data source is mixed, validate the expected range and layout as well as the type.

Return the date to another cell

A macro may need to place the found date in a result cell rather than show a message. After validating and converting the value, assign extractedDate to the desired destination cell. For example, the following line writes the date two columns to the right of the label:

foundCell.Offset(0, 2).Value = extractedDate
foundCell.Offset(0, 2).NumberFormat = "yyyy-mm-dd"

These lines assume foundCell is the matching label cell and extractedDate has already been assigned after a successful date check. The first line writes a date value, not a preformatted text string; the second controls how Excel displays it. Change the offset if your output belongs somewhere else. If you want to preserve an existing destination value, add a check before writing rather than overwriting it automatically.

Common problems and how to fix them

  • The macro says it cannot find the text. Confirm the active worksheet, spelling, spaces, and whether the cell contains extra text. Use xlPart only if a partial match is appropriate.
  • The wrong occurrence is found. A label may appear more than once. Narrow the search to a specific column or range, or add logic to identify the correct row. A worksheet-wide search returns one match, not every matching label.
  • The adjacent cell looks like a date but fails the check. It may contain an unusual text format, a leading apostrophe, or an error value. Inspect the cell’s actual value and correct or explicitly parse the source data.
  • The displayed date changes by computer. Text dates can be interpreted according to regional settings. Use true Excel dates, or parse a known text format with an explicitly defined order.
  • The label is in the last worksheet column. There is no cell to its right. The example checks for this case before using Offset.
  • The date is on the next row, not the same row. Change the offset to Offset(1, 0) if that is the actual layout. An offset is relative to the found label, so the row and column directions matter.

Choose the approach that fits the sheet

Use a worksheet-wide search when labels move but are unique. Use a restricted range or column when duplicate labels are possible. Use a direct cell reference when the layout is fixed. If the date is embedded in the same text string as the label, first define the exact text pattern and parse that string; searching for a neighboring cell will not extract part of the current cell’s contents.

For repeated reporting, consider making the data layout consistent: one field per cell, a clear header row, and dates stored as date values. That structure is easier to validate and maintain than a mixed cell containing labels and dates. If the task only needs a visible result and no automation, selecting the adjacent cell or using a worksheet formula may be simpler than enabling macros. VBA is most useful when the search and follow-up action need to run repeatedly or as part of a larger procedure.

Frequently Asked Questions

How do I get the cell to the right of a found string in VBA?
Use foundCell.Offset(0, 1), where foundCell is the range returned by Find. The zero keeps the same row; one moves one column right.

Does Find match part of a cell’s text?
It does when you set LookAt:=xlPart. Use xlWhole for an exact cell match. Partial matches are convenient but can select a different label that contains the same words.

Can VBA read a date stored as text?
Often, if VBA recognizes the text as a date, IsDate and CDate can validate and convert it. Ambiguous text dates depend on regional settings, so storing a real Excel date or parsing a known format is safer.

How do I get the date if it is in the same cell as the string?
Offset reads another cell; it does not extract a substring. You need to parse the cell’s text according to its structure. The right method depends on whether the date always follows a fixed label and uses a consistent format.

Will this VBA macro run in Excel for the web?
No. Excel for the web does not run VBA macros. Run the macro in desktop Excel for Windows or Mac, and keep the workbook in a macro-enabled format if you need to retain its VBA code.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.