Function to add leading zeroes to make a 4 digit number 1

[remsaw]

I have an Excel spreadsheet with a long list of numbers on it, (over 1500). Some of the numbers are less than four digits (ie - 23) and I need them to all be converted to four digit numbers with leading zeroes (ie - 0023).

Yes, I have already converted that column to text so it will display the leading zeroes. Rather than search through the list and manually edit the numbers to add the leading zeroes, I wrote a function to do this, the function is called "Modify_Barcodes" - and it does not work!! I am not sure why - here is the code:

Public Function Modify_Barcodes(Orig_Numb As String)
'Created 3/7/07 by Scott Wasmer
'This function will take a number of less than four digits
'and add leading zeroes to it so as to make it have four digits.
'eg - 23 would modify to 0023

'Declare some variables
Dim intOrig_String As Integer
Dim intZeros_2_Add As Integer


'Detemine the number of leading zeroes to add
intOrig_String = Len(Orig_Numb)
intZeros_2_Add = 4 - intOrig_String

'Now concantanate the string with the zeroes.
Select Case intZeros_2_Add

Case 0
Orig_Numb = Orig_Numb
Case 1
Orig_Numb = "0" & Orig_Numb
Case 2
Orig_Numb = "00" & Orig_Numb
Case 3
Orig_Numb = "000" & Orig_Numb
Case Else
'Put some error routine here

End Select

End Function


I tried it both as passing the number in as a string and an integer, tried schemes of getting the value or the string, etc.

I always get the same result. For example, if in cell A23 there is the number 23 and then in cell E23 I type =Modify_Barcodes(A23) the only thing I get in E23 is 0.

When I put breakpoint in the code, all the variables have the correct values. After the line
Case 2
Orig_Numb = "00" & Orig_Numb

I put in a Debug.Print Orig_Numb statement, and then in the immediate window I get the result of 00 23 I was not sure why of the space either. But as I said, in the cell on the worksheet, all I get is 0.

If anybody can spot the error in my code or thinking, please let me know.

Thanks

Scott

 

[SkipVought]

Hi,

There's a difference between DISPLAYING leading zeros for numbers and CONVERTING numbers to TEXT with leading zeros.

For the former,

select the range

Cells/Format - Number Tab - Custom in the TYPE box 0000

For the latter, you cannot use a function to change the value in the cell. You can return a value to ANOTHER CELL, and that simply is
[tt]
=TEXT(A1,"0000")
[/tt]
if your number is in A1.



Skip,
[sub]
[red][/red]
[/sub]

 

[remsaw]

Why is it the simple solution is so elusive? Thanks Skip - I feel like a real dummy-head for not thinking of the custom number format!!!

Scott