Google-sheets – Formula for custom number format in Google Sheets (e.g. 5M –> 5,000,000; 50k –> 50,000; etc)

formattingformulasgoogle sheets

I've been trying to figure this out for a bit but everything I've tried doesn't work. A quick search brought me to the conversion from full number format to condensed format (5,000,000 –> 5M; 50,000 –> 50k), however I need to do the reverse conversion (5.4M –> 5,400,000; 50k –> 50,000). I'm being given in the number in the condensed format with "B", "M", and "k" but I need to type out the full number which is becoming a right pain.

Anyone have a formula for this conversion?

Best Answer

unfortunately, this isn't supported mostly because of unknown round-down inaccuracy (eg. 1k can be 1000 or 1002 or 1099 etc.)

closest you can get is to use +1 column with a formula like:

=IF(RIGHT(A1;1)="K"; VALUE(REGEXREPLACE(A1; "\D+"; ""))*1000;
 IF(RIGHT(A1;1)="M"; VALUE(REGEXREPLACE(A1; "\D+"; ""))*1000000;
 IF(RIGHT(A1;1)="B"; VALUE(REGEXREPLACE(A1; "\D+"; ""))*1000000000;)))

and format that column as #,##0 to get:

Code

var curDate = Utilities.formatDate(new Date(), "GMT+1", "MM/dd/yyyy")

References

Conditional Formatting in Google Sheets: Highlight Cells with ‘False’

Short answer

FALSE and TRUE are Google Sheets keywords, they represent the respective boolean values. To use them as text, use ' as prefix, In the regular expression change False to FALSE.
The "Apply to range" start cell should be L2 instead of L1.

Explanation

Apply to range

The relative references in custom formatting formulas takes the start cell as the pivot. As the values FALSE/TRUE/Unsure start on row 2, on Apply to range instead of using L1 as the start cell, use L2.

Custom formula

The formula

=(sum(arrayformula(n(regexmatch($L2:$P2, "False")))) = 5)

returns #VALUE! and the following error description:

Error
Function REGEXMATCH parameter 1 expects text values. But 'FALSE' is a boolean and cannot be coerced to a text.

Note: To see the above error message in a Google Sheets spreadsheet, add the above formula to any cell not in the columns L-P

As REGEX is case sensitive the formula to use is:

 =(sum(arrayformula(n(regexmatch($L2:$P2, "FALSE")))) = 5)

An alternative formula to avoid the use of prefix is the following

 =ARRAYFORMULA(SUM(IFERROR(IF($L2:$P2,1,0),1))=0)