View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Niek Otten Niek Otten is offline
external usenet poster
 
Posts: 3,440
Default 500m, 500b, 500t, function?

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