Add Leading Zero to a cell based on length of cell contents.

[Kickingkangaroos]

Hi All,

I am trying to make excel automatically add a leading zero to values which are 5 digits long;

i.e. number input is 15185, then excel automatically changes it to 015185.

If I put a Customer Number Format of 0##### it works, however, a user could put any length of number into these cells, and if the number is less than 5 digits I don't want a leading zero.

Is there any way of writing a small macro to sort this out.

The numbers would be input into range B16:223.

Many thanks,


Andy

 

[Special-K99]

Formula? Maybe
=IF(LEN(A1)<5,A1,TEXT(A1,"000000"))

 

[Andrew Poulsom]

Try the number format:

[>9999]000000;General

 

[Kickingkangaroos]

Thank you both so much!

Andrew I ended up using yours and it works perfectly!

Fantastic, speedy response.

Much appreciated.


Andy