Solved: Number Format in Table

[Zack Barresse]

Okay, quickie here. Referencing this thread/file, I have a table(s) setup and am entering numbers. How can I change the numbers from this ..

1234567

.. to look like this ..

1,234,567

Do I really need to put a comma in there manually? I'm ready to do this in Excel..

[Jacob Hilderbrand]

I think you need to type it in 1,234,567 and put the comma in. I always use Excel for when I need a table anyways.

[Zack Barresse]

Dang, ok. Just found the formula field too. Not bad .. for Word.

[Jacob Hilderbrand]

You can also try Insert | Field... | NumChars but I would just stick with Excel.

[Zack Barresse]

Yeah, I would much rather do Excel on this one as well. But alas, I'm in a bind on this one, it's out of my hands.

Okay, another question for you; VBA this time though. (I figure why take up a whole 'nother thread??)

My fields, with the formula "=SUM(ABOVE)", I want it to update, but see a problem: I may print and forget to do this. Is there a way to automatically update fields before a print or save perhaps?

[mdmackillop]

Join these two together and you won't be far away!
http://www.vbaexpress.com/kb/getarticle.php?kb_id=108
http://www.vbaexpress.com/kb/getarticle.php?kb_id=106

[TonyJollans]

Hi Zack,

What exactly is it that you're trying to do?

1. If you have a number as plain text, you just have to type it in in the format you want - there's no equivalent to an Excel cell - AFAIK you can't apply the kind of formatting you are asking for to a table cell automatically - although, of course, code could be written to do it if you really needed it.

But you can apply formatting to a field, for example:

{ =SUM(ABOVE) \# "#,##0" }

2. Updating is a bit more complicated.

If all you want is to update before printing, then go to Tools > Options > Print tab > Printing Options section, and check Update Fields.

If your SUM is summing other Fields, then you can set each of the separate fields to Calculate on Exit, but the Exit event doesn't necessarily get triggered so it's only partially successful - it depends on how you are using your document.

[mdmackillop]

You can also insert a spreadsheet into Word, but I find formatting, aligning etc. is a bit fiddly

[Zack Barresse]

Malcolm: Thanks! Those may prove useful.

Tony: Tried the field formatting you suggested. I couldn't get it to work. It just made the cell go blank. I typed in everything just like this ..

{=SUM(above)\# "#,##0.0"}

I have enabled the Print/Update Fields option; thanks. I'm using this as a report form. The file shell is located in the link I posted above (an earlier Word Help thread). I'm thinking that maybe I should have Inserted an Excel Object instead possibly ..

[TonyJollans]

I assumed you had a field already - you can't type in the braces, you must use Ctrl+F9 to insert them and type what you need inside - or type it all bar the braces, select it all and then press Ctrl+F9 to wrap it in braces.

[Zack Barresse]

Okay, I have a field already. I can get the braces there now, but they show up inside of the table cell. I think I have the format as needed. It shows up the way I want it to, so I don't think the cell format is an issue. I just want to make sure it will update now, as right now I have to right click the field, select Update Field. Is the best way to go about this the way Malcolm has shown?

So is there anyway to use the SUM formula (field) to sum only certain fields? I'm using it to SUM(RIGHT) at the moment. Can you specify which columns/rows to SUM?

[mdmackillop]

Hi Jake,
Extract from online help:

Perform other calculations in a table

1. Click the cell in which you want the result to appear.
2. On the Table menu, click Formula.
3. If Microsoft Word proposes a formula that you do not want to use, delete it from the Formula box. Do not delete the equal sign. If you deleted the equal sign, reinsert it.
4. In the Paste function box, click a function. For instance, to add numbers, click SUM. To reference the contents of a table cell, type the cell references in the parentheses in the formula. For instance, to add the numbers in cells A1 and B4, the formula would read =SUM(a1,b4)
5. In the Number format box, enter a format for the numbers. For example, to display the numbers as a decimal percentage, click 0.00%.

Note Word inserts the result of the calculation as a field in the cell you selected. If you change the values in the referenced cells, you can update the calculation by selecting the field and then pressing F9.

Note Microsoft Word table calculations must be manually recalculated. Consider using Microsoft Excel to perform complex calculations.

[TonyJollans]

Just previewed this and saw Malcolm has posted - but as I've written it, I will post anyway.

I am not an expert in this. Word Fields are immensely powerful but the User interface is archaic. That said, ...

Fields are many and varied and include Page Numbers, etc.
They behave slightly differently in Headers and Footers - thanks to Eric Fletcher for this, which I hadn't appreciated until recently.

In the main body of a document ..

Formulae go in Fields.

They can refer to values in other Fields
And they can refer to values in Table Cells

Fields can have a picture string so numbers can be formatted in various ways.

You can set a Field to Calculate On Exit which means that when you exit it, fields which depend on it are automatically updated - in theory (and generally in practice, too).

There isn't anything you can do to a field itself, to make it automatically update - updating happens according to various triggers and options set elsewhere.

Fields can be set to automatically update before printing.

You can not give these properties to Table Cells - only to Fields.

Now, the various functions like SUM(ABOVE) are special cases but a formula in a field can be almost anything you want. You just have to reference things correctly.

Table cells are referenced using the same mechanism as Excel - the first row is row 1, the second, row 2; the first column is column A, the second, column B, etc. You reference a cell as A1, B2, etc. I think it ought to be possible to make relative references in theory, but the code would be complex.

So, to add particular cells together, say the first, second and fourth columns in the second row of a table:

position the cursor where you want the answer (somewhere inside the same table for now, but it doesn't have to be)
select Table > Formula from the menu
under Formula, enter =A2+B2+D2
choose a format if you want
that's it.

[Zack Barresse]

Thanks for the solid help guys! It's much apprecaited!!