How to Insert a Dot between Numbers in Excel – 3 Methods

In the sample dataset, you have the Vehicle Engine Chassis Number, and want to insert dots.

Method 1 – Using the REPLACE Function to Insert a Dot between Numbers

The REPLACE function replaces characters with new_text.

Step 1: Insert the following formula in any cell (here, D5).

`=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C5,2,0,"."),7,0,"."),10,0,"."),12,0,"."),14,0,"."),16,0,".")`

The formula takes theÂ REPLACE functionâ€™s arguments ( old_text, start_num, num_chars, new_text). The multiple brackets insert dots in specific location.

Step 2: Press ENTER and drag the Fill Handle.

Method 2 – Inserting Dots between Numbers Using Multiple Formulas

A combination of the LEFT, MID, and RIGHT functions along with the Ampersand (&) can also insert dots. .

Step 1: Enter the following formula in D5.

`=LEFT(C5,1) & "." & MID(C5,2,4) & "." & MID(C5,6,2) & "." &MID(C5,8,1) & "."& MID(C5,9,1) & "."& MID(C5,10,1) & "." & RIGHT(C5,7)`

The formula inserts the 1st dot using the LEFT function and the middle dots using the MID function. TheÂ  RIGHT function inserts the last dot.

Step 2: Press ENTER and drag the Fill Handle.

Method 3 – Using the CONCATENATE Function to Insert Dots within Numbers in Excel

You have multiple different numbers andÂ  want to create Unique Ids with dots.

Step 1: Enter the formula in E5.

`=CONCATENATE(B5,CHAR(46),C5,CHAR(46),D5)`

The CONCATENATE function takes multiple texts as arguments (iB5, CHAR (46),….) to concatenate them as a Unique Id. The CHAR function represents the ASCII code for the Dot character.

Step 2: Press ENTER key to apply the formula and drag the Fill Handle.

Using Cell Formatting to Insert a Trailing Dot

Step 1: Highlight the entire column. Go to the Home tab to and in Font, chooseÂ Font SettingsÂ or press CTRL+1, to open the Format Cells window.

Step 2: In the Format Cells window. Go to NumberÂ  > Select Custom (under Category) > In Type, enter #. ClickÂ OK.

The entire highlighted column has trailing dots.

Related Article

<< Go Back to Bullets and Numbering in Excel | Worksheet Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF