If VBA (or some other programming approach) is an option then I think that is the best and most versatile way to solve this.
There is an approach that uses a DATABASE field to reformat the numbers. It can usually be made to work on a Windows desktop PC installation of Office, but it may not work in your Citrix environment because
it needs to be able to access an external file, which I would guess in a Citrix environment would be on a server somewhere. More details below.
it uses the Jet/ACE OLE DB provider - normally that would all be available on a desktop environment, but again I don't know whether Word would be able to do this in a Citrix environment.
Finally, if these numbers can come in with all sorts of different national numbering formats, I don't think it would be easy to write fields (and possibly not even the VBA code) that would cope with all of them.
So I'm assuming the numbers are coming in as you mentioned and that you need to end up with something that has no spaces or "$", and "." as the decimal point. (Once you have got that, you can use Word's numeric formatting to format them).
If your numbers have any quotation marks in them, this DATABASE field technique probably won't work. For example, I think the Swiss may use a single quotation character "'" as the thousands group separator.
If the user has access to a file system via, say a "g:" drive or via, say, \myserver\myshare then what you need is a small plain text file in a known place in that file system. For example, suppose you have a file called
g:\standard\i.udl
or
\\myserver\myshare\standard\i.udl
that contains 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;
If you have an older version of Word, you need to specify the older Jet provider, using e.g.
[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.;Jet OLEDB:Engine Type=96;
A .udl file is a "Universal Data Link" file that allowed you to specify an ODBC or OLEDB data source. Word still recognises them, and using this file allows you to issue Jet/ACE SQL queries without actually having a .mdb or .accdb database.
If the user system can only access files via HTTP, instead of a .udl file you will need a minimal Access .mdb file. You can create one in Access. It doesn't need to have any user tables or queries. Let's say it is at
http://192.168.200.200/standard/i.mdb
(I have only been able to test using http: here, not https:, so I am not certain that this can be made to work with https:)
Now ensure that you can execute a Jet/ACE query by inserting the following example DATABASE field in your document (use ctrl-F9 for all the special field code braces and type everything else between them):
{ DATABASE \d "g:\\standard\\i.udl" \s "SELECT 1" }
(and the following will probably also work:
{ DATABASE \d "g:/standard/i.udl" \s "SELECT 1" }
)
or
{ DATABASE \d "\\\\myserver\\myshare\\standard\\i.udl" \s "SELECT 2" }
or
{ DATABASE \d "http://192.168.200.200/standard/i.mdb" \s "SELECT 3" }
depending on where you put your file. Ensure that backslashes in path literals are doubled up, as I have shown.
Select that field, update it via F9, and look at the result. If there is an error message and your path/access permissions etc. are all OK, then this approach is not going to work. If you see the result of the query (a couple of paragraphs with 1, 2, or 3 in the second one, then so far so good.
At this point I should point out that you can't put a DATABASE field in a Word table. If you need these values to be in a table you will have to put these DATABASE fields outside the table, then use a SET field to assign the result to a bookmark, which you can then { REF } inside your table.
What you then need is to verify that you can transform your number to a format that you can work with. I'm going to step through this, stating with your first example number
2 000,00 $
First change your field so it looks like this, where is whatever path worked above.
{ DATABASE \d "<thatpath>" \s "SELECT replace('2 000,00 $',' ','')" }
Ensure that '' is two single vertical quotation marks, not a " double quotation mark character.
Update that, and the result should contain the number without the spaces:
2000,00$
Now try
{ DATABASE \d "<thatpath>" \s "SELECT replace(replace('2 000,00 $',' ',''),'$','')" }
that should remove the "$"
and finally
{ DATABASE \d "<thatpath>" \s "SELECT replace(replace(replace('2 000,00 $',' ',''),'$',''),',','.')" }
which should give you
2000.00
But actually, your number is in a bookmark called DataName, so you really need
{ DATABASE \d "<thatpath>" \s "SELECT replace(replace(replace('{ REF DataName }',' ',''),'$',''),',','.')" }
where the { } are the proper field code braces you can enter with ctrl-F9.
(It's usually better to omit the REF and just use
{ DATABASE \d "<thatpath>" \s "SELECT replace(replace(replace('{ DataName }',' ',''),'$',''),',','.')" }
If you need to substitute other characters, you can add more replace functions, but there is a limit (probably 255 characters) to the total length of the SELECT statement.
If that all works OK, the next step is to remove the extra paragraph mark that Word started inserting after Word 2000. Luckily, you can do that by nesting all that inside a QUOTE field, e.g.
{ QUOTE "{ DATABASE \d "<thatpath>" \s "SELECT replace(replace(replace('{ DataName }',' ',''),'$',''),',','.')" }" }
You can also add a numeric format to the QUOTE field if you want, e.g.
{ QUOTE "{ DATABASE \d "<thatpath>" \s "SELECT replace(replace(replace('{ DataName }',' ',''),'$',''),',','.')" }" \#"$,0.00"}
If you need to reference that value elsewhere (e.g. inside a table, then you can do
{ SET mynumber "{ QUOTE "{ DATABASE \d "<thatpath>" \s "SELECT replace(replace(replace('{ DataName }',' ',''),'$',''),',','.')" }" }" }
then e.g.
{ mynumber \#"$,0.00 }
If there are a lot of these numbers in the document, there will be a lot of DATABASE fields, and they may take some time to execute. As far as I know, Word caches the database/.udl data so execution will probably speed up after the first DATABASE field is updated, but you should verify that Word doesn't actually hang, or appear to hang.
Again, if there are a lot of numbers to format, you can put all "common text" into other bookmarks which will help make these fields shorter and should improve maintainability. e.g. you could do
(at the beginning of the document)
{ SET dbpath "g:\standard\ia.udl" } (single backslashes here will work OK later)
{ SET qhead "SELECT replace(replace(replace('" }
{ SET qtail "',' ',''),'$',''),',','.')" }
{ SET format "$,0.00" }
then use
{ QUOTE "{ DATABASE \d "{ dbpath }" \s "{ qhead }{ DataName }{ qtail }" }" \#"{ format }" }
Some people prefer to remove inessential white space and quotation marks. Personally I prefer to leave that stuff and would need to test to be sure what exactly can be omitted, but for example you can certainly reduce this to
{QUOTE "{DATABASE \d "{dbpath}" \s "{qhead}{DataName}{qtail}"}" \#"{format}"}
What are the values of the "Decimal Symbol," "Digit grouping symbol" and currency symbol set up in your operating system? (If it's Windows, look in Settings or Control Panel, and be willing to click through a number of links; if it's Mac OS(X), head for System Preferences->Language and Region->Advanced...). They are one of the main things that determine what Word does with things that look like numbers or currency amounts. It's easier to discuss this problem knowing those specific values than in the general case. e.g., having "space" as a digit group separator can create additional problems. – None – 2019-12-01T15:16:01.957
There is a general problem in Word when you want to do arithmetic with amounts that are not in the format defined in control panel. Usually, Word will treat the value as "text', possibly showing a syntax error as the field result, or as a list of numbers which Word will then sum. So e.g. if your decimal point character is "," and your digit grouping character is " ", Word will probably recognise "211.50 as a list containing 211 and 50, then add them up to give you 261. Also, in the numeric pattern, if your OS says the decimal point is ".", you need to use "." in the pattern, and so on. – None – 2019-12-01T15:21:18.087
Yokki, about your first comment, I cannot know those values as we run on a Citrix / Windows Server environment and we have no access to those settings ... Sorry. As for the second comment, you are totally right. All numbers that work perfectly because they have ,00 $ at the end. The only one that bugs is the 211,50 $. Any clue on how to fix that? Note: I can't access the settings above. – Anthony – 2019-12-02T19:07:54.417
OK, if the decimal point character is set to “.” then Word won’t process “211,50” properly, so if you cannot change the way your amounts are included you are stuck. FWIW I do not believe changing the language of the text or document as suggested elsewhere will make a difference. Do you have any control over how these values are brought into Word? (Incidentally, not sure I am seeing notifications on super user or I would have replied yesterday). – None – 2019-12-04T16:07:05.170
I have no control how the values are brought in. They come in the 2 500,00 $ format – Anthony – 2019-12-10T18:53:22.030
In that case I just can't see any way to do what you want. – None – 2019-12-10T23:18:40.980
1Is it possible to bring only the numbers into excel first? If so, then a little work with Find and Replace and cell formatting would have them in ready for import into word in no time. – KnotWright – 2019-12-16T21:03:57.280
@KnotWright good point. Perhaps a better set of questions for Anthony would start with " how are you bringing the numbers in?" – None – 2019-12-16T21:29:14.017
@KnotWright Impossible to bring into Excel. The number is coming from another software (Business management system) directly, in Word. – Anthony – 2019-12-19T01:15:37.577
(1) Could you use VBA to reformat the values after they have been inserted in Word (till now I assumed VBA wouldn't be an option)? (2) How exactly is the other software inserting the values? Is it putting them directly into the document? If so, how does it know where to put them? e.g., does it use a bookmark as a placeholder or something like that? (3) Without VBA, the only other method I can think of would be to use a DATABASE field and a bit of Jet SQL to reformat the number. That's a real kludge, and I'm not sure it is workable in the Citrix environment you describe. – None – 2019-12-21T11:40:31.010