Converting all occurences of $ to £

I have a large excel equity valuation spreadsheet, cells with dollar entries are scattered everywhere and mixed up (in rows/columns) with other types of formatting like % etc.

How do I convert all occurences of $ to £?

I've tried find and replace but it doesn't work.

Vishesh's picture

In find/replace dialog box

In find/replace dialog box goto options; then select find format and replace format. Choose currency type. Do replace all.

VBA code for the same is

Application.FindFormat.NumberFormat = "[$$-409]#,##0.00"
Application.ReplaceFormat.NumberFormat = "[$£-809]#,##0.00"
Cells.Replace What:="", Replacement:="", LookAt:=xlWhole, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

convert $ to £

you need a cell with the exchange rate.
- enter the rate
- copy the cell
- select all the cells you want to change (use CTRL to select multiple cells)
- then go: edit => paste special => mulitply

There are > 1000 entries that

There are > 1000 entries that can't be selected easily contained in around 70 different tables mixed up with cells that I don't want to have changed in any way.

I need a better method :(

convert $ to £

are all the cells formatted the same way ?
..if so, it's possible to do it with VBA..
Are the values fixed, or are they formulae.. or mixed ?

Formatting is mixed and so

Formatting is mixed and so are the values, constants and formulas.

Almir's picture

Is it formatting or cell contents issue?

Are you sure that cells actually contain currency symbol? If the cells were just formatted to show $, Replace command can not change them because format changes only appearance, but not the actual value in the cell.

Nick's picture

so if you were to do it

so if you were to do it manually, how would you identify the cells that need to be changed ?