Thousands as K, Millions as M, Billions as B

In excel I want to specify a custom format that will display thousands as "k", millions as "M", and billions as "B", for positive and negative values. All at the same time. For example

$4,100 -> $4.1 k
-$1,200,000 -> -$1.2 M
$3,800,000,000 -> $3.8 B

I have found ways to do this for just millions or just thousands, but no way to combine all formats. In addition no tutorials tell how to deal with the negative numbers. Is this possible without dipping into VBA?

Hi TestMailinator,

I hate to point you to some other site, but I think you should read the answer at:
http://www.mrexcel.com/forum/excel-q...ormatting.html

well, if you have essentially fixed values, thousands, millions, billions this would work w/o vba...
=IF(LEN(A1)=4,"$"&LEFT(A1,1)&"."&LEFT(RIGHT(A1,3),1)&"k",IF(LEN(A1)=7,"$"&LEFT(A1,1)&"."&LEFT(RIGHT(A1,6),1)&"M",IF(LEN(A1)=10,"$"&LEFT(A1,1)&"."&LEFT(RIGHT(A1,9),1)&"B"))) But if you get into tens of thousands and hundreds of thousands tens of millions then setting up a reference table would be better.

Please Login or Register  to view this content.

here is a tweek that would work if you have thousands up to hundreds of billions.
BUT it relies on putting a formula for length in another column, in this case cell D1 =LEN(A1)
Then referring to that, this in another cell...
=IF(D1=4,"$"&LEFT(A1,1)&"."&LEFT(RIGHT(A1,3),1)&"k",IF(D1=5,"$"&LEFT(A1,2)&"."&LEFT(RIGHT(A1,3),1)&"k",IF(D1=6,"$"&LEFT(A1,3)&"."&LEFT(RIGHT(A1,3),1)&"k",IF(D1=7,"$"&LEFT(A1,1)&"."&LEFT(RIGHT(A1,6),1)&"M",IF(D1=8,"$"&LEFT(A1,2)&"."&LEFT(RIGHT(A1,6),1)&"M",IF(D1=9,"$"&LEFT(A1,3)&"."&LEFT(RIGHT(A1,6),1)&"M",IF(D1=10,"$"&LEFT(A1,1)&"."&LEFT(RIGHT(A1,9),1)&"B",IF(D1=11,"$"&LEFT(A1,2)&"."&LEFT(RIGHT(A1,9),1)&"B",IF(D1=12,"$"&LEFT(A1,3)&"."&LEFT(RIGHT(A1,9),1)&"B")))))))))

Other than Marvin I think we've all gone off on a tangent, the OP was looking for a custom format not formula

PJW, you're right, I concentrated more on not "VBA" than I did on the formatting issue. Of course I did look initially at formatting and didn't see it as a possibility offhand.
BUT I had fun trying to get the formula to work so... even if TestMailinator doesn't want it, it was fun developing it.

Row\Col	A	B	C
1	4,100	4.1 K	B1: =A1
2	-1,200,000	-1.2 M
3	3,800,000,000	3.8 B

Three conditional formatting rules:

=ABS(Me)>1000 format as 0.0 K

=ABS(Me)>1000000 format as 0.0,, \M

=ABS(Me)>1000000000 format as 0.0,,, \B

Replace Me with the address of the cell.

Originally Posted by Sambo kid

PJW, you're right, I concentrated more on not "VBA" than I did on the formatting issue. Of course I did look initially at formatting and didn't see it as a possibility offhand.
BUT I had fun trying to get the formula to work so... even if TestMailinator doesn't want it, it was fun developing it.

yeah, I did the same!