isayisay "CUSTOM" Formatting

[nastech]

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]

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

--

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)

[nastech]

(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
>
>