On Fri, 13 Jul 2012 10:43:36 -0700 (PDT), N Lee wrote:

Greetings:

Not sure if there's a way to do it, but someone might have a good idea. I want to retrieve the number of visible decimals in a cell as an integer.

For instance, a cell has a value of 98.7654321, and displays as 98.8 because its NumberFormat is "#,##0.0_);(#,##0.0)". This should return '1'.

A cell with a value of 87.6543212 would display as 88 if the value is zero.

I'm NOT asking how to format a cell to show a certain number of decimals. I already know how to do that by setting the NumberFormat. I want to know how to retrieve as an integer the number of decimals that are being displayed.

Thanks,
Nathan

Here's one way. The Text property of the cell contains that which is being displayed. Using regular expressions returns only the digits that exist after the decimal in the displayed item. Since there can be non-digit characters returned by formatting, it is important to count only the digits, and not any other characters.

Also, although I did not do so, it would be trivial to make this function aware of non-dot decimal symbols. Let me know if you require this.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=NumDecDispl(cell_reference)

in some cell.

Note that if cell_reference refers to multiple cells, the function will return an array of the results.

============================================
Option Explicit
Function NumDecDispl(rg As Range) As Variant
Dim v() As Variant, c As Range
Dim re As Object
Const sPat As String = "^[^.]+\.(\d+).*"
Dim i As Long
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.Pattern = sPat
.MultiLine = True
End With

If rg.Count = 1 Then
NumDecDispl = Len(re.Replace(rg.Text, "$1"))
Else
ReDim v(0 To rg.Count - 1)
For Each c In rg
v(i) = Len(re.Replace(c.Text, "$1"))
i = i + 1
Next c
NumDecDispl = v
End If
End Function
===============================