VBA & Macro Problems

How to Extract Text After an Order ID in Access VBA

Nimra MalikNimra Malik Nov 2, 2026 870 views

Question details

The user needs to locate a specific label (like 'Order ID') within a memo field that varies in line number and extract the substring immediately following it using VBA.

How to Extract Text After an Order ID in Access VBA
Product
Microsoft Access
Device & OS
not provided
Scenario
Parsing dynamic text strings in a database where the target data follows a known label but resides in unpredictable locations within the text block.
Observed behavior
The user wants to isolate and extract only the target text/number following the label without capturing the preceding text or the label itself.
Before you start

Ensure you have backed up your database and have basic familiarity with the VBA editor before executing new text-extraction scripts to prevent any unintended data modification.

Solution 1Recommended

Use InStr and Mid Functions to Extract the Target Substring

Combine the InStr function to pinpoint the starting location of your text and the Mid function to extract the desired characters from that exact point.

The InStr function scans the text block and returns the numerical starting position of your target label (e.g., 'ORDER ID'). By adding the length of that label plus any spaces to this position, you determine precisely where your actual data begins.

The Mid function takes over by extracting a specified number of characters starting directly from the position calculated by InStr, isolating only the data you need.

1
Open the VBA Editor

Press ALT + F11 within your Microsoft Access database to open the Visual Basic for Applications editor.

2
Declare the Variables

Set up your variables to store the position and the extracted text. For example: Dim numStartPoint As Integer and Dim strOrderID As String.

3
Find the Starting Position with InStr

Use the InStr function to locate the label and calculate the offset. Example: numStartPoint = InStr(1, [Contents], "ORDER ID") + 9 (adjust '9' depending on the exact character count of your label and following spaces).

4
Extract the Text with Mid

Use the Mid function to pull the characters. Example: strOrderID = Mid([Contents], numStartPoint, 10) to extract the next 10 characters.

5
Assign or Output the Result

Assign the extracted string to a text box or another variable. Example: Text10 = strOrderID.

Use InStr and Mid Functions to Extract the Target Substring
Omitting the Length Argument: If the length of your target text varies, you can omit the final length argument in the Mid function. This will automatically return the entire remainder of the text string starting from your defined point.
Free Microsoft Office alternative

Try WPS Office for Your Advanced VBA and Spreadsheet Needs

While WPS Office does not feature a database alternative to Microsoft Access, it is an exceptional, free solution for Excel, Word, and PowerPoint workflows. WPS Spreadsheet provides excellent VBA and macro support, empowering you to automate tasks seamlessly without expensive subscription fees.

  1. 1. Download the Installer: Visit the official WPS Office website and download the free software package.
  2. 2. Install WPS Office: Run the installer. The lightweight design ensures setup is completed in just a few minutes.
  3. 3. Run Your Macros: Open your existing Excel files in WPS Spreadsheet and seamlessly continue editing and executing your VBA macros.
Completely free, lightweight, and fast installation processHigh format compatibility with Microsoft Office files (.xlsx, .docx, .pptx)Robust VBA and Macro support in WPS Spreadsheet to run your automated scriptsFamiliar and intuitive user interface allowing for instant productivity
QA img-9

Frequently Asked Questions

What happens if the search string is not found in the text?

If the InStr function does not find the specified string within the field, it returns 0. It is highly recommended to wrap your Mid extraction in an IF statement (e.g., If numStartPoint > 0 Then) to prevent runtime errors.

How can I extract text up to the end of the line instead of a fixed character length?

You can use a second InStr function to search for the carriage return (vbCrLf) starting from your initial start point. By subtracting your start position from the carriage return position, you can calculate the exact dynamic length to pass into the Mid function.

Is the InStr function case-sensitive when searching for labels?

By default, InStr uses binary comparison, which is case-sensitive. To perform a case-insensitive search, include vbTextCompare as the fourth argument, for example: InStr(1, [Contents], "order id", vbTextCompare).