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.
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).
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.
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)
The 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.
- How to Convert Decimal to Days Hours and Minutes in Excel (3 Methods)
- Remove Decimal Places in Excel (5 Easy Methods)
- How to Convert Hours to Decimal in Excel (3 Easy Methods)
- Convert Decimal to Minutes and Seconds in Excel (3 Easy Ways)
- How to Remove Decimals in Excel with Rounding (10 Easy Methods)
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.
Step 1: Type the below formula in cell E5.
Step 2: Use the ENTER key to execute the formula and drag the Fill Handle to apply the formula to other cells.
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.
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.
🔺 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.
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.
- How to Remove Decimals Without Rounding in Excel (4 Suitable Ways)
- Converting Time to Decimals in Excel (4 Examples)
- How to Convert Minutes to Decimal in Excel (3 Quick Ways)
- Excel 2 Decimal Places without Rounding (4 Efficient Ways)
- How to Change Decimals to Percentages in Excel (4 Easy Ways)
- Convert Hours and Minutes to Decimal in Excel (2 Cases)