We help IT Professionals succeed at work.

using currency and % formats in excell formulas

BARTHRBD
BARTHRBD asked
on
I developped my models starting with excell 97

I have plenty of formulas in which I use : ... &text(cell reference;"#.##0,00 €").

This is a common  format in Belgium to show values in €-currency as e.g. 1.234.567,89 €

Recently, they sold my a new pc with windows 8 and  excell 2013 . In this version this doesn't work. The result is a kind of nonsens text, not even a value.

The same problem occurs with : ... &text(cell reference;"#.##0,00 %"), This is a common used format in Belgium to show values in €-currency as e.g. 12,34 %

Thx for your help
Comment
Watch Question

This may just be the settings in your new install of Excel.

First, fix decimal seperator:
- In Excel, click File
- Click Options
- Click Advanced
- In "Editing options" uncheck "Use system separators"
- Change "Decimal separator" to a comma
- Change "Thousands separator" to a period

Your formula should then be: =TEXT(B9;"#.##0,00 €")

If B9 = "912548333"

the formula will display:  "912.548.333,00 €"

As an example.

I think from your post this is what you were trying to do.
Commented:
Try to use this

=TEXT(B7,CONCATENATE("#.##0,00",CHAR(128))) in your formula
Most Valuable Expert 2011
Top Expert 2011
Commented:
You could also just change the regional settings in the Windows Control Panel so that they are set to Belgium.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.