Converting UK Currency to Euro format

[Daithi]

Hello

I posted this yesterday but I think it was in the wrong place. Sorry.

I have a worksheet where all the values are written in UK format, e.g.

10,000,000.00
7,500.12
100.00

I need to have them in Euro format, i.e.

10.000.000,00
7.500,12
100,00

The built in Currency Formatting isn't working for me. It's putting an extra 2 zeros in the value. Nor is Find/Replace.

Hope some one can help


Best

David

 

[Gerald Higgins]

Try this.

File
Options
Advanced
Untick the box for "Use system separators"
Then in the box for "decimal separator", you probably have the "." character, replace this with ","
And similarly for "thousands separator", replace "," with "."

 

[Daithi]

Gerald....Thank You!!!

Your solution works perfectly

I have tried to reply before but each time I tried to send I got logged out.

A small follow-up question. If there are zeros following the decimal point, in the formula bar, I don't see the zeros. But if there are numbers there I see them. I'm just curious why this is.

Best

David

 

[Gerald Higgins]

The formula bar shows the underlying value that Excel is holding, and this disregards any formating that may have been applied to the cell.

For example, if you enter the value
1
in a call, you can format this to display in the cell as
1.00
but the formula bar always just shows the
1
That's just the way Excel is, and I think knowing that can be helpful.
On this board there are quite often questions from people who are confused about why their cell is displaying a different number from what they expect, and the reason is often cell formating.
Comparing the formula bar with the cell's appearance can give you a clue to this.

 

[Daithi]

Thanks Gerald...Makes sense...very clear answer.

Best

David