Hi Leslie,

Can't you just test for the pound sign?
I used $ because I had that readily available:

=IF(LEFT(A1,1)="$",VALUE(RIGHT(A1,LEN(A1)-1))*2,"")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Leslie Isaacs" wrote in message ...
| Hello All
|
| I have a 3rd party application from which I will be regularly generating
| excel worksheets, in which one of the columns contains text values (~10%)
| and numeric values (~90%). Each cell in the column 'comes in' as a text
| value: the 90% that represent curency values are displayed as "£160", etc.,
| left-justified: these value always start with a £ sign.
|
| My problem is that I need to add a new column with a formula that will use
| these values where they represent a currency amount, but which will result
| in a 'blank' being displayed (not #value, etc) where the value in the
| problem column is genuinely text. Effectively, if the problem column is A, I
| need something like:
|
| =If(<A1 contains any characters between a and z or A1 is blank,"",<extract
| the numeric value of A1 and double it)
|
| ... but I cannot find the syntax for the <A1 contains any characters between
| a and z or A1 is blank and <extract the numeric value of A1 and double it
| expressions above!!
|
| Hope someone can help.
| Many thanks
| Les
|
|