Text manipulation
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 |
Text manipulation
=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 |
Text manipulation
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 . |
Text manipulation
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 . |
All times are GMT +1. The time now is 03:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com