0

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?

|improve this question
0

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:

enter image description here

|improve this answer

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.