# Shifting Number of Decimal Places

#### HurricaneBob

##### New Member
I'm trying to create a macro that can read the format of the current cell, determine the number of decimal places, and then add one more but I want it do be dynamic:

For example, if the current format is #,##0 , it will change the format to #,##0.0, and if the current format is #,##0.000 it will change to #,##0.0000

The tricky part is that I want to maintain the current cell format, so it would work with number, currency, percent, etc.

Appreciate any help!

#### Marc L

##### Excel Ninja
As it depends on dynamic (often it means everything and nothing so better to avoid it) cells formats you use​
so without several samples it's difficult to help in another case than your unique sample. Obviously with an attachment …​
Easy way just adding a zero after the decimal separator via Replace or starting wih Split or …​
And do not forget to post the code you have started …​

#### Marc L

##### Excel Ninja
A kid level logic :​
Code:
``````Sub Demo00()
V = "#,##0"
MsgBox V & IIf(InStr(V, "."), "", ".") & "0"
End Sub``````
Do you like it ? So thanks to click on bottom right Like !​

#### HurricaneBob

##### New Member
Thanks! I'll give that a try.

#### HurricaneBob

##### New Member
Thank you! I got it to work with the following code:

Code:
``````Sub Add_Zeros()
MyFormat = Selection.NumberFormat
NewFormat = MyFormat & IIf(InStr(MyFormat, "."), "", ".") & "0"
Selection.NumberFormat = NewFormat
End Sub``````

#### HurricaneBob

##### New Member
Thank you! I got it to work with the following code:

Code:
``````Sub Add_Zeros()
MyFormat = Selection.NumberFormat
NewFormat = MyFormat & IIf(InStr(MyFormat, "."), "", ".") & "0"
Selection.NumberFormat = NewFormat
End Sub``````
That only works when there's a value for a positive format - if there's a negative format, it only modifies the negative format.

#### Marc L

##### Excel Ninja
That's the reason why of post #2 first sentence : difficult w/o samples …​

#### HurricaneBob

##### New Member
Understood. I'll be sure to include examples next time.

Last edited by a moderator:

#### Marc L

##### Excel Ninja
A positive / negative decimal only number format demonstration :​
Code:
``````Sub Demo0()
D\$ = Application.DecimalSeparator
ActiveCell.NumberFormatLocal = Replace(ActiveCell.NumberFormatLocal, D & "0", D & "00")
End Sub``````
You may Like it !​

#### Marc L

##### Excel Ninja
According to the initial post it could be the one :​
Code:
``````Sub Demo1()
Const S = "#0"
D\$ = Application.DecimalSeparator
ActiveCell.NumberFormatLocal = Replace(ActiveCell.NumberFormatLocal, S & IIf(InStr(ActiveCell.NumberFormatLocal, D), D, ""), S & D & "0")
End Sub``````
You could Like it !​

#### HurricaneBob

##### New Member
That's awesome! Is there a way to take it a step further and make it apply to a range of cells or would that require different logic?

#### Marc L

##### Excel Ninja
The demonstration revamped :​
Code:
``````Sub Demo1r()
Const S = "#0"
Dim D\$, Rg As Range
D = Application.DecimalSeparator
For Each Rg In Selection
Rg.NumberFormatLocal = Replace(Rg.NumberFormatLocal, S & IIf(InStr(Rg.NumberFormatLocal, D), D, ""), S & D & "0")
Next
End Sub``````
You should Like it !​

#### HurricaneBob

##### New Member
This is amazing! How did you learn to do all of this?

If I wanted to create another macro to go the other way (remove a 0), is there a function that is essentially the inverse of '& "0"'?

Last edited by a moderator:

#### Marc L

##### Excel Ninja
Thanks to not quote all the previous post, that just clutter the thread.​
I learn from using the Macro Recorder and reading the VBA help (hit F1 key !) …​
You should have found yourself the remove way as the logic is easier …​
Anyway a VBA demonstration removing a decimal if the format has more than one :​
Code:
``````Sub Demo2()
Dim R\$, F\$, Rg As Range
R = "0" & Application.DecimalSeparator & "0":  F = R & "0"
For Each Rg In Selection
Rg.NumberFormatLocal = Replace(Rg.NumberFormatLocal, F, R)
Next
End Sub``````
You may Like it !