Format a number into the French format

[almurray]

Hi everyone,


I was asked the other day how a number currently shown as 1,234.56 could be formatted to the French equivalent of 1 234,56.


I thought 'oh that sounds easy' and went straight to custom format. I can put a space instead of the comma as the thousand seperator, but I cannot change the decimal point to a comma.


To make things more difficult the lady needs to see different currencies on the same sheet. So she will see US dollars, the GBP and French Euros on on one sheet so I need to format specific cells.


I found this link which sounded promising but did not work for me.


http://office.microsoft.com/en-us/excel-help/creating-international-number-formats-HA001034635.aspx


Any help would be much appreciated.


Alan

 

[magbo]

Go to menu Tools->Options ->International and there set decimal & thousands separators

 

[fred]

hi Magbo, I guess you are suggesting the recipient of the spreadsheet to do this, right?


I have been sending and receiving files to/from Europe for a number of years. In the 2003 version on both sides, the number format would remains the same as in the origin point, when a receipient opens up the file sent to him/her. And vice versa, all excel files originated from european excel will have a format #.###,## I know it may not look right to us or right to them on US files, we just have to let it go.


Now that the office is using 2007 i have not asked my colleagues across the sea if they have changed to #,###.## format. I don't think so because there are other local german readers as well. I tried looking on the web but i can't find anything that explicitly stated that all European Excel in #.###,## format is automatically converted into #,###.## format.

 

[asa]

http://office.microsoft.com/en-us/excel-help/creating-international-number-formats-HA001034635.aspx

 

[asa]

OK, well the link above is great for showing dates, times and the like according to locale, but nothing on changing the decimal separator. My Google search seems to indicate the option does not exist as a cell format. You can change the computer's regional setting, or change the Excel application's regional settings override as magbo suggested.


Alternatively, you can put a macro in your workbook that changes the Excel regional settings.


That's interesting that you experienced Excel files retaining the source-point regional settings with the decimal separator. I don't see any information about that.


Asa


http://www.excelforum.com/excel-general/772631-excel-international-decimal-separator.html

 

[asa]

Sorry; that link is informative about applying locale, calendar, and numeral shape settings to number formats, but NOT to change the decimal separator.


My searches only turned up the suggestion to use a VBA function to change Excel's overall setting (the setting pointed to by magbo). But that would change the user's settings, even if temporarily, and not a great idea in my view... plus it would change it for all displayed numbers, not just the applicable currencies. Well, then there is the option to use a formula.... if the number is in cell A1, say, you could hide that column, and have in B1 something like:

=SUBSTITUTE(TEXT(TRUNC(A1),"[$€-2]#,##0"),",",".")&","&RIGHT(TEXT(A1,".00"),2)


Which will give you a text string starting with the "-" sign if applicable, the euro symbol, and then the amount with "." as a thousands separator and "," as the decimal separator.


Asa


edit:

Here's a version that will carry over blank cells and text as-is.  The first version would return 0,00 for blanks and #VALUE for text.  Both versions will show the same error if the original cell has an error in it, like #NA.

=IF(ISNUMBER(A1),SUBSTITUTE(TEXT(TRUNC(A1),"[$€-2]#,##0"),",",".")&","&RIGHT(TEXT(A1,".00"),2),T(A1))

p.s. changed this new formula after initial "edit" to be shorter uses T() function to eliminate some extras..

 

[NARAYANK991]

Hi Alan ,


asa's formula is correct , but you will not be able to use these cells in arithmetic operations without again converting them to their right values.


Narayan