Convert to number but retain currency

[ebbo]

Hello, I have a little bit of a problem here, working with 3 worksheets:


Worksheet 1 column is called Holding (as in how many shares are you hold), this column under normal circumstances contains a number but on some occassion will contain a value held in £'s. so the column will appear something like this

1000
740
500
5000
£4000
300
£350

Worksheet 2 needs to replicate this data exactly, e.g if I have a £ symbol in worksheet 1, I must also show a £ symbol in worksheet 2. If there is no £ symbol in worksheet 1 then there should be no £ symbol in worksheet 2!

and I am trying to get worksheet 3 so that it is purely numbers, i.e no £ symbol and everything should be stored as numbers! (worksheet 3 connects to an external file that can only read numbers!)

As an additional test i want to see if my external connection will recognise a "currency" i.e something with a £ followed by numbers (£4000) purly as a number, so in worksheet 4 i would like everything to appear exactly as in worksheet 1 but everything should be a number regardless of whether it contains a £ symbol or not. Possible?!

Please see attachment for a clearer idea

many thanks

[NBVC]

If you select Sheet1!A2:A5 and copy..

Then select Sheet2!A2:A5 and go to Edit|Paste Special and select Formats...

..you will have same formats.. is that ok?

A formula solution would be:

=TEXT(Sheet1!A2,IF(CELL("format",Sheet1!A2)="G","0","£ 0"))

right aligned and copied down... but this is text...