VBA & Macro Problems

How to Insert a Decimal Point After Three Characters in Excel

Partner Editor Sep 28, 2026 868 views

Question details

The user needs a method to automatically insert a decimal point exactly after the first three characters across multiple cells containing alphanumeric codes.

How to Insert a Decimal Point After Three Characters in Excel

Product: Excel
Device & OS: not provided
Scenario: Formatting a large dataset of text strings or alphanumeric codes by splitting the text and inserting a specific character (a period) at a fixed position.
Observed behavior: The current dataset lacks the required decimal formatting, requiring either a helper column formula or a VBA script to manipulate the strings without doing it manually.

Before you start

Ensure your alphanumeric data is stored in a single column without trailing spaces, and decide whether you prefer creating a new column with formulas or overwriting the original data directly using a macro.

Solution 1Recommended

Use an Excel Formula to Insert a Decimal Point

Using a combination of LEFT, MID, and LEN functions in a helper column is the safest and most common way to insert a decimal point into existing text.

This method creates a modified copy of your original data in a new column. It extracts the first three characters, appends a decimal point, and then attaches the remaining characters.

Select a blank adjacent cell

Click on a blank cell right next to the first cell of your data (for example, click B1 if your alphanumeric code is in A1).

Enter the text manipulation formula

Type the formula =LEFT(A1,3)&"."&MID(A1,4,LEN(A1)-3) into the formula bar and press Enter.

Apply the formula to the rest of the column

Click the cell with the formula, then click and drag the small green square (fill handle) in the bottom-right corner down the column to apply the decimal insertion to all your data.

Use an Excel Formula to Insert a Decimal Point

Convert Formula to Text: Once your data is formatted, you can copy the helper column and use 'Paste as Values' to remove the formula and keep only the formatted text.

Solution 2

Use a VBA Macro for In-Place Modification

If you need to process hundreds of codes directly without creating a helper column, a custom VBA macro can insert the decimal point in the selected range automatically.

Efficient Text Manipulation in WPS Spreadsheet

Easily Format Text and Codes with WPS Office

WPS Spreadsheet is a powerful and free tool that seamlessly handles advanced formulas, text manipulation functions, and VBA macros exactly like Microsoft Excel, making data processing effortless.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and locate the column containing your alphanumeric codes.
2. Enter the string formula: In the adjacent blank column, input =LEFT(A1,3)&"."&MID(A1,4,LEN(A1)-3) and press Enter.
3. Drag to fill the entire column: Double-click or drag the fill handle on the cell's bottom-right corner to instantly insert decimals across all rows.

Full compatibility with Microsoft Excel (.xlsx) file formats.Supports all standard string manipulation functions like LEFT, MID, and LEN.Comprehensive VBA macro support for automating repetitive data tasks.Free, lightweight, and features an intuitive tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I insert a character other than a decimal point using this formula?

Yes. In the formula =LEFT(A1,3)&"."&MID(A1,4,LEN(A1)-3), simply replace the period "." with any other character enclosed in quotes, such as a hyphen "-" or a space " ".

What happens if a cell has fewer than three characters?

If you use the formula, it will simply return the original short string and attach the period at the end (e.g., 'AB' becomes 'AB.'). The VBA macro specifically includes a condition (If Mid(rng.Value,4,1)<>"") to skip cells that don't have enough characters.

How do I remove the formulas and keep only the formatted text?

Highlight the column containing your new formula results, press Ctrl+C to copy them, then right-click the same selection and choose 'Paste as Values' (usually represented by an icon with '123'). This replaces the active formulas with static text.

Excel & Data Problems