Adding decimal places to a cell 1

[Chrissirhc]

Hi,

In Excel there is a button to add or remove decimal places from a number in a cell. Is there a keyboard shortcut for this?

Thanks,

Chris

 

[xlbo]

There is no real shortcut for this - best bet might be to record yourself adding a decimal and taking one off and then assigning a shortcut key to the macro

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Surely this is on the Formatting toolbar ... two of the standard ones? Increase decimal and decrease decimal are after the Currency/Percent/Comma style buttons.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Chrissirhc]

I was thinking I may have to use macro approach...

Glenn, They are on the formatting bar, however that requires me to use the mouse.

Thanks for your help.

Chris

 

[rmikesmith]

Chris,

Here are procedures that will mimic the decimal digits increment/decrement buttons on the formatting toolbar, followed by test procedures that call the respective routines:

Sub IncrementDecimalDigits(ByRef oCell As Range)
Dim DecimalPos As Integer
Dim DecimalDigits As Integer
Dim Length As Integer
Dim NumFormat As String

   If Not IsNumeric(oCell.Value) Then Exit Sub
   
   DecimalPos = InStr(1, oCell.NumberFormat, ".", vbTextCompare)
   If DecimalPos > 0 Then
     Length = Len(oCell.NumberFormat)
     DecimalDigits = Length - DecimalPos
     NumFormat = Left$(oCell.NumberFormat, DecimalPos) & String(DecimalDigits + 1, "0")
     oCell.NumberFormat = NumFormat
   Else
     oCell.NumberFormat = "0.0"
   End If
   
End Sub


Sub DecrementDecimalDigits(ByRef oCell As Range)
Dim DecimalPos As Integer
Dim DecimalDigits As Integer
Dim Length As Integer
Dim NumFormat As String

   If Not IsNumeric(oCell.Value) Then Exit Sub
   
   DecimalPos = InStr(1, oCell.NumberFormat, ".", vbTextCompare)
   If DecimalPos > 0 Then
     Length = Len(oCell.NumberFormat)
     DecimalDigits = Length - DecimalPos
     If DecimalDigits > 1 Then
       NumFormat = Left$(oCell.NumberFormat, Length - 1)
     Else
       NumFormat = Left$(oCell.NumberFormat, Length - 2)
     End If
     oCell.NumberFormat = NumFormat
   End If
   
End Sub

Sub TestIncrement()
   IncrementDecimalDigits ActiveCell
End Sub

Sub TestDecrement()
   DecrementDecimalDigits ActiveCell
End Sub

For testing purposes, I assigned [tt]Ctrl-i[/tt] to the TestIncrement procedure and [tt]Ctrl-d[/tt] to the TestDecrement procedure. If you intend to use these as keystroke macros you might want to change the main subs to reference the ActiveCell and call them directly.

Hopefully, this helps.

Mike

 

[rmikesmith]

D'oh

Sometimes the forest gets in the way of the trees! Here is a simpler way to exactly "mimic" the use of the toobar buttons for incrementing/decrementing the displayed decimal digits:

Sub IncrementDecimalDigits()
   On Error Resume Next
   Application.CommandBars.FindControl(ID:=398).Execute
End Sub


Sub DecrementDecimalDigits()
   On Error Resume Next
   Application.CommandBars.FindControl(ID:=399).Execute
End Sub

Regards,
Mike

 

[xlbo]

[LOL] - bet you were really pleased with the 1st one eh !! Must say I'm a little surprised that there isn't an increase / decrease decimal method somewhere but hey ho - nice bit of thinking - star for you

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[rmikesmith]

Thanks, Geoff

bet you were really pleased with the 1st one eh !!

You know it! Then along comes the smuggery¹ smackdown.


¹Don't try to look this up in your OED


Regards,
Mike