Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to retrieve number of visible digits after decimal point in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to retrieve number of visible digits after decimal pointin VBA
hi Nathan,
Sub Macro1() Dim n As Double, itg As Integer, dcm As Double n = 98.7654321 itg = Int(n) dcm = Split(n - itg, ".")(1) End Sub -- isabelle Le 2012-07-13 13:43, N Lee a écrit : 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to retrieve number of visible digits after decimal point in VBA
Thanks for the response, but it's not quite the answer to my question. That will tell me how many decimal places are in a Double, but I want to find how many decimal places are DISPLAYED in a cell. All of my cell VALUES are doubles, but they may be displayed with 0, 2, or 4 decimal places.
Even so, it's a clever bit of code. I think I'm going to make myself a custom function with that which could come in handy for a future project. Nathan On Friday, July 13, 2012 3:47:05 PM UTC-4, isabelle wrote: hi Nathan, Sub Macro1() Dim n As Double, itg As Integer, dcm As Double n = 98.7654321 itg = Int(n) dcm = Split(n - itg, ".")(1) End Sub -- isabelle Le 2012-07-13 13:43, N Lee a �crit : > 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to retrieve number of visible digits after decimal point in VBA
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 =============================== |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to retrieve number of visible digits after decimal point in VBA
"N Lee" wrote:
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'. Range("A1").Text returns to displayed contents of a cell. So if A1 contains =PI() with format Number with 2 decimal places, Range("A1").Text returns "3.14". If we only had to deal the Number format, the following algorithm would return the number of decimal places in variable n: Dim s As String, n As Long s = Range("a1").Text n = InStr(s, ".") If n 0 Then n = Len(s) - n However, that will not work with your format and negative numbers. The following is one algorithm (untested), which assumes that you do not have "." in text. Someone else might provide a regular expression solution, which would be better. n = InStr(s, ".") If n 0 Then For p = n+1 To Len(s) If Not IsNumeric(Mid(s, p, 1)) Then Exit For Next n = p - n -1 End If |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to retrieve number of visible digits after decimal point in VBA
Very clever plan. I'll get to trying it out on Monday. Thanks for the in-depth description. I hope it will help me solve this little puzzle.
Nathan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to retrieve number of visible digits after decimal point in VBA
On Fri, 13 Jul 2012 13:15:07 -0700, "joeu2004" wrote:
which assumes that you do not have "." in text. Excellent point! And one which I failed to consider in my regex solution. Fortunately, only requires a change in the regex to take that into account. And while I'm sure there are more efficient regexes for this purpose, this seems to work: ================================== Option Explicit Function NumDecDispl(rg As Range) As Variant Dim v() As Variant, c As Range Dim re As Object Const sPat As String = "^.*?\S*\.(\d+).*$" Dim i As Long Set re = CreateObject("vbscript.regexp") With re .Global = False .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 ===================================== |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to retrieve number of visible digits after decimal point in VBA
On Fri, 13 Jul 2012 13:16:41 -0700 (PDT), N Lee wrote:
Very clever plan. I'll get to trying it out on Monday. Thanks for the in-depth description. I hope it will help me solve this little puzzle. Nathan As I mentioned in my reply to joeu2004, who raised the issue of dots within the custom formatting that were not part of the number, I made a change in the regex portion. However, what I posted in response to his is flawed, and the latest iteration of the regex pattern is expressed by: Const sPat As String = "^.*?\d\.(\d+).*$" The entire UDF: ============================ 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\.(\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 ============================ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to retrieve number of visible digits after decimal point in VBA
On Fri, 13 Jul 2012 16:34:38 -0400, Ron Rosenfeld wrote:
On Fri, 13 Jul 2012 13:15:07 -0700, "joeu2004" wrote: which assumes that you do not have "." in text. Excellent point! And one which I failed to consider in my regex solution. Fortunately, only requires a change in the regex to take that into account. And while I'm sure there are more efficient regexes for this purpose, this seems to work: ================================== Option Explicit Function NumDecDispl(rg As Range) As Variant Dim v() As Variant, c As Range Dim re As Object Const sPat As String = "^.*?\S*\.(\d+).*$" Dim i As Long Set re = CreateObject("vbscript.regexp") With re .Global = False .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 ===================================== The regex should be changed to: ^.*?\d\.(\d+).*$ and .Global = True |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to check if a number has no more than 2 decimal digits | Excel Programming | |||
Digits to the right of decimal point | Excel Discussion (Misc queries) | |||
Number format still uses decimal point | New Users to Excel | |||
Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
Displaying only digits after the decimal point | Excel Worksheet Functions |