Recognition of Binary Prefix Numerical Format

[Dogbert]

Dear forum users

There is a problem I wonder if you could help me with. Perhaps it is
simple matter but I have been unable to find the answer

Large numbers are commonly abbreviated with binary prefixes, especiall
in finance. By binary prefix, I am referring to the use of a singl
letter to represent the order of magnitude of a number. For instanc
"K" represents a thousand, and "M" denotes a million. e.g. Th
population of the US is around 300M (or 0.3B)

So when I have a column of numbers such as "... 8000; 9000; 10K
11K...", I want Excel to convert or recognise the value of "K". At th
moment, Excel is just treating this as text. Does anyone know how to d
this

Thank you in advance for your help

 

[Guest]

The "M" in 300M is neither binary nor a prefix.

I know of no native format that will recognize and interpret such inputs as
numbers, but you can easily write a formula to covert them, such as
=IF(RIGHT(cel,1)="M",LEFT(cel,LEN(cel)-1)*10^6)
You could nest IF statements to recognize up to five possible postfixes.
For more you could use VLOOKUP to get the values from a table.

"K" is an ambigous postfix, that may either mean 1000 or 1024=2^10.

Jerry

 

[Bernard Liengme]

Not extensively tested but this seems to work:
=IF(ISNUMBER(VALUE(RIGHT(A1,1))),A1,VALUE(MID(A1,1,LEN(A1)-1)*LOOKUP(RIGHT(A1,1),{"B","K","M"},{1000000000,1000,1000000})))

Not sure if 'binary prefix' is correct name for these symbols. Note also
that in UK, 'Bm' for billion means 10^12 (million milllion) not 10^9
(thousand million).

 

[Guest]

One potential solution involves the use of the "Substitute" function. Let's
assume that you have the value "10K" in cell A1 (the other data is also in
column A). What you'd like is to replace "10K" with "10000". You can do
this as follows (I'll assume that the result will reside in cell B1):

B1 =SUBSTITUTE(A1,"K","000")

We also would like to set the "M" suffix to "000000". So modify the above
formula to:

B1 =SUBSTITUTE(SUBSTITUTE(A1,"K","000"),"M","000000")

Okay, so that takes care of the thousands and millions. The result of the
substitute function is a text string that looks like a number. We'll need
that string to actually be a number:

B1 =VALUE(SUBSTITUTE(SUBSTITUTE(A1,"K","000"),"M","000000"))

Now copy this formula down for all data items in column A and you should be
good to go.