Increase / Decrease decimals for group of cells with different formatting w/o changing the formatting

Question

How do I increase / decrease decimals for a given group of cells - that contain various number formats - without changing the formats of those cells other than decimal place?

For example, I have three cells:  1) 50.0, 2) $50.0 and 3) 50.0%.  If I highlight these three cells and click the increase decimal button, the cells output to 50.00, 50.00 and 50.00 in which the last two cells replicate the format of the first cell and lose there original formatting.  Is there an efficient way to do this where output is 50.00, $50.00 and 50.00%...keeps the increase/decrease decimal button functionality but also keeps existing formats for cells? 

It seems pretty silly that these command buttons do not take this - cells with different formats - into consideration.  If a macro is required, does anyone know how to write one that will do this?

Thanks in advance.

Answer 1

Thanks for the macro.  However, it doesn't capture the full functionality I was looking for re not changing other formatting across selected cells. 

That being said, I found a website that has a free download of my desired macros - macabacuslite.  It has the functionality I was looking for re decimals.  You can hit Ctrl+, and Ctrl+. to toggle decimals for any cell without affecting format. 

I would still like to replicate the VBA code for the toggle decimal functionality of macabacuslite somehow but that is beyond my ability.

Maybe Microsoft will add this functionality into Excel at some point...

Answer 2

Another case of Excel trying to be too smart?  And not doing so well at it...

Here's a macro that might work for you.  The tough one is the currency setup because it doesn't look to see how it actually is at the moment (negative numbers specifically), and just uses a basic currency setup with the number of decimals you specify.

I don't make any promises - it may turn out you don't like it at all, especially since you have to call it into action somehow.  But here goes:

Sub SetDecimals()

  Dim numDecimals As Integer

  Dim anyCell As Range

  Dim testText As String

  numDecimals = Application.InputBox("Enter number of decimals to display:", _

   "Set Decimals", 2, , , , , 1)

  Application.ScreenUpdating = False

  For Each anyCell In Selection

    testText = anyCell.Text

    If InStr(testText, "$") > 0 Then

      'looks like currency, set it up

      anyCell.NumberFormat = _

      "_($* #,##0." & String(numDecimals, "0") & "_);_($* (#,##0." & _

       String(numDecimals, "0") & ");_($* ""-""??_);_(@_)"

    End If

    If InStr(testText, "%") > 0 Then

      'looks like percentage, set it up

      anyCell.NumberFormat = "0." & String(numDecimals, "0") & "%"

    End If

    If InStr(testText, "$") = 0 And InStr(testText, "%") = 0 Then

      'looks like a plain number, set that up

      anyCell.NumberFormat = "0." & String(numDecimals, "0")

    End If

  Next

End Sub