Add leading zeroes in Function

hyoung3

New Member
Joined
May 9, 2011
Messages
21
Hi, I am trying to format a number to add leading zeroes in a function I have created. The input Integer will have a maximum length of 5 characters, but the output must be five characters with the proper number of leading zeroes. I tried using the format function (ie. Format(Strike, "00000"), but the output only gives me the input strike. Here is an example:

Code:
Public Function OSI(Strike As String)    
    'Declare variable to format Dollar Strike
    Dim IntDollar As Single
    Dim aDollar As Single
        
    'Dollar Strike format
        IntDollar = Int(Strike)
        aDollar = Format(IntDollar, 00000)
             
    'Output
        OSI = aDollar
    
End Function

If the Strike input equals 25.5 then the OSI output should be 00025. With this I am only getting 25.

I appreciate the help!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Most importantly, you need quotes around the 2nd argument of the Format function. Nearly as importantly, you've got several redundant steps...

Code:
Public Function OSI(Strike As String)
       
    OSI = Format(Int(Strike), "00000")
    
End Function
 
Upvote 0
Try,
Code:
[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Dim aDollar As String[/COLOR][/SIZE][/FONT]
instead of
Code:
[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Dim aDollar As Single[/COLOR][/SIZE][/FONT]
 
Upvote 0
Why do you need a UDF? This can be done with standard Excel functions:

=TEXT(INT(A1),"00000")
 
Upvote 0

Forum statistics

Threads
1,211,710
Messages
6,103,452
Members
447,866
Latest member
bowers261

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top