How to Insert Dot between Numbers in Excel (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, users need to insert a Dot between numbers or characters in Excel to display different parts of numbers or IDs. It’s common to insert dot between numbers in Excel to signify numbers for numerous reasons.

Let’s say we have Vehicle Engine Chassis Numbers, and we want to insert dots to show its different parts signifying different aspects.

Dataset-How to Insert Dot between Numbers in Excel

In this article, we demonstrate multiple functions such as REPLACE, LEFT, MID, RIGHT, and CONCATENATE as well as the Format Cells feature to insert dot between numbers in Excel.


How to Insert Dot between Numbers in Excel: 3 Easy Ways

Inserting dots within or between numbers is quite easy. Follow any of the below-described methods to insert dot between numbers.


Method 1: Using REPLACE Function to Insert Dot between Numbers

Excel the REPLACE function replaces characters (i.e., num_chars) with whatever is given as new_text. Users can use a simpler REPLACE function but, in this case, we slightly complicated the formula to accommodate a large number to place dots within.

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

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

Replace Function-How to Insert Dot between Numbers in Excel

The formula takes the typical REPLACE function’s arguments (i.e., old_text, start_num, num_chars, new_text). The multiple bracketed REPLACE formula is there to insert dots at desired times.

Step 2: Hit ENTER then drag the Fill Handle to insert dots in other numbers as depicted in the below image.

Fill Handle

You can change the arguments’ values to insert dots as you want to.


Method 2: Inserting Dots between Numbers Using Multiple Formulas

A combination of the LEFT, MID, and RIGHT functions along with the Ampersand (&) can insert dots at our desired positions or between numbers or characters.

Step 1: Paste the following formula in cell 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)

Formulas-How to Insert Dot between Numbers in ExcelThe formula inserts the 1st dot using the LEFT function portion and the middle dots get inserted by the MID function at last the the RIGHT function inserts the last dot. All the function has their typical arguments.

Step 2: Press ENTER afterward drag the Fill Handle to display dots between numbers as shown in the following picture.

Fill Handle


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

Suppose we have multiple different numbers, and we want to create Unique Ids by concatenating them. However, we also want to insert dots to symbolize the different portions of those Unique Ids. The data may look like the below depiction.

Dataset 2-How to Insert Dot between Numbers in Excel

Step 1: Type the below formula in cell E5.

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

Concatenate Function-How to Insert Dot between Numbers in Excel

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

Step 2: Use the ENTER key to execute the formula and drag the Fill Handle to apply the formula to other cells.

Dot inserted

Here, the CONCATENATE formula joins numbers and constitutes Unique Ids inserting dots between numbers.


🔄 Use of Cell Formatting to Insert Trailing Dot

Excel offers multiple features to deal with the number system. Format Cells feature is one of them. Users can create their Custom Number Type to achieve what they desire. However, Excel’s Format Cells feature only allows trailing dots after a number. Thus this method is not so effective. Let’s start the proceedings.

Step 1: Highlight the entire column. After that click on the Font Settings Icon in the Home tab Font section or press CTRL+1, to fetch the Format Cells window.

Format Cells-How to Insert Dot between Numbers in Excel

Step 2: Excel brings the Format Cells window. Go to Number section > Select Custom (under Category) > Type #. or select it if it previously exists. Click on OK.

Format Cells window

🔺 In a moment, you see the entire highlighted column gets trailing dots. Though this type of depiction we are not discussing, we describe the feature to display the limitations of the Format Cells feature in cases of inserting dots between numbers.

Outcome


Download Excel Workbook


Conclusion

In this article, we describe multiple formulas to insert dot between numbers in Excel. Hope you find your desired method among the discussed ones. Comment, if you have further inquiries or have anything to add.


Related Article


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo