MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to Change British Pound Sign to $ Sign


Posted by Bobby on July 05, 2001 1:53 PM

Is there a macro that will change the formatt of a British Pound Currency to USD$ and back to british pound.


Posted by Scott on July 05, 2001 2:02 PM

Do you want to convert the British Pound to a USD$, or just reformat the cell (another words, change British Pound 1.00 to $1.00)?

Posted by Bobby on July 05, 2001 2:06 PM

Just change British Pound 1.00 to $1.00.
What happens is the data is a BP but my template might be formatted as a USD.
I want to be able to switch between US and BP on the same template without having to manually change the formatt

Posted by Scott on July 05, 2001 2:55 PM

Here is a simple macro that you can use to switch back and forth:

Sub Macro1()

Columns("A:A").Select
If Selection.NumberFormat = "$#,##0.00" = True Then
Selection.NumberFormat = "[$£-809]#,##0.00"
Else: Selection.NumberFormat = "$#,##0.00"
End If
Range("a1").Select

End Sub

This is assuming that your data is in Column A. It selects the entire column and changes the format to either Pounds or $, which ever one it is not. If you only want to select a range, you can replace "Columns("A:A").Select" with "Range("a1:a10").Select", A1:A10 being the range that you want to change.


Posted by Joe Was on July 05, 2001 2:59 PM

Hot-key VB Code

Copy & Paste the macros below to your Macro module (Macro-Macros, Edit or Create).

If you then set the Macro - Option to a Hot-key you can convert the format back and forth.

Note: This will only reformat the active cell, it does not convert the value. With this code; 1 pond will = $1.00 it will not convert 1 pond to $2.31 lets say!

If you set the Hot-key codes as below then if you have a value in the active cell and hit "Ctrl-p" no-matter what format the value was in it will be in ponds to two places. If you hit Ctrl-d then no-matter what format the value is in it will now be in $0.00 format. Hope this helps. JSW

Sub Pound()
'Macro-Options assign Hot-key =p
ActiveCell.NumberFormat = "[$£-809]#,##0.00"
End Sub

Sub Dollar()
'Macro-Options assign Hot-key =d
ActiveCell.NumberFormat = "[$$-409]#,##0.00"
End Sub

Posted by Bobby on July 06, 2001 6:22 AM

Thanks Scott it works great No Msg...