adding a leading 00 to some loan numbers

[*alan*]

I've got several thousand loan numbers which need to be updated to a
ten-digit format by adding a leading 00.
That's easy enough to do, HOWEVER, some of them have already been updated
with the leading 00.
What formula can I use to add 00 only to those which have not already been
updated?
Thanks.

 

[Dave Peterson]

=right(rept("0",10)&a1,10)

If the values are all numeric (not text), you could apply a custom format:

Select the range
format|cells|Number tab|Custom category
0000000000

or use a formula like this if the cell looks numeric:
=text(a1,rept("0",10))
or
=text(a1,"0000000000")

 

[*alan*]

Thank you so much ---- the numbers were all text, and your formula worked
perfectly. I see also that it will work perfectly for some 7-digit numbers
that have be updated with 000, and in fact (by changing the number after the
comma)will work for addition of as many zeroes as will be necessary to
expand to any digit matrix. I'd been totally unaware of the "rept" factor.
Thanks again

 

[Tyro]

A good book on Excel, such as JohnWalkenbach's Bible series would help you.
It's only $40 and easy reading

Tyro

 

[*alan*]

Thanks for the suggestion --- I'll check it out.