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

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.


Download Excel Workbook


3 Easy Ways to Insert Dot between Numbers in Excel

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.

Read More: How to Reduce Decimals in Excel (10 Easy Methods)


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 functions at last 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

Read More: How to Fix Decimal Places in Excel (7 Simple Ways)


Similar Readings


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.

Read More: How to Set Decimal Places in Excel with Formula (5 Effective Ways)


🔄 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


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 Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo