Add leading zeros in same column numbers and text

So quick sample of data :
B74
B74
9
94
1
948
B74

So if I have this data in a column you notice they have different lenght.
Now I want my macro to add leading zeros until the length of all occurrences is 4.

I know for numbers you can do a range.numberformat = "0000"
But this won't work for B74 because (obviously) it will not be recognized as a number.

I know in a formula you can do it with the command TEXT.
However I do not want to create extra columns I want something to alter it in place just like the numberformat does
but then for numbers and text.
Thanks!

Thanks Glenn but not looking fot a formula. As stated that would be even simpler using TEXT formula.

Thanks generaldisarray, so there is no simple function in VBA like numberformat which would do this?
And how is the performance of this solution with more the 100000 lines, do you know?
Anyway thanks for your solution

Here's how I read your problem

Thanks Jindon,
But actually B74 is ok and would become 0B74
However an interesting piece of code, thank you!

Thanks SHG, your code would probably work too.
So my conclusion would be that there is no real "easy" way of transforming all in the column to length 4
Like this:

Please Login or Register  to view this content.

Too bad.

Then this should do.

Please Login or Register  to view this content.

Thanks jindon.

You are welcome and thanks for the rep.

Thanks Martin,
This is not as short as I would want but it is the shortest yet

Originally Posted by GeneralDisarray

Yeah, i would really just recommend using another column if it's at all possible Looping over that many rows is never going to be speedy.

There might be a way to do the entire range in one shot so there is no loop, but i can't think of it. Usually SHG comes up with magic like that...

My code in #12 is not looping.