Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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?
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:
