Formatting custom currency quirk

Hey all..

I have formatted a long list of numbers using a custom currency format code. The funny thing is it seems to work correctly on many of the numbers, and not quite correctly on others.

The code is --> 0[$EN ]

The resulting number looks like: 501 EN <-- with an extra space after it.

(The numbers designate a particular test, and the "EN" tells us it's in English. I took the "EN" out, so these would format as numbers and could be sorted as such.)

I put the extra space in after the EN for cosmetic reasons. This number is right-aligned, and the next column of data is left-aligned, so this just makes a space between for readability.

Here's the issue -- Most numbers format with the extra space, but some don't. I've applied the format globally to the whole column, unformatted, reformatted, and get the same results.

It's not a big deal, we can live without the extra space, but I just can't figure out why it's doing this.

Any ideas?

Thanks!

John

To get the blank before EN, I had to use 0 [$EN ], with a space before the
opening bracket.
Using XL 2003
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"jds217" <[email protected]> wrote in
message news:[email protected]...
>
> Hey all..
>
> I have formatted a long list of numbers using a custom currency format
> code. The funny thing is it seems to work correctly on many of the
> numbers, and not quite correctly on others.
>
> The code is --> 0[$EN ]
>
> The resulting number looks like: 501 EN <-- with an extra space after
> it.
>
> (The numbers designate a particular test, and the "EN" tells us it's in
> English. I took the "EN" out, so these would format as numbers and
> could be sorted as such.)
>
> I put the extra space in after the EN for cosmetic reasons. This
> number is right-aligned, and the next column of data is left-aligned,
> so this just makes a space between for readability.
>
> Here's the issue -- Most numbers format with the extra space, but some
> don't. I've applied the format globally to the whole column,
> unformatted, reformatted, and get the same results.
>
> It's not a big deal, we can live without the extra space, but I just
> can't figure out why it's doing this.
>
> Any ideas?
>
> Thanks!
>
> John
>
>
> --
> jds217
> ------------------------------------------------------------------------
> jds217's Profile:
> http://www.excelforum.com/member.php...o&userid=28608
> View this thread: http://www.excelforum.com/showthread...hreadid=511081
>

I used your number format, copied it from A1:A65536. Filled the same range
with numbers from 1-65536. Scrolled through the entire worksheet and I don't
see any abnormalities.

Can you provide a secific example of a number in your special format column,
and the corresponding entry in the next column.

Also, what is the format of the second column?

Also, your custom number format only addresses positive numbers. Any
possability that the abnormal cells are negative, zero or text?

"jds217" wrote:

>
> Hey all..
>
> I have formatted a long list of numbers using a custom currency format
> code. The funny thing is it seems to work correctly on many of the
> numbers, and not quite correctly on others.
>
> The code is --> 0[$EN ]
>
> The resulting number looks like: 501 EN <-- with an extra space after
> it.
>
> (The numbers designate a particular test, and the "EN" tells us it's in
> English. I took the "EN" out, so these would format as numbers and
> could be sorted as such.)
>
> I put the extra space in after the EN for cosmetic reasons. This
> number is right-aligned, and the next column of data is left-aligned,
> so this just makes a space between for readability.
>
> Here's the issue -- Most numbers format with the extra space, but some
> don't. I've applied the format globally to the whole column,
> unformatted, reformatted, and get the same results.
>
> It's not a big deal, we can live without the extra space, but I just
> can't figure out why it's doing this.
>
> Any ideas?
>
> Thanks!
>
> John
>
>
> --
> jds217
> ------------------------------------------------------------------------
> jds217's Profile: http://www.excelforum.com/member.php...o&userid=28608
> View this thread: http://www.excelforum.com/showthread...hreadid=511081
>
>