Word/Excel (2002)

Topic

New Reply

I have a professor who merged data from Excel to Word. The problem is that in Word the merged data is display with too many decimal points, 78.46777777887 – something like this. How to I turn this off? Do I make the change in Excel or Word?

Reply | Quote

Replies

hi Melanie,

that’s the problem with professors, they always want to be too precise!

seriously though, you have two options.
the simplest is to format the numbers directly in excel. word will show them exactly as they are displayed in excel.

or edit the mergefields fields in word. you’ll need to add a formatting switch:
{ MERGEFIELD numbercolumn # #.##0,00 }

the formatting switch # #.##0,00 will tell word to round numbers to the second decimal. beware of internation settings, you may need to switch comma and the decimal point.

greetings,

Reply | Quote

yes – professors can be that way, unfortunately without them I wouldn’t have a job, or actually without students, the professor and I and the entire campus would be out of jobs!!

I did try reformatting in Excel. Everything from number with no decimal to custom but still comes across with 15 decimal places. I will try the field switch. Thanks.

Reply | Quote

Melanie,

A number format applied in Excel is only just that – formatting. The spreadsheet stores the numbers with all those ugly decimals.

In a mail merge, the actual data are transferred, not their format. Pieter has given you the best way to handle it – with a format switch in the merge field in Word. It is also possible to do it in Excel, but it’s more work:

– Add an extra column in Excel, with a column heading like FormattedNum
– If the numbers are in column A, starting in cell A2 (for example), enter the formula =TEXT(A2, “0.00”) in the second row of the new column
– Fill down as far as needed.
– Since these values are text, not numeric, they will be transferred to Word as displayed.
– Use the new (text) field as merge field instead of the numeric field.

As you see, Pieter’s solution is easier.

Reply | Quote

Thank you – I have been playing around with the field switches and I have gotten them to work.

The one thing I forgot to mention is that the entire merge process worked fine in Word/Excel 2000 last week. His department was upgraded to Office XP and now his numbers are formatted incorrectly.

Can you figure this one out??

Reply | Quote

hi Hans, Melanie.

formatting the data in excel that will be transferred to word, works for me in office XP (using the DDE merge option), no need to create extra columns.
btw, wether the transferred number is the result of an excel formula or hard coded, wether you’ve applied the number format directly or used a custom number format in excel, it still is tranferred as displayed in excel when i test it. (maybe this indeed won’t work if one would try the odbc or other merge options that are available)

greetings,

Reply | Quote

so what could have changed in the upgrade? All he did was open Excel, update the grades, quizzes, etc and then merged as he always did with Word 2000. I am not sure why it doesn’t display correctly?

Reply | Quote