Foreign currency format in Excel cells
Sep 19, 2006, 8:08 am
#1
Original Poster
Join Date: Sep 2004
Location: Deep in the heart of...DL country.
Programs: DL GM
Posts: 3,838
Foreign currency format in Excel cells
This is probably a stupid question, but is there any way that I can get amounts in euros to display in the French format ("," instead of decimal point) in certain cells in Excel spreadsheets?
I can't seem to find any way to format the cells to do that.
I can't seem to find any way to format the cells to do that.
Sep 19, 2006, 8:11 am
#2
Join Date: Aug 2006
Location: RTP
Programs: AA(EXP), BA, Hilton, Starwood
Posts: 1,250
Not a stupid question at all.
Highlight, right click, Format Cells, Number, use , as 1000 seperator. The adjust the decimal place showing as necessary.
There are excellent "excel tip" websites for stuff like that too.
Just remember, MS has tried three times to remove the 2^16 limit on Excel cells and failed, so it's not like it is supposed to be easy!
Highlight, right click, Format Cells, Number, use , as 1000 seperator. The adjust the decimal place showing as necessary.
There are excellent "excel tip" websites for stuff like that too.
Just remember, MS has tried three times to remove the 2^16 limit on Excel cells and failed, so it's not like it is supposed to be easy!
Sep 19, 2006, 8:22 am
#3
Original Poster
Join Date: Sep 2004
Location: Deep in the heart of...DL country.
Programs: DL GM
Posts: 3,838
I'm sorry, I'm missing something here. I'm trying to get an amount that would be, say, 30.65 if it were in dollars, to display as 30,65 because it's in euros.
But thanks for trying. Maybe I'm just more than usually dense today.
But thanks for trying. Maybe I'm just more than usually dense today.
Sep 19, 2006, 12:32 pm
#4
Join Date: Feb 2000
Location: Menlo Park, CA, USA
Programs: UA 1MM 0P, AA, DL, *wood, Lifetime FPC Plat., IHG, HHD
Posts: 6,912
possible
This is certainly possible, just go into the advanced setting for currency, IN THAT FIELD. You can either set it across the whole system, or just in the cells.
It will work in a whole column, row, or cell by cell.
It will work in a whole column, row, or cell by cell.
Sep 19, 2006, 2:05 pm
#5
Original Poster
Join Date: Sep 2004
Location: Deep in the heart of...DL country.
Programs: DL GM
Posts: 3,838
The only place I'm seeing an advanced setting for currency is in the euro conversion toolbar and, 1st, I'm not actually converting currency, and 2nd, it doesn't offer a formatting option with a "," in place of a ".".
I guess I'm still not seeing something.
I guess I'm still not seeing something.
Sep 19, 2006, 3:59 pm
#8
Join Date: Apr 2005
Location: LHR
Programs: BA Gold, TG Gold, HHonors Diamond, SPG Plat
Posts: 8,665
Choose
Format
Cell
Custom
From the menu, select 0,000.00;-0,000.00
In the space where this appears at the top of the list, literally, highlight the "." and just type in ",".
This should now appear as 0,000,00;-0,000,00
You can put in anything you want:
, or a or ~ or|
Format
Cell
Custom
From the menu, select 0,000.00;-0,000.00
In the space where this appears at the top of the list, literally, highlight the "." and just type in ",".
This should now appear as 0,000,00;-0,000,00
You can put in anything you want:
, or a or ~ or|
Sep 19, 2006, 6:46 pm
#9
Join Date: Nov 2000
Location: DFW
Programs: AA EXP/4MM, QF PLT, Marriott PLT
Posts: 1,425
Great work, KenJohn!
I'm curious though... Why would one want to mix formats on the same spreadsheet? It seems confusing -- Americans expect numbers to be in comma/decimal format and the French the opposite. I'm not sure what I'd do if I saw them mixed based on currency. (Well, I would assume there's an error on the speadsheet first off...)
I'm curious though... Why would one want to mix formats on the same spreadsheet? It seems confusing -- Americans expect numbers to be in comma/decimal format and the French the opposite. I'm not sure what I'd do if I saw them mixed based on currency. (Well, I would assume there's an error on the speadsheet first off...)
Sep 20, 2006, 2:16 am
#10
Original Poster
Join Date: Sep 2004
Location: Deep in the heart of...DL country.
Programs: DL GM
Posts: 3,838
Originally Posted by KenJohn
Choose
Format
Cell
Custom
From the menu, select 0,000.00;-0,000.00
In the space where this appears at the top of the list, literally, highlight the "." and just type in ",".
This should now appear as 0,000,00;-0,000,00
You can put in anything you want:
, or a or ~ or|
Format
Cell
Custom
From the menu, select 0,000.00;-0,000.00
In the space where this appears at the top of the list, literally, highlight the "." and just type in ",".
This should now appear as 0,000,00;-0,000,00
You can put in anything you want:
, or a or ~ or|
Sep 20, 2006, 2:21 am
#11
Original Poster
Join Date: Sep 2004
Location: Deep in the heart of...DL country.
Programs: DL GM
Posts: 3,838
Originally Posted by bollar
Great work, KenJohn!
I'm curious though... Why would one want to mix formats on the same spreadsheet? It seems confusing -- Americans expect numbers to be in comma/decimal format and the French the opposite. I'm not sure what I'd do if I saw them mixed based on currency. (Well, I would assume there's an error on the speadsheet first off...)
I'm curious though... Why would one want to mix formats on the same spreadsheet? It seems confusing -- Americans expect numbers to be in comma/decimal format and the French the opposite. I'm not sure what I'd do if I saw them mixed based on currency. (Well, I would assume there's an error on the speadsheet first off...)
Sep 20, 2006, 4:48 am
#12
Join Date: Apr 2002
Location: Paris
Posts: 577
There is a workaround, but it reformats the cell to text so you'll have to create a hidden column with the numbers with a decimal point for your calculations. Also, all numbers must be rounded to 2 places.
If your number is in cell A3, for example:
=CONCATENATE("€ ", LEFT(TEXT(A3,"# ##0.00"),LEN(TEXT(A3,"# ##0.00"))-3), ",",RIGHT(TEXT(A3,"0.00"),2))
This will give you a comma for a decimal and a space for the thousands separator.
You may need to add the format "# ##0.00" to the custom formats for cells.
If your number is in cell A3, for example:
=CONCATENATE("€ ", LEFT(TEXT(A3,"# ##0.00"),LEN(TEXT(A3,"# ##0.00"))-3), ",",RIGHT(TEXT(A3,"0.00"),2))
This will give you a comma for a decimal and a space for the thousands separator.
You may need to add the format "# ##0.00" to the custom formats for cells.
Last edited by Louie_LI; Sep 20, 2006 at 5:39 am
Sep 20, 2006, 5:41 am
#13
Original Poster
Join Date: Sep 2004
Location: Deep in the heart of...DL country.
Programs: DL GM
Posts: 3,838
Originally Posted by Louie_LI
There is a workaround, but it reformats the cell to text so you'll have to create a hidden column with the numbers with a decimal point for your calculations. Also, all numbers must be rounded to 2 places.
If your number is in cell A3, for example:
=CONCATENATE("€ ", LEFT(TEXT(A3,"# ##0.00"),LEN(TEXT(A3,"# ##0.00"))-3), ",",RIGHT(TEXT(A3,"0.00"),2))
This will give you a comma for a decimal and a space for the thousands separator.
You may need to add the format "# ##0.00" to the custom formats for cells.
If your number is in cell A3, for example:
=CONCATENATE("€ ", LEFT(TEXT(A3,"# ##0.00"),LEN(TEXT(A3,"# ##0.00"))-3), ",",RIGHT(TEXT(A3,"0.00"),2))
This will give you a comma for a decimal and a space for the thousands separator.
You may need to add the format "# ##0.00" to the custom formats for cells.
I really hate it when my software fights with me. (Especially since I'm not well-equipped to fight back.)
Sep 20, 2006, 8:00 am
#14
Join Date: Nov 2000
Location: DFW
Programs: AA EXP/4MM, QF PLT, Marriott PLT
Posts: 1,425
Wirelessly posted (BlackBerry8700/4.1.0 Profile/MIDP-2.0 Configuration/CLDC-1.1 VendorID/102)
Another idea. You could write a macro to change the format temporarily when you print. (Change the format, print, cha,ge it back)
Another idea. You could write a macro to change the format temporarily when you print. (Change the format, print, cha,ge it back)
Sep 20, 2006, 9:02 am
#15
Original Poster
Join Date: Sep 2004
Location: Deep in the heart of...DL country.
Programs: DL GM
Posts: 3,838
Originally Posted by bollar
Wirelessly posted (BlackBerry8700/4.1.0 Profile/MIDP-2.0 Configuration/CLDC-1.1 VendorID/102)
Another idea. You could write a macro to change the format temporarily when you print. (Change the format, print, cha,ge it back)
Another idea. You could write a macro to change the format temporarily when you print. (Change the format, print, cha,ge it back)