Hi all,

sorry if this has already been posted before but I am looking for
user defined formula (if this doesn't already exist within Excel'
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 lik
it to round it to the nearest million and output 105m or also if I ca
get it to display 104.86m

Can someone please point me in the right direction?

Many thanks,
Ton

--
Ton
-----------------------------------------------------------------------
Tone's Profile: http://www.excelforum.com/member.php...fo&userid=2535
View this thread: http://www.excelforum.com/showthread.php?threadid=48978

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" wrote in message
...

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

Thank you!


--
Tone
------------------------------------------------------------------------
Tone's Profile: http://www.excelforum.com/member.php...o&userid=25356
View this thread: http://www.excelforum.com/showthread...hreadid=489788

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" wrote in message
...
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" wrote in
message
...

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

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

--
Kind regards,

Niek Otten

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in
message
...

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