Home » Forums » Open Source Learning Center » Question and Answer

Converting all occurences of $ to £

Submitted by Davinder28 on 17 July, 2011 - 14:10

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.

‹ dynamic concatenation BIG Excel Size issue ›
»
Vishesh's picture

In find/replace dialog box

Submitted by Vishesh on 19 July, 2011 - 05:47.

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 £

Submitted by Anonymous on 17 July, 2011 - 22:38.

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

Submitted by Davinder28 on 17 July, 2011 - 23:03.

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 £

Submitted by Anonymous on 18 July, 2011 - 13:10.

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

Submitted by Anonymous on 18 July, 2011 - 15:21.

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

»
Almir's picture

Is it formatting or cell contents issue?

Submitted by Almir on 24 February, 2014 - 19:48.

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

Submitted by Nick on 18 July, 2011 - 18:36.

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

»