VBA Excel - leading zeros in number

[neveu]

hello Forum,

i would need your ideas for the next challenge:

i have a field containing numbers which should contain 6 characters.
in case i have number 345 then it should write 000345. this is very easy to be done using formatting, but i would actual need those leading zeroes.
the number in the field can have 1 to 6 digits(ex: 1; 11 ; 111 ; 1111; 11111; 111111)

thank you in advance for you inputs

 

[Peter_SSs]

In that case you will end up with text rather than a number, is that a problem?

Do you need a macro? Could you use a formula in an adjacent column? Perhaps:

=RIGHT("00000"&A1,6)

If you do need a macro, more detail about where the original data is and where the results should go would help.

 

[neveu]

Hi Peter,
A macro would be the best solution:

the data is input in column C starting with C3. the result should be on the same cell.

thank you

 

[Peter_SSs]

In the range to be worked on ..

1. Could there be blank cells?

2. Could there be cells that are not numeric?

3. Could there be decimal values? If so what to do with them?

 

[neveu]

there can only be numerical values, no zeroes.
it's ok if the output is text ( but the characters have to be digits)

 

[Peter_SSs]

Try this

<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> Add_Zeros()<br>    <SPAN style="color:#00007F">Dim</SPAN> a<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Range("C3", Range("C" & Rows.Count).End(xlUp))<br>       .NumberFormat = "@"<br>       a = .Value<br>       <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(a, 1)<br>        a(i, 1) = Right("00000" & a(i, 1), 6)<br>       <SPAN style="color:#00007F">Next</SPAN> i<br>       .Value = a<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Sektor]

In cell B1.

=REPT(0,6-LEN(A1))&A1

 

[neveu]

Hello Peter,

i would still need your advice on a small issue?
after i copy the data in column A, the leading zeroes dissapear.

the problem is that i cannot run the macro again as the pattern changed. now the numbers needed to be added leading zeroes are on column A; the only logical pattern is that they are situated one row below after the a cell containing the text "[backtab]".
on top of that besides the numbers that have to have leading zeroes, there are other numeric data which doens't need to be changed

ex:
[backtab]
111
text
othernumber
[backtab]
2334

is there any chance to modify the existing code for the above pattern?

thank you in advance

 

[Peter_SSs]

Try this

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Add_Zeros_Below_backtab()<br>    <SPAN style="color:#00007F">Dim</SPAN> Found <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> FirstAddr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("A1", Range("A" & Rows.Count).End(xlUp))<br>        <SPAN style="color:#00007F">Set</SPAN> Found = .Find(What:="[backtab]", After:=.Cells(1, 1), _<br>            LookIn:=xlValues, LookAt:=xlWhole, _<br>            MatchCase:=False, SearchFormat:=False)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            FirstAddr = Found.Address<br>            <SPAN style="color:#00007F">Do</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> Found.Offset(1)<br>                    .NumberFormat = "@"<br>                    .Value = Right("00000" & .Value, 6)<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> Found = .FindNext(After:=Found)<br>            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> Found.Address <> FirstAddr<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[llizanoa]

Hi
I'm using this useful macro for social security numbers but once I apply it. is deleting the leading zeros in all numbers that start with zero.

example
011-22-333
converted
1122333

how can I avoid that since it doesn't matter if you have pre format the columns to text or custom?

Thanks