For instance:
first column
10k
10m
10b
I need a formula to convert them and put into the second column for
10,000
10,000,000
10,000,000,000
Is it possible?
Thanks.
For instance:
first column
10k
10m
10b
I need a formula to convert them and put into the second column for
10,000
10,000,000
10,000,000,000
Is it possible?
Thanks.
Re: Formula to convert unit?
Perhaps something like this
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"b",",000m"),"m",",000k"),"k",",000")+0
If you have input like 1.2k , this formula will handle that
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"k",""),"m",""),"b","") * (10^VLOOKUP(RIGHT(A1,1),{"0",0;"b",9;"k",3;"m",6}, 2))
Re: Formula to convert unit?
Upon further reflection, this will handle 1.2k inputs:
=LEFT($A1,LEN($A1)-OR(ISNUMBER(FIND({"k","m","b"},$A1)))) * (10 ^ (10^VLOOKUP(RIGHT(A1,1),{"0",0;"b",9;"k",3;"m",6}, 2))
Re: Formula to convert unit?
Consider an extention of this kmb notation where 2kk is two thousand thousands, 2,000,000
and 1.2mb = 1.2mb = 1,200,000,000,000,000
If we select a cell in row one and define a relative referenced Name
Name: KMBformat
RefersTo: =EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A1,"k","*10^3"),"m","*10^6"),"b","*10^9"))
=KMBformat will return the value for what is in column A.
Re: Formula to convert unit?
Quote from mikerickson;592356Perhaps something like this
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"b",",000m"),"m",",000k"),"k",",000")+0If you have input like 1.2k , this formula will handle that
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"k",""),"m",""),"b","") * (10^VLOOKUP(RIGHT(A1,1),{"0",0;"b",9;"k",3;"m",6}, 2))
Thanks, this one does work.
But the other ones you gave don't work, Excel reports error and I don't know how to use the EVALUATE one either...
Don’t have an account yet? Register yourself now and be a part of our community!