Experts needed: Format number in MERGEFIELD

Question

Hi, 

I've spent some hours searching the forums, and could not find an answer to this challenge until now, so I'm posting the question here. I am setting up a document template to create invoices. This template gets imported into an English accounting software. The challenge is, that I am trying to create a number format used commonly across Europe, where the comma is used instead of the decimal as the thousands splitter.

e.g 1,000.00 in English (in German - a German person might read this number as 1 euro + 5 decimal points. Very confusing!) 

=

1.000,00 € in German / Dutch / Many more EU languages 

The challenging part, is normally one would solve this with the document language. But this doesn't work, because the system this document gets imported into, runs the document language as English. Therefore, the . and , conversion needs to be done within the MergeField tag. 

So far, the closest I have come to solving this is: # "# ##0',00 €';

This outputs a number as : 1 000,00 €

However, this is really a hack. Because the ,00 € is actually always fixed. So, it cannot work because numbers which have decimals just simply arent shown. So the number in reality is 1 000,50. Makes a big difference in a tax invoice -> wrong tax summary! 

I'm wondering if anyone knows a solution, which does not involve changing the language of the document, to basically format the MergeField tag to output a close or similar number with a switched , and . seperators. 

E.g one idea that I had, but no idea how that works: I am wondering if its possible to have two parts within the MergeField tag, the first part before the decimal, and the second part after e.g 1 000 (Part 1) and ,05 € (part two) which then shows the number together as 1 000,05 € 

Last point - this number field is purely visual. I.e its not relevant if the final number / format is machine readable etc.. because its only used to render the invoice, not for anything else. 

Thank you!

Answer 1

Hi, have you tried it:

#,##0.00 [$€-de-DE]

In my Excel 2016, i've entered A1 = 1000 and then just selected Format Cells ~ Currency ~ Symbol as "€ German (Germany)" and the result is:

I hope this will help you.

Answer 2

Hi Faysal - thanks for the reply. 

Unfortunately this has not helped. 

My format formula was: # "# ##0.00  [$€-de-DE];

Unfortunately this does not seem to parse via the MergeField. 

Also, in my tests in excel, it only changes the € sign, but not switch the comma and decimals as needed. 

The formula you suggested results in this output in the document (see top right). 

The desired number format that I need is shown on the left. 

At the bottom however you see the problem with my current format.

All of the 'cents' are disappearing. 

Answer 3

German system:

Cell format:

#.##0,00 €

If that file is opened in an English system:

Format becomes automatically:

#,##0.00 "€"

Andreas.

Answer 4

Hi Andreas, 

Thanks for your reply. 

I'm aware that this formula by itself is correct - but the fact that the format 'automatically' changes depending on the system language is exactly the problem. 

Because the system where this document gets imported into, is an English system. 

So - I am looking for a way to 'force' the formatting to be done in German, locally, within the document / within the field. 

Any ideas?

Answer 5

So - I am looking for a way to 'force' the formatting to be done in German, locally, within the document / within the field. 

You want to show 1234.56 Euro as 1.234,56 € on an English system?

That is not possible with a formula nor value in a cell. You have to convert the currency to Text.

Use the macro below to convert selected cells.

German system:

English system:

Andreas.

Sub ConvertToText()

  Dim R As Range

  For Each R In Intersect(ActiveSheet.UsedRange, Selection)

    R = R.Text

  Next

End Sub