XL 2K3 formatting 2

[MeGustaXL]

Hi there

Looonnnggg.. list of prices:

554.89
18.58
14.23
69.27
57.31
57.31
48.95
48.95

etc.

I am required to present these as fixed-length text in a custom format:

000000 000

so they look like
000554 890
000018 580
etc.

How can I switch from numbers to the leading-and trailing-zeroes thing?

Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

Hi,

This requirement in not a FORMAT. FORMAT changes nothing.

You must convert to text in an adjacnet column...
[tt]
=TEXT(A1*1000,"000000 000")
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

Ha! Skip old bean, I KNEW you'd do it!

How can I change them back again - say I get a list like:

000048 950
000092 450
000163 950
000054 830

and want them in dollars to 2 d.p?

Chris

Someday I'll know what I'm donig...damn!

 

[GlennUK]

Like this:

=+LEFT(A1,6)+(RIGHT(A1,3)/1000)


Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Easiest would be to parse, using Data > Text to columns..., then in an adjacent column, assuming that you parsed from A to A:B...
[tt]
C1: =A1*B1/100
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

Thanks Glenn! STAR for you, too :-D

Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

Oh, yes B1/1000, thanx to Glenn!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}