I'm downloading data from a database that gives me
quantity in this format.
S 100M
B 100M
S 350M
S 5M

S=sell B=buy

I need to change this data just to give me the dollar
value.

so i need the 1st example to just read -100,000,000 and
the 2nd example to be 100,000,000, and so forth.

are there any functions or vba code to assist me in this
dilema.

thank you

=IF(A1="S",-1,1)*IF(RIGHT(B1,1)="M",LEFT(B1,LEN(B1)-1)*10^6,IF(RIGHT(B1,K1)=
"K",LEFT(B1,LEN(B1)-1)*10^3,B1))

--
Regards,
Tom Ogilvy



"Ryan" wrote in message
...
I'm downloading data from a database that gives me
quantity in this format.
S 100M
B 100M
S 350M
S 5M

S=sell B=buy

I need to change this data just to give me the dollar
value.

so i need the 1st example to just read -100,000,000 and
the 2nd example to be 100,000,000, and so forth.

are there any functions or vba code to assist me in this
dilema.

thank you

In my previous example all the text is in one cell. so,

cell A1 contains:

S 100M

cell B1 contains:

B 100M

and so forth

need to covert those cells to just take the numbers and
covert into numeric value. 100M = 100,000,000; S=sell
(negative value); B=buy(positive value)

Tks.


-----Original Message-----
=IF(A1="S",-1,1)*IF(RIGHT(B1,1)="M",LEFT(B1,LEN(B1)-1)
*10^6,IF(RIGHT(B1,K1)=
"K",LEFT(B1,LEN(B1)-1)*10^3,B1))

--
Regards,
Tom Ogilvy



"Ryan" wrote in
message
...
I'm downloading data from a database that gives me
quantity in this format.
S 100M
B 100M
S 350M
S 5M

S=sell B=buy

I need to change this data just to give me the dollar
value.

so i need the 1st example to just read -100,000,000 and
the 2nd example to be 100,000,000, and so forth.

are there any functions or vba code to assist me in
this
dilema.

thank you



.

Do text to columns from the data menu and separate the leading text and the
second text into separate columns.

--
Regards,
Tom Ogilvy

"Ryan" wrote in message
...
In my previous example all the text is in one cell. so,

cell A1 contains:

S 100M

cell B1 contains:

B 100M

and so forth

need to covert those cells to just take the numbers and
covert into numeric value. 100M = 100,000,000; S=sell
(negative value); B=buy(positive value)

Tks.


-----Original Message-----
=IF(A1="S",-1,1)*IF(RIGHT(B1,1)="M",LEFT(B1,LEN(B1)-1)
*10^6,IF(RIGHT(B1,K1)=
"K",LEFT(B1,LEN(B1)-1)*10^3,B1))

--
Regards,
Tom Ogilvy



"Ryan" wrote in
message
...
I'm downloading data from a database that gives me
quantity in this format.
S 100M
B 100M
S 350M
S 5M

S=sell B=buy

I need to change this data just to give me the dollar
value.

so i need the 1st example to just read -100,000,000 and
the 2nd example to be 100,000,000, and so forth.

are there any functions or vba code to assist me in
this
dilema.

thank you



.