500m, 500b, 500t, function?

[Tone]

Hi all,

sorry if this has already been posted before but I am looking for a user defined formula (if this doesn't already exist within Excel's formulas) that will take a number and output it with a suffix of a 'm' for millions, 'b' for billions, 't' for trillions, etc.

So if the functions took a number like this: 104,856,000 I would like it to round it to the nearest million and output 105m or also if I can get it to display 104.86m

Can someone please point me in the right direction?

Many thanks,
Tony

[Niek Otten]

One way:

Function NumChar(a As Double) As String
Select Case a
Case Is >= 1000000000000#
NumChar = Format(a / 1000000000000#, "0.00") + "t"
Case Is >= 1000000000
NumChar = Format(a / 1000000000, "0.00") + "b"
Case Is >= 1000000
NumChar = Format(a / 1000000, "0.00") + "m"
Case Else
NumChar = Format(a, "0.00")
End Select
End Function


--
Kind regards,

Niek Otten

"Tone" <[email protected]> wrote in message
news:[email protected]...
>
> Hi all,
>
> sorry if this has already been posted before but I am looking for a
> user defined formula (if this doesn't already exist within Excel's
> formulas) that will take a number and output it with a suffix of a 'm'
> for millions, 'b' for billions, 't' for trillions, etc.
>
> So if the functions took a number like this: 104,856,000 I would like
> it to round it to the nearest million and output 105m or also if I can
> get it to display 104.86m
>
> Can someone please point me in the right direction?
>
> Many thanks,
> Tony
>
>
> --
> Tone
> ------------------------------------------------------------------------
> Tone's Profile:
> http://www.excelforum.com/member.php...o&userid=25356
> View this thread: http://www.excelforum.com/showthread...hreadid=489788
>

[Tone]

Thank you!

[Bob Phillips]

You could also use a custom format of

[>1000000000]#,##0,,,"t";[>1000000]#,##0,,"m";General

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Niek Otten" <[email protected]> wrote in message
news:%[email protected]...
> One way:
>
> Function NumChar(a As Double) As String
> Select Case a
> Case Is >= 1000000000000#
> NumChar = Format(a / 1000000000000#, "0.00") + "t"
> Case Is >= 1000000000
> NumChar = Format(a / 1000000000, "0.00") + "b"
> Case Is >= 1000000
> NumChar = Format(a / 1000000, "0.00") + "m"
> Case Else
> NumChar = Format(a, "0.00")
> End Select
> End Function
>
>
> --
> Kind regards,
>
> Niek Otten
>
> "Tone" <[email protected]> wrote in
message
> news:[email protected]...
> >
> > Hi all,
> >
> > sorry if this has already been posted before but I am looking for a
> > user defined formula (if this doesn't already exist within Excel's
> > formulas) that will take a number and output it with a suffix of a 'm'
> > for millions, 'b' for billions, 't' for trillions, etc.
> >
> > So if the functions took a number like this: 104,856,000 I would like
> > it to round it to the nearest million and output 105m or also if I can
> > get it to display 104.86m
> >
> > Can someone please point me in the right direction?
> >
> > Many thanks,
> > Tony
> >
> >
> > --
> > Tone
> > ------------------------------------------------------------------------
> > Tone's Profile:
> > http://www.excelforum.com/member.php...o&userid=25356
> > View this thread:
http://www.excelforum.com/showthread...hreadid=489788
> >
>
>

[Niek Otten]

Yes, and that way you could still calculate with them!

--
Kind regards,

Niek Otten

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> You could also use a custom format of
>
> [>1000000000]#,##0,,,"t";[>1000000]#,##0,,"m";General
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Niek Otten" <[email protected]> wrote in message
> news:%[email protected]...
>> One way:
>>
>> Function NumChar(a As Double) As String
>> Select Case a
>> Case Is >= 1000000000000#
>> NumChar = Format(a / 1000000000000#, "0.00") + "t"
>> Case Is >= 1000000000
>> NumChar = Format(a / 1000000000, "0.00") + "b"
>> Case Is >= 1000000
>> NumChar = Format(a / 1000000, "0.00") + "m"
>> Case Else
>> NumChar = Format(a, "0.00")
>> End Select
>> End Function
>>
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "Tone" <[email protected]> wrote in
> message
>> news:[email protected]...
>> >
>> > Hi all,
>> >
>> > sorry if this has already been posted before but I am looking for a
>> > user defined formula (if this doesn't already exist within Excel's
>> > formulas) that will take a number and output it with a suffix of a 'm'
>> > for millions, 'b' for billions, 't' for trillions, etc.
>> >
>> > So if the functions took a number like this: 104,856,000 I would like
>> > it to round it to the nearest million and output 105m or also if I can
>> > get it to display 104.86m
>> >
>> > Can someone please point me in the right direction?
>> >
>> > Many thanks,
>> > Tony
>> >
>> >
>> > --
>> > Tone
>> > ------------------------------------------------------------------------
>> > Tone's Profile:
>> > http://www.excelforum.com/member.php...o&userid=25356
>> > View this thread:
> http://www.excelforum.com/showthread...hreadid=489788
>> >
>>
>>
>
>