Results 1 to 4 of 4

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 NBVC; 2013-11-01 at 06:18 PM.

  2. #2
    Good afternoon,

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

    Hope this helps,

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    Do you need a function?
    Try:
    =TEXT(A1/24,"[hh]mmss")

  4. #4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •