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.
--
alan

=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* wrote:
>
> 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.
> --
> alan

--

Dave Peterson

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
--
alan

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> =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* wrote:
>>
>> 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.
>> --
>> alan
>
> --
>
> Dave Peterson

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

Tyro

"*alan*" <(E-Mail Removed)> wrote in message
news:bgkBj.15649$(E-Mail Removed)...
> 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.
> --
> alan

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

"Tyro" <(E-Mail Removed)> wrote in message
newsTlBj.16746$(E-Mail Removed)...
>A good book on Excel, such as JohnWalkenbach's Bible series would help you.
>It's only $40 and easy reading
>
> Tyro
>
> "*alan*" <(E-Mail Removed)> wrote in message
> news:bgkBj.15649$(E-Mail Removed)...
>> 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.
>> --
>> alan
>
>