If I have a list of values in column A ranging from 1-1000, how can I format the column so each value is ‘0000’ ie. based on len(max(A:A)) which would be 4. This would need to be dependent on the max value.
If the max in column A was 10,500, I would want every value to have a format ‘00000’. Any way to automatically set this – either in VBA or another formatting method? Thanks
>Solution :
Range("A2:A10").NumberFormat="00000"
should do it. For more dynamic, something like:
set rng = Range("A2:A10")
rng.NumberFormat = Left("0000000000000", len(application.max(rng)))
(quickly written and untested – no Excel at hand)