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.

1 Answer 1


Here's a way to do it with one formula :

if(REGEXMATCH(transpose({regexextract(A2,"[\w]*"),regexextract(A2,"[\w]* ([\w|\.]*)"),regexextract(A2,"[\w]* [\w|\.]* ([\w|\.]*)")}),"A"),
transpose({regexextract(A2,"[\w]*"),regexextract(A2,"[\w]* ([\w|\.]*)"),regexextract(A2,"[\w]* [\w|\.]* ([\w|\.]*)")})
if(REGEXMATCH(transpose({regexextract(A2,"[\w]*"),regexextract(A2,"[\w]* ([\w|\.]*)"),regexextract(A2,"[\w]* [\w|\.]* ([\w|\.]*)")}),"[0-9]B"),
regexextract(transpose({regexextract(A2,"[\w]*"),regexextract(A2,"[\w]* ([\w|\.]*)"),regexextract(A2,"[\w]* [\w|\.]* ([\w|\.]*)")}),"[^B]*")*power(10,9),
if(REGEXMATCH(transpose({regexextract(A2,"[\w]*"),regexextract(A2,"[\w]* ([\w|\.]*)"),regexextract(A2,"[\w]* [\w|\.]* ([\w|\.]*)")}),"[0-9]M"),
regexextract(transpose({regexextract(A2,"[\w]*"),regexextract(A2,"[\w]* ([\w|\.]*)"),regexextract(A2,"[\w]* [\w|\.]* ([\w|\.]*)")}),"[^M]*")*power(10,6)

You can replace the spaces in the regexp with \* if your data is always the same.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.