Formula with text and numerics
 

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

Formula with text and numerics
 

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

Formula with text and numerics
 

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

Formula with text and numerics
 

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