Format cell for currency

Discussion:

(too old to reply)

dbalorenzini

2009-03-24 15:30:01 UTC

I am working with a table in Microsoft Word 2007. I have figured out how to
add up a certain number of cells and do a sum and have that formatted for
currency. My question is that the cells that are in the formula (B1+B2+B3,
etc.) do not appear as currency. How can I format the entry cells for
currency?

Thank you for you advice in advance.

macropod

2009-03-25 05:07:06 UTC

Permalink

Hi dbalorenzini,

You can do this by adding a numeric picture switch to the SUM field. To do this:
. select the field;
. press Shift-F9 to reveal the field coding. It should look something like {=SUM(B1:B3)} (note that you don't need to specify each
cell in a spanned range);
. edit the field so that you get {=SUM(B1:B3) \# $,0.00} (or whatever other numeric format you prefer - see below);
. position the cursor anywhere in this field and press F9 to update it;
. run your mailmerge.

Note: The '\# 0' in the field is referred to as a numeric picture switch. Other possibilities include:
. \# 0 for rounded whole numbers
. \# ,0 for rounded whole numbers with a thousands separator
. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
. \# $,0 for rounded whole dollars with a thousands separator
. \# $,0.00;($,0.00);- for currency, with brackets around negative numbers and a hyphen for 0 values

The precision of the displayed value is controilled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

If you use a final ';' in the formatting switch with nothing following, (eg \# $,0.00;($,0.00);) zero values will be suppressed.
Note that this suppresses 0s resulting from empty fields and from fields containing 0s.

For more information on doing calculations in Word, check out my Word Field Maths Tutorial, at:
http://www.wopr.com/index.php?showtopic=365442&st=0&p=365442&#entry365442
or
http://www.gmayor.com/downloads.htm#Third_party
--
Cheers
macropod
[MVP - Microsoft Word]

Post by dbalorenzini
I am working with a table in Microsoft Word 2007. I have figured out how to
add up a certain number of cells and do a sum and have that formatted for
currency. My question is that the cells that are in the formula (B1+B2+B3,
etc.) do not appear as currency. How can I format the entry cells for
currency?
Thank you for you advice in advance.

alorenzini

2009-03-25 21:55:03 UTC

Permalink

Thank you for your reply. That works for my SUM cells but I am talking about
the cells that are being sum.

eg.
=SUM(B1+B2+B3)

I am talking about setting the cell format to currency in the cells that the
numbers are being entered, in this case B1, B2, and B3.
--
Thanks,
Art
Database Administrator
Yankton, SD

Post by macropod
Hi dbalorenzini,
.. select the field;
.. press Shift-F9 to reveal the field coding. It should look something like {=SUM(B1:B3)} (note that you don't need to specify each
cell in a spanned range);
.. edit the field so that you get {=SUM(B1:B3) \# $,0.00} (or whatever other numeric format you prefer - see below);
.. position the cursor anywhere in this field and press F9 to update it;
.. run your mailmerge.
.. \# 0 for rounded whole numbers
.. \# ,0 for rounded whole numbers with a thousands separator
.. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
.. \# $,0 for rounded whole dollars with a thousands separator
.. \# $,0.00;($,0.00);- for currency, with brackets around negative numbers and a hyphen for 0 values
The precision of the displayed value is controilled by the '0.00'. You can use anything from '0' to '0.000000000000000'.
If you use a final ';' in the formatting switch with nothing following, (eg \# $,0.00;($,0.00);) zero values will be suppressed.
Note that this suppresses 0s resulting from empty fields and from fields containing 0s.
http://www.wopr.com/index.php?showtopic=365442&st=0&p=365442&#entry365442
or
http://www.gmayor.com/downloads.htm#Third_party
--
Cheers
macropod
[MVP - Microsoft Word]

Doug Robbins - Word MVP on news.microsoft.com

2009-03-25 22:43:41 UTC

Permalink

Insert an Excel spreadsheet instead of a Word table.
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Post by alorenzini
Thank you for your reply. That works for my SUM cells but I am talking about
the cells that are being sum.
eg.
=SUM(B1+B2+B3)
I am talking about setting the cell format to currency in the cells that the
numbers are being entered, in this case B1, B2, and B3.
--
Thanks,
Art
Database Administrator
Yankton, SD

Post by macropod
Hi dbalorenzini,
.. select the field;
.. press Shift-F9 to reveal the field coding. It should look something
like {=SUM(B1:B3)} (note that you don't need to specify each
cell in a spanned range);
.. edit the field so that you get {=SUM(B1:B3) \# $,0.00} (or whatever
other numeric format you prefer - see below);
.. position the cursor anywhere in this field and press F9 to update it;
.. run your mailmerge.
Note: The '\# 0' in the field is referred to as a numeric picture switch.
.. \# 0 for rounded whole numbers
.. \# ,0 for rounded whole numbers with a thousands separator
.. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
.. \# $,0 for rounded whole dollars with a thousands separator
.. \# $,0.00;($,0.00);- for currency, with brackets around negative
numbers and a hyphen for 0 values
The precision of the displayed value is controilled by the '0.00'. You
can use anything from '0' to '0.000000000000000'.
If you use a final ';' in the formatting switch with nothing following,
(eg \# $,0.00;($,0.00);) zero values will be suppressed.
Note that this suppresses 0s resulting from empty fields and from fields containing 0s.
For more information on doing calculations in Word, check out my Word
http://www.wopr.com/index.php?showtopic=365442&st=0&p=365442&#entry365442
or
http://www.gmayor.com/downloads.htm#Third_party
--
Cheers
macropod
[MVP - Microsoft Word]

macropod

2009-03-25 23:02:16 UTC

Permalink

Hi alorenzini,

Unless you're using a formula field, text formfield or a userform to populate the cell, Word has no auto-format tools to do the
formatting for you.
--
Cheers
macropod
[MVP - Microsoft Word]

Post by macropod
Hi dbalorenzini,
.. select the field;
.. press Shift-F9 to reveal the field coding. It should look something like {=SUM(B1:B3)} (note that you don't need to specify
each
cell in a spanned range);
.. edit the field so that you get {=SUM(B1:B3) \# $,0.00} (or whatever other numeric format you prefer - see below);
.. position the cursor anywhere in this field and press F9 to update it;
.. run your mailmerge.
.. \# 0 for rounded whole numbers
.. \# ,0 for rounded whole numbers with a thousands separator
.. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
.. \# $,0 for rounded whole dollars with a thousands separator
.. \# $,0.00;($,0.00);- for currency, with brackets around negative numbers and a hyphen for 0 values
The precision of the displayed value is controilled by the '0.00'. You can use anything from '0' to '0.000000000000000'.
If you use a final ';' in the formatting switch with nothing following, (eg \# $,0.00;($,0.00);) zero values will be suppressed.
Note that this suppresses 0s resulting from empty fields and from fields containing 0s.
http://www.wopr.com/index.php?showtopic=365442&st=0&p=365442&#entry365442
or
http://www.gmayor.com/downloads.htm#Third_party
--
Cheers
macropod
[MVP - Microsoft Word]

alorenzini

2009-03-25 23:11:01 UTC

Permalink

OK thanks for the update. It looks like it back to the drawing board.
--
Thanks,
Art
Database Administrator
Yankton, SD

Post by macropod
Hi alorenzini,
Unless you're using a formula field, text formfield or a userform to populate the cell, Word has no auto-format tools to do the
formatting for you.
--
Cheers
macropod
[MVP - Microsoft Word]

Post by macropod
Hi dbalorenzini,
.. select the field;
.. press Shift-F9 to reveal the field coding. It should look something like {=SUM(B1:B3)} (note that you don't need to specify
each
cell in a spanned range);
.. edit the field so that you get {=SUM(B1:B3) \# $,0.00} (or whatever other numeric format you prefer - see below);
.. position the cursor anywhere in this field and press F9 to update it;
.. run your mailmerge.
.. \# 0 for rounded whole numbers
.. \# ,0 for rounded whole numbers with a thousands separator
.. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
.. \# $,0 for rounded whole dollars with a thousands separator
.. \# $,0.00;($,0.00);- for currency, with brackets around negative numbers and a hyphen for 0 values
The precision of the displayed value is controilled by the '0.00'. You can use anything from '0' to '0.000000000000000'.
If you use a final ';' in the formatting switch with nothing following, (eg \# $,0.00;($,0.00);) zero values will be suppressed.
Note that this suppresses 0s resulting from empty fields and from fields containing 0s.
http://www.wopr.com/index.php?showtopic=365442&st=0&p=365442&#entry365442
or
http://www.gmayor.com/downloads.htm#Third_party
--
Cheers
macropod
[MVP - Microsoft Word]

J Hop

2012-01-17 01:29:48 UTC

Permalink

I found that to be very helpful. with a little tweaking rto fit my needs, i was albe to make it work. I confused myself with my first attempt because the data was zero (0). I thought i was just getting $0.00. I wish i was as smart as i am.

Thank you word MVP!

Post by macropod
Hi dbalorenzini,
. select the field;
. press Shift-F9 to reveal the field coding. It should look something like {=SUM(B1:B3)} (note that you don't need to specify each
cell in a spanned range);
. edit the field so that you get {=SUM(B1:B3) \# $,0.00} (or whatever other numeric format you prefer - see below);
. position the cursor anywhere in this field and press F9 to update it;
. run your mailmerge.
. \# 0 for rounded whole numbers
. \# ,0 for rounded whole numbers with a thousands separator
. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
. \# $,0 for rounded whole dollars with a thousands separator
. \# $,0.00;($,0.00);- for currency, with brackets around negative numbers and a hyphen for 0 values
The precision of the displayed value is controilled by the '0.00'. You can use anything from '0' to '0.000000000000000'.
If you use a final ';' in the formatting switch with nothing following, (eg \# $,0.00;($,0.00);) zero values will be suppressed.
Note that this suppresses 0s resulting from empty fields and from fields containing 0s.
http://www.wopr.com/index.php?showtopic=365442&st=0&p=365442&#entry365442
or
http://www.gmayor.com/downloads.htm#Third_party
--
Cheers
macropod
[MVP - Microsoft Word]

Post by Doug Robbins - Word MVP on news.microsoft.com
Insert an Excel spreadsheet instead of a Word table.
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Post by alorenzini
OK thanks for the update. It looks like it back to the drawing board.
--
Thanks,
Art
Database Administrator
Yankton, SD