How to Split Multiline Access Field Values into Separate Fields
Question details
The user needs to separate multiline text data stored in a single Microsoft Access field into individual fields or records for better database architecture.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Correcting a poorly designed database where several discrete values are lumped into one multiline field, preventing efficient relational database usage.
- Observed behavior
- Multiple distinct values are separated by line breaks within a single text field, making the database difficult to query, link, and maintain.
Identify the exact line-break delimiter used in your multiline fields (such as vbCrLf) and design a new, properly normalized related table structure to receive the parsed data.
Normalize Data Using VBA Split Function
Parse existing multiline text data using VBA and migrate it into a properly structured relational table.
Storing multiple values in a single field violates relational database principles. To correct this, you must split the existing text and move the discrete values into a new related table instead of relying on complex workaround queries.
Navigate to the Create tab in Access and build a new table with the necessary fields to hold the individual values, ensuring it contains a foreign key to link back to your main table.
Press ALT + F11 to launch the VBA editor, insert a new module, and prepare to write a custom parsing script.
Use the Split function in VBA (e.g., Split(YourField, vbCrLf)) to separate the multiline field into a data array based on the line break delimiter.
Write a loop in your VBA script to iterate through the array items and execute a SQL INSERT statement or use a Recordset to write each separated value as a new row in your new related table.
Open Database Tools, click Relationships, and drag the primary key from your original table to the foreign key in your new related table to enforce referential integrity.

Boost Your Productivity with WPS Office
While Microsoft Access requires complex database normalization and VBA coding, handling everyday data sets in spreadsheets can often be simpler. WPS Office offers a highly compatible, lightweight, and free suite for your spreadsheets, documents, and presentations.
- 1. Download the software: Visit the official WPS Office website and click the free download button.
- 2. Install WPS Office: Run the installer and follow the on-screen prompts to set up the software on your computer.
- 3. Open your data files: Launch WPS Spreadsheet and open your exported CSV or Excel data files to manage them effortlessly.

Frequently Asked Questions
Why shouldn't I keep multiple values in one Access field?
Keeping multiple values in one field breaks the rules of database normalization. It makes querying, sorting, and reporting on the data extremely difficult, error-prone, and inefficient.
What is the delimiter for a new line in Access VBA?
In Access VBA, a standard carriage return and line feed are typically represented by the constant vbCrLf, which you can use in functions like Split() or Replace().
Can I split multiline fields using only an Access query?
While you can use string functions like InStr and Mid in complex queries, it is often messy and limited. Using a VBA custom function with the Split method is much more reliable and easier to maintain for transferring multiline data to a new table.
How do I properly link the new separated tables?
Open the Relationships window under the Database Tools tab, add your primary and new related tables, and drag the primary key field to the matching foreign key field to establish a proper one-to-many relationship.




