Hi, am looking for way to "CUSTOM FORMAT" a cell to read 1.511B or 1.511M
as: 1B 1M (both B for billion & M.. present in column, thanks)

(CUSTOM FORMATTING !!: Right-Click cell, FORMAT CELLS.., NUMBER TAB,
CUSTOM..

Modify / correct this line:
Just looking for custom formatting such as: #,##0? ("?" for
wildcarding B or M)

Is this an area not possible for custom formatting in Excel?

separate work column might not work for me, but (THIS WORKS):
=TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)

The Text format is probably the best option, as in normal formatting,
once a digit is encountered the following zeros are not suppressed, so
a custom format of ####"B "##0"M" will display such as:

B 1M
1B 001M

and you are looking for zero suppression on the M also.

=TEXT(INT(BZ9/1000000),"#,##0")&"B
"&TEXT(INT((MOD(BZ9,1000000)/1000)),"##0")&"M"

Hope this helps

--


nastech Wrote:
Hi, am looking for way to "CUSTOM FORMAT" a cell to read 1.511B or
1.511M
as: 1B 1M (both B for billion & M.. present in column,
thanks)

(CUSTOM FORMATTING !!: Right-Click cell, FORMAT CELLS.., NUMBER TAB,
CUSTOM..

Modify / correct this line:
Just looking for custom formatting such as: #,##0? ("?" for
wildcarding B or M)

Is this an area not possible for custom formatting in Excel?

separate work column might not work for me, but (THIS WORKS):
=TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=536624

(Not looking for formula's). Just need "Custom Format" for how column of
e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?



"Bryan Hessey" wrote:


The Text format is probably the best option, as in normal formatting,
once a digit is encountered the following zeros are not suppressed, so
a custom format of ####"B "##0"M" will display such as:

B 1M
1B 001M

and you are looking for zero suppression on the M also.

=TEXT(INT(BZ9/1000000),"#,##0")&"B
"&TEXT(INT((MOD(BZ9,1000000)/1000)),"##0")&"M"

Hope this helps

--


nastech Wrote:
Hi, am looking for way to "CUSTOM FORMAT" a cell to read 1.511B or
1.511M
as: 1B 1M (both B for billion & M.. present in column,
thanks)

(CUSTOM FORMATTING !!: Right-Click cell, FORMAT CELLS.., NUMBER TAB,
CUSTOM..

Modify / correct this line:
Just looking for custom formatting such as: #,##0? ("?" for
wildcarding B or M)

Is this an area not possible for custom formatting in Excel?

separate work column might not work for me, but (THIS WORKS):
=TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=536624