1

I've been looking for a formatting solution to do the opposite of what most try to do. Turning shortened numbers like 7.51M into 7510000 or 7,510,000. Or in thousands: 135.6K into 135600.

Update:
Found this statement which basically works, but I'd rather do it with formatting if possible.

=if((right(H6,1))="K",(value(left(H6,(len(H6))-1)))*1000,if((right(H6,1))="M",(value(left(H6,(len(H6))-1)))*1000000,if((right(H6,1))="B",(value(left(H6,(len(H6))-1)))*1000000000,H6)))

4
  • Try Ctrl+Shift+1, it's standard number formatting would return 7,510,000.00. Or go to More formats (123 button on the ribbon) / More formats / Custom number format and choose from pre-made options or write something that would fit your need. e.g. #,##0 – Oleg_S Mar 19 at 8:48
  • @Oleg_S, I appreciate your quick response. I've tried some of the pre-made options, none of them changed it. Neither using CMD + Shift + 1 (I'm using Mac) – Bar Malka Mar 19 at 8:59
  • 2
    Got it. It didn't work because your value is non-numeric. I thought it was just change format issue. If it's string value formatting won't help to convert it to number. Option you described is the way to do it – Oleg_S Mar 19 at 10:22
  • Check this answer out webapps.stackexchange.com/questions/57716/… Maybe what you need? – Craig Lambie Mar 19 at 15:57
2

I agree with the above comments and the assessment that there is no formatting that can turn a string into a number. However, I have some thoughts for you.

First, this formula will be a little more flexible and succinct:

=IFERROR(REGEXEXTRACT(H6,"[\d\.]+")*10^IFERROR(VLOOKUP(REGEXEXTRACT(UPPER(H6),"K|M|B"),{"K",3;"M",6;"B",9},2,FALSE),1),H6)

Secondly, if you are needing to apply this change to many cells, it would be best to use an array formula (i.e., one formula that can process an entire range in the same way). If that is the case and you are interested, report back with the range to which such a formula would apply, and I or someone else can adapt the formula to work as an array formula.

2
  • Your formula is very nice, it does a similar job as that giant IF statement I wrote. I will mark this as the answer. This is enough for my usage of it :) Thank you. – Bar Malka Mar 20 at 9:36
  • 1
    Glad it can be of use. And thanks for closing the loop by marking it as "Best Answer." This makes it easy for volunteer contributors here to see at a glance from the main listings which issues have already been resolved, as well as making it easier for future site visitors to find the answers that may best parallel their own issues. – Erik Tyler Mar 20 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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