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
xlPartonly 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
- Excel Text to Columns: How to Undo and Restore Data
- Excel Pasting: Stop Tab-Delimited Text Splitting on Paste
- Excel Unable to Merge Cells? Causes, Fixes, and Alternatives
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA AutoFilter Off: Clear Filters or Remove ArrowsTo turn off a standard worksheet AutoFilter in Excel VBA, first clear any active filter so the filtered-out…
- VBA AutoFilter for Tanaka: Filter Excel Rows by NameTo show only rows whose name is Tanaka, apply Excel’s AutoFilter to a range that includes its header…
- Excel VBA Find a Value in an Array: Loop, Match, and TipsTo find a value in a VBA array, loop from LBound to UBound and compare each element with…
- VBA Resize UserForm: Set Size and Enable ResizingTo resize an Excel VBA UserForm, set its Width and Height properties in the form’s Properties window or…
- Keyboard Macro Recorder Free? Tools, Limits and SafetyYes—free keyboard macro recorders are available, especially for Windows. Some capture keystrokes and mouse actions for replay, while…
- View Macros in Google Sheets: Find and Read the CodeTo view a macro in Google Sheets, open the spreadsheet in a web browser and choose Extensions >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.