Formula with text and numerics

[Leslie Isaacs]

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

 

[Niek Otten]

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

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

 

[Niek Otten]

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

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

 

[Leslie Isaacs]

Hello Niek

Thanks for your reply ... worked a treat!

Cheers
Les