Text manipulation

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

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))
 
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.
 
Do text to columns from the data menu and separate the leading text and the
second text into separate columns.
 
Back
Top