# In google sheets - how return *1.54M* as \$1,540,000 with the same formula that I would return \$1.35B* as \$1,350,000,000?

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.

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

``````=ArrayFormula(
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. 