Number formatting after/during a merge

[John Viall]

I am merging some data from an excel spreadsheet. The data in the cell is
3.83 but after the merge it comes in as 3.8300000000000001.

I have tried to ensure the data in Excel is only two spaces using the format
number to two decimal places. I have truncated the value to two locations,
and in word told the field to preserve the formatting.

Nothing has worked.

Any assistance would be greatly appreciated.

 

[John Viall]

I have already found a solution, but not an answer to why it is doing this in
the first place.

Alt-F9, then following the merge field, I added \# #.00 and it worked fine.
But why is it doing this?

Thanks,
John

 

[Peter Jamieson]

Use Alt-F9 in Word to display the field codes.

if for example your field is

{ MERGEFIELD myfield \*Mergeformat }

change it to

{ MERGEFIELD myfield \#0.00 }

"Preserve the formatting" probably didn't mean what you expected - it
probably meant include the "\*Mergeformat" switch , which is really to do
with the typeface, font size, tx colour, all that stuff, rather than numeric
rounding and so on.

For some more info. about these "elongated decimals" such as
3.8300000000000001, see http://tips.pjmsn.me.uk/t0003.htm , but frankly, I
doubt if that will tell you anything particularly useful.

Peter Jamieson

 

[John Viall]

Thanks, it was your post to a previous person regarding the % formating that
gave me the clues to Google.

Thanks,
John

 

[Graham Mayor]

It does so because Word reads the underlying raw data and not the Excel
formatted data.
See http://www.gmayor.com/formatting_word_fields.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Peter Jamieson]

FWIW, I used to think this was the case because of the odd behaviour of the
ODBC driver and (in this case) the OLE DB provider, and the similarities of
the numbers to numbers that have been stored as floating point numbers and
rounded incorrectly.

But now I'm not so sure - it seems more likely that the OLE DB provider is
introducing errors on conversion. It's difficult to tell without knowing the
internals of the .xls format, but if you start with a .xls where this
problem occurs and save it as a .xml there is no sign of these long numbers,
which suggests that Excel may not actually be storing them as floating point
numbers even in the .xls.

http://tips.pjmsn.me.uk/t0003.htm isn't the last word on the subject -
there's other material that could usefully go in there - but I think it's
probably the best collection of background info. on how this stuff actually
behaves. The trouble is that there is no "do-it-all" solution for some of
the problems - AFAICS the two main workarounds are still: use switches, or
in some cases, revert to DDE.

Peter Jamieson