Dear forum users,

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

Large numbers are commonly abbreviated with binary prefixes, especially
in finance. By binary prefix, I am referring to the use of a single
letter to represent the order of magnitude of a number. For instance
"K" represents a thousand, and "M" denotes a million. e.g. The
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 the
moment, Excel is just treating this as text. Does anyone know how to do
this?

Thank you in advance for your help.


--
Dogbert
------------------------------------------------------------------------
Dogbert's Profile: http://www.excelforum.com/member.php...o&userid=35338
View this thread: http://www.excelforum.com/showthread...hreadid=551072

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

"Dogbert" wrote:


Dear forum users,

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

Large numbers are commonly abbreviated with binary prefixes, especially
in finance. By binary prefix, I am referring to the use of a single
letter to represent the order of magnitude of a number. For instance
"K" represents a thousand, and "M" denotes a million. e.g. The
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 the
moment, Excel is just treating this as text. Does anyone know how to do
this?

Thank you in advance for your help.


--
Dogbert
------------------------------------------------------------------------
Dogbert's Profile: http://www.excelforum.com/member.php...o&userid=35338
View this thread: http://www.excelforum.com/showthread...hreadid=551072

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,10 00,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).
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dogbert" wrote in
message ...

Dear forum users,

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

Large numbers are commonly abbreviated with binary prefixes, especially
in finance. By binary prefix, I am referring to the use of a single
letter to represent the order of magnitude of a number. For instance
"K" represents a thousand, and "M" denotes a million. e.g. The
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 the
moment, Excel is just treating this as text. Does anyone know how to do
this?

Thank you in advance for your help.


--
Dogbert
------------------------------------------------------------------------
Dogbert's Profile:
http://www.excelforum.com/member.php...o&userid=35338
View this thread: http://www.excelforum.com/showthread...hreadid=551072

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","00 0000"))

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

"Dogbert" wrote:


Dear forum users,

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

Large numbers are commonly abbreviated with binary prefixes, especially
in finance. By binary prefix, I am referring to the use of a single
letter to represent the order of magnitude of a number. For instance
"K" represents a thousand, and "M" denotes a million. e.g. The
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 the
moment, Excel is just treating this as text. Does anyone know how to do
this?

Thank you in advance for your help.


--
Dogbert
------------------------------------------------------------------------
Dogbert's Profile: http://www.excelforum.com/member.php...o&userid=35338
View this thread: http://www.excelforum.com/showthread...hreadid=551072

Thank you Jerry, Bernard, and Paul for your kind replies, each with your
own unique way of dealing with the problem. I shall try all three to see
which one would suit me best, but these are all good solutions. I am
just disappointed that Excel does not have some built in function for
something so simple, and something that I think a lot of people come
across.

Jerry raised the issue of whether "these" are binary prefixes or not.
In fact, they are prefixes, as they are normally placed in front of
units, such as "kg" and "cm". In the case of finance, they are not
placed next to the unit, i.e. the currency, because the financial world
likes to have its own peculiar notations. I suppose in scientific
notation, one thousand dollars whould be written as "1 k$" and not the
now accepted "$1K". Whether it is binary or not, I don't know. Perhaps
decimal prefix would be better? As for the ambiguity of whether "K" is
1,000 or 1,024, it is commonly recognised that "K" is kilo, which is
1,000, while "Ki" is kibi, which is 1,024.

However, like Bernard, I do admit I am not completely sure that binary
prefix is the most appropriate name for these things. There is probably
a more apt name out there, but I don't know it. Binary prefix was the
first thing that came to my mind, as it drifted back to electronic lab
days. As for a billion being a "million million" in the UK, that is no
longer the case. The UK now go with the US standard, which, for once,
makes more sense, unlike its obstinate refusal to use the SI / metric
system.

Once again, thank you all for your help.


--
Dogbert
------------------------------------------------------------------------
Dogbert's Profile: http://www.excelforum.com/member.php...o&userid=35338
View this thread: http://www.excelforum.com/showthread...hreadid=551072