Travel Technology - 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.

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!

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.

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.

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.

Yes, I think I have some regional and language support loaded in my office install. I think it is from the MSFT website though.

You can go to Tools > Options > International and change it Excel-wide. This may not be what you want, though.

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|

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...)

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|

When I do that "1,892.83" displays as "1,893".

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...)
It's because of this weird project I'm doing. I'm paying for materials and labor in euros, but I'm actually costing it for myself in dollars. So I want to be able to print the euro parts for artisans and suppliers in the format they're used to seeing.

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.

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.

Thanks, I'll try that. If that works it will at least save me from manually entering each cell as text for printing, which is what I'm doing now.

I really hate it when my software fights with me. (Especially since I'm not well-equipped to fight back.)

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)

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)

I'm extremely flattered that you think I would know how to write a macro. I must not sound quite as technologically ignorant as I am.

Another variation:=SUBSTITUTE(TEXT(A1, "€### ##0.00"),".",",")This formats it, then changes the decimal point to a comma.