Google-sheets – 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

google sheets

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.

Best Answer

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.

Short answer

From the question:

edit: where trying to use an implementation of the script where the result satisfies a condition, I'm not certain but I think the sheet "views" the formula input as giving a result, albeit blank? So not a value of "0"

Instead of something like

values[i][0] > 0

you should use

values[i][0] != ''

Explanation

Part 1: Misconception about blank cells and empty strings.

Your formula doesn't return a blank, it returns a empty string "". A blank cell is coerced to 0 always but a empty string doesn't.

Example:
If A1 has the following formula ="", =ISNUMBER(A1) returns FALSE but =A1+1 returns 1.

By the other hand, on Google Apps Script a blank cell value is handled as an empty string, not as 0 (zero).

Google Apps Script is based on JavaScript no on the "spreadsheet thinking" so there are several things like the default value of a blank cell that are handle in a different way than it's on Google Sheets.

Part 2: One practice for Google Apps Script troubleshooting

In case of doubts, a general practice is to create simple test functions that return the result to the log but in the case of Google Sheets you could use a custom function if you feel more comfortable with the Google Sheets UI than with the Script Editor.

To check the value that Google Apps Script got from an empty string you could use something like the following custom function:

/**
 * Returns a 2D array 
 *
 * @param {"A1"} input Cell reference string
 * @customfunction
 */
function valueDataType(input){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange(input);
  var value = range.getValue();
  var output = [  
     ['Value: ' + value],
     ['Data type: ' + typeof value]
  ];
  return output;
}

Best Answer

Related Solutions

Google-sheets – Return multiple rows with same value in first column

Google-sheets – Replace formula with result, but exclude formulas that return blanks

Short answer

Explanation

Part 1: Misconception about blank cells and empty strings.

Part 2: One practice for Google Apps Script troubleshooting

Related Topic