I am building a stock model - where I use
=importhtml() to give me financial data pulled from a website on certain stocks.
It is returning it correctly, but there are asterisks surrounding all the data as seen below:
Ticker Market Cap Income *AAPL* *1151.68B* *57.53B* *ABT* *144.46B* *3.60B* *AMD* *58.17B* *328.00M*
(no actual spaces are between asterisks - just avoiding HTML )
So I am taking that data and using
=if(B3="*-*",0,mid(B3,2,LEN(B3)-2)) to get rid of the asterisks:
Ticker Market Cap Income AAPL 1151.68B 57.53B ABT 144.46B 3.60B AMD 58.17B 328.00M
That's working - but I want it to be able to recognize that there is either a "B" or an "M" at the end, take that char off and then multiply it by either a billion or a million, depending.
I can take my second asterisk-free table and then make a THIRD table using:
=if(right(B95,1) = "B",LEFT(B95,Len(B95)-1)*1000000000,if(right(B95,1) = "M", LEFT(B95,Len(B95)-1)*1000000))
That does the trick. But I would like figure out how to merge these two into one command so I don't have to make the third table.