Converting this "French" currency format to "English"?

[StargateFanNotAtHome]

I found this neat coding in the custom number format box:

_ * #,##0.00_) $_ ;_ * (#,##0.00) $_ ;_ * "-"??_) $_ ;_ @_

It produces this when there is no dollar amount:

- $

I like this format, but of course, need to write the currency in the
more standard English format since this particular sheet is in English
and not French so that I should get this instead:

$ -

Dunceville me ... I tried everything I could think of but I can't get
it to come out right. Every attempt gave me an error code with the
kind suggestion to use the "built-in number formats" D P <g>

How would we change the above to display
$ -
when the cell is blank?

Thanks! D

 

[Shane Devenshire]

Hi,

The custom format you showed is not a legal format in Excel and Excel won't
let you enter it.

However, the following built-in custom format seems to do something similar
to what you want:

_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)

 

[StargateFanNotAtHome]

Hi, thanks so much for your response.

Actually, I see that I was unclear. Legal or no, that's what they
have in the cell and it's pretty neat. Without anything in the cell,
no zero or other number, it shows as

- $

in the French fashion with the $ sign after the numbers. It makes it
nice and clear that no amount has been entered.

I'd like the blank cell to look like that but in the more customary
"English" format of

$ -

Your code works very well (thank you) but it shows a blank cell
_until_ you enter something into it which isn't quite what the sheet
has. My goal is merely to change the English spreadsheets into a
different display format but not to eliminate the "$ - " altogether.
But as I mentioned, even though I just moved the $ sign a few
different ways, Excel wouldn't accept them (probably having to do with
that illegal stuff you mention).

I'm sure this can be done but I haven't been successful myself.

Thanks much! D

 

[StargateFan]

I found this neat coding in the custom number format box:

_ * #,##0.00_) $_ ;_ * (#,##0.00) $_ ;_ * "-"??_) $_ ;_ @_

It produces this when there is no dollar amount:

- $

I like this format, but of course, need to write the currency in the
more standard English format since this particular sheet is in English
and not French so that I should get this instead:

$ -

Dunceville me ... I tried everything I could think of but I can't get
it to come out right. Every attempt gave me an error code with the
kind suggestion to use the "built-in number formats" D P <g>

How would we change the above to display
$ -
when the cell is blank?

Thanks! D

Good Morning!

Was still hoping to convert to English format "legally" ... <g>

This coding to show French currenty may be "illegal" but how can we
reproduce the results? Here it is again:

_ * #,##0.00_) $_ ;_ * (#,##0.00) $_ ;_ * "-"??_) $_ ;_ @_

I've tried everything I know. Currently, the spreadsheet feels "off"
because the currency is in the French format yet the spreadsheet is in
English. When there is nothing in the totals column because there is
no value in the originating cells, the cells show this:

$ -

The minute we put a value in the depdent cells, naturally the totals
column then gets populated. The custom format seems easy at first but
it actually hasn't been since we need to keep the ($#,##0.00)
formatting (which I'd actually add "Red" to, as well) and that's where
I can't seem to make it work. Give me something a little beyond the
normal and I always have troubles <lol>.

Thanks. D

 

[Héctor Miguel]

hi, !

1) AFIAK there is NO WAY to put a (pre-filling) "mask" into EMPTY cells, however...

2) when you customize number formats you have four available sections separated by 3 semi-colon (
the 1st section applies for positive numbers
the 2nd section applies for negative numbers
the 3rd section applies for zero values
the 4th section applies for text values
(you also have format codes available for each section, they are brackets delimited)

3) if you play with the numeric sections (regarding to positive, negative, zeroes and text)
you can also define a color (according to conditions) prefixing the color to the section
(i.e.) settle color to a custom format would it be something like the following...
[Green][<0]#,##0;[Blue][>10000]#,##0;[Yellow]#,##0

check the online help and you will find a great variety of applications for the format codes
watch for (possible) differences in the names of the colors for languages, etc.

hth,
hector.

 

[StargateFan]

hi, !

1) AFIAK there is NO WAY to put a (pre-filling) "mask" into EMPTY cells, however...

2) when you customize number formats you have four available sections separated by 3 semi-colon (
the 1st section applies for positive numbers
the 2nd section applies for negative numbers
the 3rd section applies for zero values
the 4th section applies for text values
(you also have format codes available for each section, they are brackets delimited)

3) if you play with the numeric sections (regarding to positive, negative, zeroes and text)
you can also define a color (according to conditions) prefixing the color to the section
(i.e.) settle color to a custom format would it be something like the following...
[Green][<0]#,##0;[Blue][>10000]#,##0;[Yellow]#,##0

check the online help and you will find a great variety of applications for the format codes
watch for (possible) differences in the names of the colors for languages, etc.

hth,
hector.

Hector, thank you for your explanation! Much appreciated. I'm going
to study this. (Love these ngs, learn so much!) When there is a lot
going on in custom formatting, I run into troubles and maybe this will
help me learn how to handle the more complex examples once and for all
<g>.

Fortunately for me, esp. since it's a spreadsheet for work, I
absolutely have had to keep working on it. And, as with other
discrepancies that have turned up in this workbook, I've found that my
predecessor has been as inconsistent here as with a lot of other areas
in my job. She didn't pick a standard and stick with it. That turned
out to be a good thing as I found about 3 other pieces of custom
formatting which supposedly achieved the same result yet that gave me
enough of a variety that I was finally able to successfully edit the
coding. I then went back and made _all_ pertinent currency cells the
same format. To get the dollar sign then some spaces and then a
hyphen and a few more spaces, when there would otherwise be a "0" and
yet still show up with parantheses and in red, this seems to be
working:


_-$* #,##0.00 ;[Red]($* #,##0.00) ;_-$* "- ";_-@_


I now have a folder-full of invoices to enter into this large workbook
so time will tell if this continues to work as well as it has been
since yesterday.

Thanks everyone. I don't know how "legal" the above is, but it's
allowing me to keep the basic structure that they're used to, which is
ideal, though converted it to English, which is what it should be for
consistency. I'll keep the French stuff in case they need me to
translate this at some point, if ever.

Thanks! D