Format cell for currency

D

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.
 
M

macropod

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
 
A

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


macropod said:
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]


dbalorenzini said:
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.
 
D

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

alorenzini said:
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


macropod said:
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]


dbalorenzini said:
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.
 
M

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]


alorenzini said:
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


macropod said:
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]


dbalorenzini said:
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.
 
A

alorenzini

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


macropod said:
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]


alorenzini said:
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


macropod said:
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]


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.
 
J

J Hop

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!
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.
On Wednesday, March 25, 2009 1:07 AM macropod wrote:
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]


"dbalorenzini" <[email protected]> wrote in message news:[email protected]...
On Wednesday, March 25, 2009 7:02 PM macropod wrote:
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]


"alorenzini" <[email protected]> wrote in message news:[email protected]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top