Adding zeros based on number of character

[Kb24]

Hi.. I got a question, i need to add either 1 or 2 zero in front of each cells based on the number of character(len), if len is 2 then add 2 zeros, if len is 3 then add 1 zero.. e.g :

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Basically i want to make to make all of them have 4 characters (it's for database purpose).. I've tried it but i can't seem to get it right..

Please Login or Register  to view this content.

Oh and how do i store this in Array ? this is important because the real data i'm working on has millions of cell.. I need Array to speed it up
thanks.

[MickG]

Hi, Try this:-

Change Range "Rng" to suit.
The resulting Range is stored as array "Ray".

Please Login or Register  to view this content.

Regards Mick

[mudraker]

Another method instead of testing the length of each value is to convert all to 4 characters

The following code is based on MickG's code

Please Login or Register  to view this content.

Note:- Based on the cells format Excel may drop the leading 0's if it considers the value is numeric

[Kb24]

Thanks for the answer, but it only changes the first row... could you fix it? Maybe you have to add columns. as well..

BTW what does .End(xlUp) do?

[mudraker]

Kb24

.End(xlUp) is normally used in conjunction with a command that looks upwards from the last row on a sheet to find the last used row in a particlar column - It is very useful if you do not know how many rows are populated


Example

Please Login or Register  to view this content.

As different versions of Excel have different total number of rows per sheet we use Rows.count to get the last row number - In most Excel versions this is row 65536

Assuming you do not have Excel 2007
The above code looks upwards from a65536


I just took a good look at your code, I only glanced at it last night

In your code you are testing the lenght & populating a c variable with a value but you do not give c a range address


As your loops are set to run 3 rows * 3 columns I suggestchanging the 2 loops to 1 loop

Please Login or Register  to view this content.

[Kb24]

I ended up changing them manually coz i wasn't patient enough :p
The huge spreadsheet i'm working at is affected by the 'pivot' cell..
So i just changed the pivot cells manually to change 'em all :p

Thanks for the help though.. I'll keep that in mind, it could be useful in the future..