Formatting custom currency quirk

[jds217]

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

 

[Bernard Liengme]

To get the blank before EN, I had to use 0 [$EN ], with a space before the
opening bracket.
Using XL 2003

 

[Jonathan Cooper]

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?