Others

How to Insert a Period After the Third Character in Microsoft Access

Natalie Taylor Oct 1, 2026 869 views

Question details

The user needs to insert a period (.) exactly after the third character of a string field without overwriting or replacing any of the existing characters.

How to Insert a Period After the Third Character in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Formatting or modifying string data within a calculated query column.
Observed behavior: Attempting to use the Mid statement alone replaces characters instead of inserting new ones, requiring a combination of string manipulation functions to achieve the desired formatting.

Before you start

Ensure you have your Microsoft Access database open and identify the exact query and field name where you want to apply this text formatting.

Solution 1Recommended

Use Left and Mid Functions in a Calculated Column

By combining the Left and Mid functions with an ampersand (&) operator, you can seamlessly concatenate the first three characters, a period, and the remainder of the string.

The Mid function is traditionally used to extract or replace characters. To insert a character without deleting existing text, you must split the string into two parts and join them together with the new character in the middle.

When using the Mid function, the third argument (length) is optional. Omitting it automatically returns all characters from the starting position to the very end of the string.

Open Query Design

Open your Microsoft Access database, navigate to the Queries section, and open your target query in Design View.

Create a Calculated Column

Click into an empty 'Field' cell in the query design grid where you want the newly formatted text to appear.

Enter the Concatenation Expression

Type the following expression: FormattedCode: Left([YourField],3) & "." & Mid([YourField],4). Replace 'YourField' with the actual name of your column.

Run the Query

Click the 'Run' button (the red exclamation mark) in the Design ribbon to execute the query. Your data (e.g., 'M234567A') will now display with the inserted period (e.g., 'M23.4567A').

Use Left and Mid Functions in a Calculated Column

Optional Length Argument: Because Mid([YourField],4) omits the length argument, Access perfectly handles variable-length strings by returning everything from the 4th character onward.

Free Microsoft Office alternative

Need a Lightweight Office Suite? Try WPS Office

While Microsoft Access is built for complex relational databases, you can easily manage, format, and manipulate structured data lists using WPS Spreadsheet. WPS Office provides a lightweight, entirely free, and highly compatible alternative for your everyday document and data management needs.

1. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
2. Install the Software: Run the setup file and follow the on-screen prompts to complete the installation in seconds.
3. Open and Edit Data: Launch WPS Spreadsheet to open your exported database files (like CSV or Excel formats) and easily apply text manipulation formulas.

Fully compatible with Microsoft Excel, Word, and PowerPoint formats.Easily manipulate text strings and manage datasets using advanced formulas in WPS Spreadsheet.Free to download with a familiar, easy-to-use tabbed interface.Lightweight installation that runs smoothly on older devices.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the Mid statement insert characters on its own?

The Mid function is designed to extract a substring or replace existing characters when used in VBA. It does not have built-in functionality to push characters to the right (insert). To insert a new character, you must concatenate the split parts of your string using the '&' operator.

Can I insert a different character instead of a period?

Yes. You can replace the period (".") in the expression with any character or string you need. For example, to insert a hyphen, use Left([Field],3) & "-" & Mid([Field],4).

What happens if the original string is less than three characters long?

If the string has fewer than three characters, the Left function will return the entire string, the period will be appended to the end of it, and the Mid function will return a null string since there is no 4th character.

Does this method alter the original data in my Access table?

No. Creating a calculated field in an Access query only changes how the data is displayed within that specific query. Your original underlying table data remains completely untouched and intact.

Office Quick Solutions