Converting UK Currency to Euro format

Daithi

New Member
Joined
Feb 28, 2014
Messages
15
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Feb 28, 2014
Messages
15
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,733
Messages
5,488,551
Members
407,646
Latest member
utl1095

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top