Checking number format within a cell

mtranchi · May 4th, 2011, 10:22 AM

hey jeskit.
this code:

Code:

Right(stFormatStyle, 1)

The right function looks at a string (in this case, stFormatStyle is my variable and it's holding the formatting code for the cell, like "0.00%") and takes however many characters starting from the right-hand side of the string. You can click on help and then type in Right and it should pop up what the right() function information.

the line

Code:

iPeriodPosition = InStr(stFormatStyle, ".")

should equal 0 if there is no period in the formatting style. Give me a while, (today's my birthday) and i'll see if i can put together a finished piece of code for you :)

jeskit · May 4th, 2011, 10:28 AM

Hi

Thanks! Happy birthday by the way!

mtranchi · May 4th, 2011, 10:49 AM

instead of giving me this:

If its a percentage then it should return to column I "%10.(whatever decimal it is, should include 0"f%%"

Can you give me all the specific strings that can be returned? I.e.:

If its a percentage with 2 digits after the decimal point, then it should return to column I "%10.2f%%"

I'm still not sure exactly what you're looking for as far as what this function is going to return for results

mtranchi · May 4th, 2011, 10:53 AM

also, what are all the possible format styles the cell may have?

jeskit · May 4th, 2011, 10:56 AM

Hi

Ok.

The strings i would like it to return are:

if its a percentage and depending on the number of decimal places for example "%10.2f%%"
if its a number and depending on the number of decimal places For example "%10.2f%%"
and for anything else "%10.0f%%"

The tsring which i need it to return it basically same for all instances except for when its a percentage. When its a percentage it should return 2 % signs at the end instead of 1

Does this help?

mtranchi · May 4th, 2011, 11:04 AM

thanks btw on the happy birthday. no one was here when i went out to kitchen, so have some time, lol.

ok, so basically if it's formatted as a percent, 2 "%" signs on the end, all else the same.

As far as the number of digits after the decimal, would a number with 4 look like this?

"%10.4f%%"

And for your "anything else" part, NO MATTER WHAT, it should look EXACTLY like this:

"%10.0f%%"

jeskit · May 4th, 2011, 11:56 AM

Yep that is correct!

mtranchi · May 4th, 2011, 12:01 PM

okay, here are the rows and sample format styles i used:

Row Number Formatting style FormatStyle string returned
1 45 General General
2 45.00 comma _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
3 45.00 number 0.00
4 45.000 number 0.000
5 45% percent 0%
6 45.00% percent 0.00%
7 45.000% percent 0.000%

Here's the Function:

Code:

Option Explicit

'I declared these variables at the module level so your code doesn't have to allocate memory everytime the function is called
Dim stFormatStyle As String
Dim iStart As Integer
Dim iEnd As Integer
Dim iDigitsAfterDecimalPoint As Integer
Dim stResult As String
Dim iDecimals As Integer
Dim bIsPercentage As Boolean

Function fTryThis(Target As Range) As String

    'reset iDigitsAfterDecimalPoint
    iDigitsAfterDecimalPoint = 0
    
    'get the formatting style for the cell
    stFormatStyle = Target.NumberFormat
    
    'if the format style is "general", return the default string and exit the function
    If stFormatStyle = "General" Then
        fTryThis = "%10.0f%%"
        Exit Function
    End If
    
    'if the format style is "comma", find the number of digits after decimal and return the string
    'this is what the NumberFormat string looks like for a "comma" format:
        '   _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
    If InStr(stFormatStyle, "#,##") Then
        'find the period
        iStart = InStr(stFormatStyle, ".")
        'find the ending 0
        iEnd = InStr(iStart, stFormatStyle, "_")
        'subtract the two to get the number of digits after the period
        iDigitsAfterDecimalPoint = iEnd - iStart - 1
        'return the string
        fTryThis = "%10." & iDigitsAfterDecimalPoint & "f%"
        'exit the function
        Exit Function
    End If
    
    'this looks at the last character of the NumberFormat string and if it's a percentage sign, then sets a Boolean to true (this will be used later to put an extra "%" sign on the return string if need be
    If Right(stFormatStyle, 1) = "%" Then
         bIsPercentage = True
    Else
         bIsPercentage = False
    End If
    'find the decimal point position:
    iStart = InStr(stFormatStyle, ".")
    
    If iStart = 0 Then 'there is no period
        iDigitsAfterDecimalPoint = 0
    Else 'there is a period
        iDigitsAfterDecimalPoint = Len(stFormatStyle) - iStart
    End If
    
    'if it's a percentage format with digits after period, subtract one to account for the "%" sign
    If bIsPercentage And iStart > 0 Then
        If bIsPercentage Then iDigitsAfterDecimalPoint = iDigitsAfterDecimalPoint - 1
    End If
    'Now you should have all the pieces you need so just put it together:
    stResult = "%10." & iDigitsAfterDecimalPoint & "f%"
    If bIsPercentage Then stResult = stResult & "%"
    
    'return the string
    fTryThis = stResult
End Function

and here are the results:

Row Function result
1 %10.0f%%
2 %10.2f%
3 %10.2f%
4 %10.3f%
5 %10.0f%%
6 %10.2f%%
7 %10.3f%%

Does that do it?

jeskit · May 5th, 2011, 04:12 AM

Hi

Yep that'll work.

However i am slightly confused. Does this all go in the module with the loop or does it go in a seperate function?

mtranchi · May 5th, 2011, 09:41 AM

i set it up as a separate function that you pass a single cell as a range and that returns a string. You'll notice that the variables i use are declared at the module level (not in the function itself). I did this so your computer doesnt' have to keep allocating and deallocating memory for every cell you use it on.

So basically should be able to copy the variables into the very top of your module (right under "Option Explicit"), and the function somewhere amongst your subs and functions. Then in the loop that you need the info, just call it. :)