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

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
|
|

If the pound sign is your local currency sign in Excel, you can use
VALUE(A1)
instead of
VALUE(RIGHT(A1,LEN(A1)-1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message ...
| 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
||
||
|
|

Hello Niek

Thanks for your reply ... worked a treat!

Cheers
Les




"Niek Otten" wrote in message
...
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
|
|