Others

How to Display Formatted Phone Numbers in Microsoft Access Forms and Queries

Maira Mehtab Oct 1, 2026 869 views

Question details

The user needs to display phone numbers imported from Excel with correct punctuation (parentheses and hyphens) in Microsoft Access forms and queries, despite the field being stored as text.

How to Format Phone Numbers in Microsoft Access Forms and Queries

Product: Microsoft Access
Device & OS: not provided
Scenario: Displaying raw digit phone numbers imported from Excel with standard formatting in Access forms and query results.
Observed behavior: Phone numbers appear as continuous digits without formatting in forms and queries, even if an input mask is applied at the table level.

Before you start

Identify the exact field name storing your phone numbers in your database table, as you will need to reference it precisely when writing query expressions.

Solution 1Recommended

Apply a Display Format to the Form Control

Format how the phone number appears visually on the form without altering the underlying text data.

Applying a custom format to the form control is the easiest way to ensure numbers display nicely for users without having to modify the raw table data or build complex queries.

Open Form in Design View

Right-click your form in the navigation pane and select 'Design View'.

Select the Phone Number Control

Click on the text box control that is bound to your phone number field.

Open the Property Sheet

Press F4 to open the Property Sheet on the right side of the screen, and navigate to the 'Format' tab.

Enter the Custom Format

In the Format property box, type the formatting string: \(@@@\) @@@\-@@@@ and save the form.

Apply a Display Format to the Form Control

Tip: This format displays the data as (123) 456-7890 when viewing the form, but allows editing of the raw text when the user clicks into the field.

Solution 2

Use String Manipulation Expressions in Queries

Create a calculated field in an Access query to insert parentheses and hyphens dynamically into the text string.

Free Microsoft Office alternative

Manage and Pre-format Your Spreadsheet Data with WPS Office

While Microsoft Access is a dedicated database tool, much of your data preparation—like formatting phone numbers before importing—can be seamlessly handled in a spreadsheet. WPS Office is a free, lightweight, and highly compatible alternative to Microsoft Office.

1. Visit the Official Website: Navigate to the WPS Office website to download the free installation package for your operating system.
2. Install WPS Office: Run the downloaded installer and follow the on-screen instructions to complete the setup.
3. Prepare Your Data: Open WPS Spreadsheets to load your Excel or CSV files, apply custom number formats to your phone columns, and save them ready for database import.

Easily format phone numbers in spreadsheets before importing into databases.Fully compatible with Microsoft Excel (.xlsx, .xls) and CSV file formats.Free, lightweight, and loads quickly on PC, Mac, and mobile.Familiar user interface ensuring a seamless migration with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the table's input mask show up in my Access query?

An input mask primarily governs data entry and default table display. Queries and forms pull the underlying raw data, so they often require their own display formatting or calculated expressions to present the text with punctuation.

What if some phone numbers are shorter or longer than 10 digits?

The basic Left, Mid, and Right string expressions assume a strict 10-digit number. For variable lengths (such as international numbers or those with extensions), you will need to write a custom VBA function or use conditional statements (IIf) in your query to handle different string lengths appropriately.

Can I format phone numbers directly in Excel before importing them?

Yes. In your spreadsheet application, you can highlight the column, open cell formatting, and apply a Custom Number Format (such as [<=9999999]###-####;(###) ###-####). Note that importing formatted data into Access may still transfer only raw values unless the data is saved specifically as formatted text.

Office Quick Solutions