word field calculation working with . as decimal separator, but not with , as separator

Question

In word, I have 3 bookmarks where I can insert a number, and then a field that - when updated - calculates another value from these numbers. The formula I use is this one.

=X * Y * Z * 0.52*0.001

Everything works fine, when I use a point as the digital separator. But when I use a comma as the digital separator (which is standard in where I live), the calculation will be wrong. Say, the bookmarks have the following contents

x: 8,3 y: 9,4 z: 7,4

The calculation will treat the comma as a separator between two values and word will take the second value do make the calculation, so the result will be 3 * 4 * 4 * ..., which is completely wrong.

Is there a way to have word accept both digital separators?

I know that I can change the digital separator to a comma in system settings, but then, when I enter a number with a point, it won't work.

I don't want to change the system settings. Nor can I use VBA, since these are official documents of the company I work for.

Thanks and greets.

Answer 1

There's no simple way to do this just using Word or Word fields. Word relies on the same system settings for recognizing and formatting numeric values. So you have to use one of either "," or "." as a decimal point throughout, and that includes how you specify the decimal point in a numeric format option in a field. Or you need to find some other way, perhaps using an embedded Excel sheet or something like that.

In theory you could convert a number like 2,3 to 2.3 using an external file and a DATABASE field on a Windows Desktop Word system (but not Mac). That introduces other complications and I think it is best avoided, but here is the gist if you want to take a look...

Create a text file, say at c:\i\ia.udl containing the following text:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.;Jet OLEDB:Engine Type=96;

Then in your document, insert a nested field like this:

{ DATABASE \d "c:\\I\\ia.udl" \s "SELECT replace('{ x }',',','.')" }

Where all the {} are the special field brace pairs that you can insert using ctrl-F9. Also, notice that the backslashes in the pathname are doubled up and the quotation marks are all "straight" marks, not curly marks or backticks.

Select the fields and update them, and given your input data (x=8,3) the result should be 8.3.

That should work on any modern Windows desktop version of Office. You will probably notice that Word will insert an extra paragraph mark in the result.

To do your calculation, you could do something like

{ SET xxxx { DATABASE \d "c:\\i\\ia.udl" \s "SELECT replace('{ x }',',','.')" } }{ SET yyyy { DATABASE \d "c:\\i\\ia.udl" \s "SELECT replace('{ y }',',','.')" } }{ SET zzzz { DATABASE \d "c:\\i\\ia.udl" \s "SELECT replace('{ z }',',','.')" } }

Then

{ =xxxx * yyyy * zzzz * 0.52*0.001 }

There are ways to simplify that a little (but not a lot!), but other than practical questions such as "how do I distribute that ia.udl file", "can it always be in the same location in the file system", "will it work even on a system that only has the Jet provider (no..., you need a different .udl for that)", there are other considerations such as "how do I arrange that the same document could work but convert "." to "," instead". If that is what you need to do.