Others

How to Display Line Breaks in Access Text Imported from Excel

Huda Qurayshi Oct 7, 2026 869 views

Question details

The user needs to display line breaks correctly in Microsoft Access for text data imported from Excel.

How to Display Line Breaks in Access Text Imported from Excel

Product: Microsoft Access and Excel
Device & OS: not provided
Scenario: Importing multiline text data from an Excel spreadsheet into a Microsoft Access database.
Observed behavior: Text imported from Excel appears as one continuous string in Access because Excel uses only line-feed characters, whereas Access requires carriage-return and line-feed pairs to format new lines.

Before you start

Back up your Microsoft Access database table before running any update queries or VBA scripts to prevent accidental data loss while modifying existing records.

Solution 1Recommended

Run an Update Query to Replace Line Characters

Use an Access update query to replace Excel's line-feed characters with the carriage-return and line-feed pairs required by Access.

Excel uses a single Line Feed (LF or Chr(10)) for new lines within cells. Access text boxes require a Carriage Return followed by a Line Feed (CR+LF, or Chr(13) & Chr(10)). Replacing these characters updates the existing records so they display correctly.

Open Query Design

Open your Microsoft Access database, navigate to the 'Create' tab on the ribbon, and click 'Query Design'.

Switch to SQL View

Close the Show Table dialog box. Right-click the query tab at the top and select 'SQL View'.

Enter the UPDATE Statement

Type the following SQL command, replacing 'TableName' and 'FieldName' with your actual table and field names: UPDATE TableName SET FieldName = Replace([FieldName], Chr(10), Chr(13) & Chr(10));

Run the Query

Click the 'Run' button (the red exclamation mark) on the Query Design ribbon. Confirm the prompt to update the rows.

Run an Update Query to Replace Line Characters

Data Format Updated: Once the query is executed, all imported text fields will automatically reflect the correct multiline formatting in your tables and forms.

Solution 2

Configure Text Box Properties in Access Forms

Ensure the specific text box on your Access form is configured to allow and display multiple lines.

Free Microsoft Office alternative

Manage Spreadsheet Data Seamlessly with WPS Office

While Microsoft Access is a dedicated database tool, preparing complex multiline text data for import is much easier with WPS Spreadsheet. WPS Office is a free, lightweight suite that offers robust compatibility with Microsoft Office formats, allowing you to clean and format your data effortlessly.

1. Open Data in WPS Spreadsheet: Launch WPS Spreadsheet and open your .xlsx or .csv data file.
2. Format Line Breaks: Use Alt + Enter to insert proper line breaks inside your cells while editing data.
3. Save and Export: Save your meticulously formatted file in a universally compatible format for a smoother database import.

Free and lightweight office suite for everyday tasksHighly compatible with Microsoft Excel (.xlsx) formatsEasily manage multiline text and line breaks before database importsFamiliar user interface for seamless and quick migration

microsoft office alternative - wps office

Frequently Asked Questions

Why do line breaks from Excel disappear when imported into Access?

Excel uses a single Line Feed character (Chr(10)) to represent a new line within a cell. Microsoft Access requires a Carriage Return and Line Feed combination (Chr(13) & Chr(10)). Because the Carriage Return is missing, Access displays the text as a continuous string.

Can I use VBA to fix the line breaks instead of an update query?

Yes, you can write a VBA procedure using the Replace function. You would open a Recordset, loop through the records, and use Replace(rs!FieldName, Chr(10), Chr(13) & Chr(10)) to update the data programmatically.

How do I type a manual line break in an Access text box?

When typing directly into a text box on an Access form, you can insert a manual line break by pressing Ctrl + Enter, provided the text box's 'Enter Key Behavior' property is set to its default.

Office Quick Solutions