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

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

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  Reply With Quote

2. Good afternoon,

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

Hope this helps,  Reply With Quote

3. Do you need a function?
Try:
=TEXT(A1/24,"[hh]mmss")  Reply With Quote

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.  Reply With Quote

#### Posting Permissions

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