How to Extract Text After an Order ID in Access VBA
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.

- 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.
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.
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.
Press ALT + F11 within your Microsoft Access database to open the Visual Basic for Applications editor.
Set up your variables to store the position and the extracted text. For example: Dim numStartPoint As Integer and Dim strOrderID As String.
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).
Use the Mid function to pull the characters. Example: strOrderID = Mid([Contents], numStartPoint, 10) to extract the next 10 characters.
Assign the extracted string to a text box or another variable. Example: Text10 = strOrderID.

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. Download the Installer: Visit the official WPS Office website and download the free software package.
- 2. Install WPS Office: Run the installer. The lightweight design ensures setup is completed in just a few minutes.
- 3. Run Your Macros: Open your existing Excel files in WPS Spreadsheet and seamlessly continue editing and executing your VBA macros.

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).




