number formatting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

zuraan - 30 Nov 2007 08:03 GMT

Hi. I was woundering how I can add trailing zero's to numbers. I have
a colunm with number lemgths between 1 and 7 digets and need them all
to be 10 diget's long. I have no trouble with adding the zero's as
headers but needt hem as trailers. Thanks.

Reply to this Message

OssieMac - 30 Nov 2007 08:54 GMT

Hi zuraan,

You could put the following formula in a column beside the numbers and then
Copy, Paste special, Values over the original numbers.

=A1*10^(7-LEN(A1))

The formula assumes that the number is in cell A1

Signature

Regards,

OssieMac

> Hi. I was woundering how I can add trailing zero's to numbers. I have
> a colunm with number lemgths between 1 and 7 digets and need them all
> to be 10 diget's long. I have no trouble with adding the zero's as
> headers but needt hem as trailers. Thanks.

Reply to this Message

RichardSchollar - 30 Nov 2007 11:06 GMT

Hi Ossie

Shouldn't that be:

=A1*10^(10-LEN(A1))

Richard

> Hi zuraan,
>
[quoted text clipped - 16 lines]
>
> - Show quoted text -

Reply to this Message

Rick Rothstein (MVP - VB) - 30 Nov 2007 14:53 GMT

And another possible approach...

=--LEFT(A1&"0000000000",10)

or, if it will be copied through blank Column A cells...

=IF(A2="","",--LEFT(A2&"0000000000",10))

Rick

> Hi Ossie
>
[quoted text clipped - 25 lines]
>>
>> - Show quoted text -

Reply to this Message

Rate this thread: