Adding a zero place holder within a string if value is < 10

MylesMc

New member
Joined
Aug 14, 2013
Messages
13
Reaction score
0
Points
0
I have a vba function that converts Decimal numbers to Degree Minutes Seconds coordinate values. For example, the Decimal: 37.9661 converts to 375758. Where 37 is Degrees, 57 is Minutes and 58 is Seconds. This means I have two slots for values of each. 2 slots for degrees, 2 for minutes, and 2 for seconds. This totals to 6 digits

The problem in my code is that when I have a conversion that comes out to have a single digit value (any value less than 10) in the minutes or seconds slot, my code doesn't insert a zero place holder in front so I end up with a final value less than 6 digits. For example, 38.1058 converts to 38621 instead of 380621 as it needs to in order to fill two slots for the minutes value.

The code I am using is the following:


Code:
'From Dec Degree to DMS
Function Convert_Degree(Decimal_Deg) As Variant
    With Application
        'Set degree to Integer of Argument Passed
        degrees = Int(Decimal_Deg)
        'Set minutes to 60 times the number to the right
        'of the decimal for the variable Decimal_Deg
        minutes = (Decimal_Deg - degrees) * 60
        'Set seconds to 60 times the number to the right of the
        'decimal for the variable Minute
        seconds = Format(((minutes - Int(minutes)) * 60), "0")
        'Returns the Result of degree conversion
        Convert_Degree = " " & degrees & Int(minutes) _
            & seconds
    End With
End Function


Any help is appreciated.

Thanks,
Myles Mc
 
Last edited by a moderator:

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Good afternoon,

You can use the text function to return a mandatory 2 digit format:: =text(--target--,"00")

Hope this helps,
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,200
Reaction score
16
Points
38
Excel Version(s)
365
Do you need a function?
Try:
=TEXT(A1/24,"[hh]mmss")
 

MylesMc

New member
Joined
Aug 14, 2013
Messages
13
Reaction score
0
Points
0
Thanks this worked bgoree!

A big thanks to p45cal as well, but I needed the code in the subroutine so I could run the custom function within the cells.
 
Top