How to Extract Text After a Keyword and Line Break in Access Query
Question details
The user needs to extract a specific identifier located after a specific keyword (such as "NC MEDIA - FILE:") and before the next line break in a Microsoft Access text field, while also removing any optional curly braces.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Querying structured text fields within a database where data values have prefixes and formatting inconsistencies (e.g., some have braces like {12-1}, others just 8-1).
- Observed behavior
- The standard string extraction methods are cumbersome, and the user requires a reliable way to isolate the identifier, strip out the curly braces, and return only the clean value in an Access query.
Verify the exact spelling and spacing of your target keyword in your Access tables, and ensure you have enabled macros or trusted locations so that custom VBA functions can execute.
Use a Custom VBA Helper Function
Create a reusable VBA module that locates the keyword, slices the string up to the line break, and cleans up any unwanted brace characters.
While Access provides built-in functions like InStr and Mid, dealing with variable line breaks and conditional characters (like curly braces) is best handled by a custom VBA function. This approach ensures your queries remain clean and easy to read.
Open your Microsoft Access database. Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.
In the top menu, click on 'Insert' and select 'Module'. This will create a new blank window for your custom code.
Copy and paste the following code into the module window: Public Function GetNcMedia(ByVal Text As String) As String Const Key As String = "NC MEDIA - FILE: " Dim Value As String On Error Resume Next ' Prevents errors if keyword is missing Value = Split(Split(Text, Key)(1), vbCrLf)(0) GetNcMedia = Replace(Replace(Trim(Value), "{", ""), "}", "") End Function
Save the VBA module and close the editor. Open your Access Query in Design View, select an empty column, and use your new function like this: ExtractedIdentifier: GetNcMedia([YourFieldName]). Run the query to see the cleaned identifiers.

Manage Data Easily with WPS Office Spreadsheets
If you are processing large amounts of text data and find Access VBA coding complicated, consider exporting your data and using WPS Office Spreadsheet. It provides powerful, intuitive text formulas to extract and clean data without writing a single line of code.
- 1. Download and Install: Get WPS Office from the official website and install the suite on your computer.
- 2. Import Your Data: Export your Access table to an Excel or CSV file, and open it instantly in WPS Spreadsheet.
- 3. Extract with Formulas: Use powerful string manipulation formulas directly in your spreadsheet cells to extract text after keywords and remove special characters.

Frequently Asked Questions
Can I extract text after a keyword without using VBA?
Yes, but it requires heavily nested expressions in the query design using Mid(), InStr(), and Replace(). Using VBA is generally recommended because it keeps the query expression clean and performs faster on complex text parsing.
What happens if the keyword is not found in the field?
If using the Split method without error handling, Access will throw a 'Subscript out of range' error. To prevent this, include 'On Error Resume Next' in your VBA block, or wrap the extraction logic inside a check like 'If InStr(Text, Key) > 0 Then'.
How do I handle different line break formats?
The VBA constant 'vbCrLf' represents a standard Windows line break (Carriage Return + Line Feed). If your data is imported from a different system (like Linux or older Mac systems), you may need to use 'vbLf' or 'vbCr' in your Split function instead.




