Custom Formatting

[nastech]

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

may need to use worker column to get rid of unwanted digits? but
separate work column might not work for me, but would be:
=TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)

Just looking for custom formatting such as: #,##0? ??

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

[Bernard Liengme]

Try
=IF(A1>=1000000000,TEXT(A1/1000000000,"#.000")&"
B",TEXT(A1/1000000,"#.000")&" M")
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"nastech" <nastech@discussions.microsoft.com> wrote in message
news:3771B9E0-0F51-4475-B71A-3B5D299A9E0D@microsoft.com...
> Hi, am looking for way to custom format cell to read 1.511B or 1.511M
> as: 1B 1M (both B for billion & M.. present in column, thanks)
>
> may need to use worker column to get rid of unwanted digits? but
> separate work column might not work for me, but would be:
> =TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)
>
> Just looking for custom formatting such as: #,##0? ??
>
> Is this an area not possible for custom formatting in Excel?

[nastech]

Hi, thanks for the response, (looks like another way to do it), but the one
used below does exactly that, and works for both B/M values. Question
actually is for "CUSTOM Formatting" (cannot use worker column for reasons
of local of copy-paste situation).

The Following is Probably Incorrect, but need the answer for RIGHT-CLICK ON
CELL, FORMAT CELLS, CUSTOM, THEN????? something like:

#,##0? maybe with a wild-card "?" for the last letter in Number/ Text
combo.

"Bernard Liengme" wrote:

> Try
> =IF(A1>=1000000000,TEXT(A1/1000000000,"#.000")&"
> B",TEXT(A1/1000000,"#.000")&" M")
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "nastech" <nastech@discussions.microsoft.com> wrote in message
> news:3771B9E0-0F51-4475-B71A-3B5D299A9E0D@microsoft.com...
> > Hi, am looking for way to custom format cell to read 1.511B or 1.511M
> > as: 1B 1M (both B for billion & M.. present in column, thanks)
> >
> > may need to use worker column to get rid of unwanted digits? but
> > separate work column might not work for me, but would be:
> > =TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)
> >
> > Just looking for custom formatting such as: #,##0? ??
> >
> > Is this an area not possible for custom formatting in Excel?
>
>
>