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

Question

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?

Answer 1

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: