Wrong (cell) number format when data pulled from xls file to Word

  • Thread starter Thread starter spidie_man
  • Start date Start date
S

spidie_man

I have an excel 2007 spreadsheet from which some data is being extracted
(using mail merge and SQL) and put in a word document. I am able to get the
data in the word document, but some of the cells are being displayed in
scientific notation. They have 6 or 10 digits after the decimal place, I just
want 2 digits after the decimal place. I have checked the format of the cells
in the excel spreadsheet, over there they are being displayed properly.

In the spreadsheet, all cells have the same format, but only a few of these
cells are not being displayed properly when they are pulled into Word 2007.

Does anyone know how to fix this?
 
Are they really using scientific notation (e.g. 1.234+E5) ? Or are they
showing extra digits because of rounding errors, e.g. 1.459999 rather
than 1.45 ?

If it's rounding errors, you should be able to use numeric format
switches in the relevant MERGEFIELD fields, e.g. use Alt-F9 to show the
field code and change

{ MERGEFIELD mynumber }
or
{ MERGEFIELD mynumber \*Mergeformat }
to e.g.
{ MERGEFIELD mynumber \#0.00 }


If that does not do what you need,
a. you may find the following page useful (although I don't have an
explanation if the numbers really are coming across with E-notation).

http://tips.pjmsn.me.uk/t0003.htm

b. if you are issuing SQL in an OpenDataSource call, you may be able
to use Jet/VBA formatting functions to get the results you need.

Peter Jamieson

http://tips.pjmsn.me.uk
 
Back
Top