VBA to add "0" at the beginning depending on cell value length

Hi there

I was thinking about creating a code that would add automatically a "0" at the beginning of the cell value if its length is 12. I realise I could play around with some formulas and get the result but I'd like to know how to approach this in VBA.

Eg. in reports I get numbers that in some cases I want as full numbers and not cut short like getting "99123000123" instead of "099123000123".

I've come up with the below which doesn't quite work... I also checked how VBA expects the number formats and the one that could help is "0"#, however in VBA I'd need to put it as ""0"#" which makes it impossible.

No idea though why it doesn't work in the first place but I'm quite bad at these things so that's probably the reason

Please Login or Register  to view this content.

Cheers

Try one of these

Please Login or Register  to view this content.

Hiya,

Amazing stuff, works like charm, both of these actually.

I was wondering (not being too much of an expert, learning these things as I go), if I put these codes into the ThisWorkbook code - will it automatically change the A2:A500 cells without the need to run the macro?

If it's possible, do I put it as a Public Sub/Private Sub? Does it change anything?

Sorry if I'm being dense
Cheers

Why not just format the cells in the column as "0000000000000"?

Originally Posted by jindon

Why not just format the cells in the column as "0000000000000"?

Because in that column I also have other numbers that are shorter and it gives them a nasty look and the zeros would need to be deleted for other uses.

Eg. 991230000123 would become 0991230000123 correctly, but 1223334444 would become 0001223334444 and if I wanted to copy and paste it in other places it would become a nuisance

@Mumps1 yeah I noticed this one it works nicely.