• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

BIGNumber 2 Text

I have seen Free code on converting Numbers to Strings. I however wished to use a function that abbreviated the string.. so rather than One million, one hundred and thirty-four thousand, I wanted [1.13 million]

It divided it using the proper fraction and returne somethign short and still readable.

Here is the code below. I have found it to be problematic. useful for some large numbers and not useful for some.

'-------------------------------------------------------------------------------------
'BigNumber2Text
'-------------------------------------------------------------------------------------

Public Function BigNumber2Text(ByVal dbNumberPassed As Double) As String
Dim dbConverted As Double
Dim sConverted As String

If dbNumberPassed > 100 Then
    If dbNumberPassed < 1000 Then
        dbConverted = FormatNumber(dbNumberPassed, 2, vbTrue, vbFalse, vbTrue)
        sConverted = dbConverted & " hundred"
        dbConverted = Round(dbConverted, 2)
    End If
End If

If dbNumberPassed > 1000 Then
    If dbNumberPassed < 100000 Then
        dbConverted = FormatNumber(dbNumberPassed, 2, vbTrue, vbFalse, vbTrue)
        dbConverted = dbConverted / 1000
        dbConverted = Round(dbConverted, 2)
        sConverted = dbConverted & " hundred thousand"
    End If
End If

If dbNumberPassed > 1000000 Then
    If dbNumberPassed < 1000000000 Then
        dbConverted = FormatNumber(dbNumberPassed, 2, vbTrue, vbFalse, vbTrue)
        dbConverted = dbConverted / 1000000
        dbConverted = Round(dbConverted, 2)
        sConverted = dbConverted & " million"
    End If
End If

If dbNumberPassed > 1000000000 Then
    If dbNumberPassed < 1000000000000# Then
        dbConverted = FormatNumber(dbNumberPassed, 2, vbTrue, vbFalse, vbTrue)
        dbConverted = dbConverted / 1000000000
        dbConverted = Round(dbConverted, 2)
        sConverted = dbConverted & " billion"
    End If
End If

If dbNumberPassed > 1000000000000# Then
    If dbNumberPassed < 1E+15 Then
        dbConverted = FormatNumber(dbNumberPassed, 2, vbTrue, vbFalse, vbTrue)
        dbConverted = dbConverted / 1000000000000#
        dbConverted = Round(dbConverted, 2)
        sConverted = dbConverted & " trillion"
    End If
End If


BigNumber2Text = sConverted
End Function

I however saw this piece of code that did something similar and managed to get to the portion that performed the operation i wanted.. only problem is... i suck at math
http://www.freevbcode.com/ShowCode.Asp?ID=343

            Select Case Int(Log(nTemp) / Log(10) + 0.5)
                Case 12: NumToString = NumToString & " trillion"
                Case 9: NumToString = NumToString & " billion"
                Case 6: NumToString = NumToString & " million"
                Case 3: NumToString = NumToString & " thousand"
            End Select
   
0
bisola_4
Asked:
bisola_4
  • 6
  • 4
  • 2
1 Solution
 
BrownheadCommented:
Didn't know how high you wanted to go but heres one that goes up to the millions... I can go as high as you want me to though.

'//Code\\
'This code requires nothing
Private Function SimplifyNum(ByVal Num As Long) As String
Select Case Len(CStr(Num))
    Case 1, 2: SimplifyNum = CStr(Num) 'Double Digit
    Case 3: SimplifyNum = CStr(Int(Num / 100)) & "." & MakeDouble(CStr(Int(Num Mod 100))) & " Hundred"
    Case 4, 5: SimplifyNum = CStr(Int(Num / 1000)) & "." & MakeDouble(CStr(Int(Round((Num Mod 1000) / 10)))) & " Thousand"
    Case 6: SimplifyNum = CStr(Int(Num / 100000)) & "." & MakeDouble(CStr(Int(Round((Num Mod 100000) / 1000)))) & " Hundred Thousand"
    Case 7: SimplifyNum = CStr(Int(Num / 1000000)) & "." & MakeDouble(CStr(Int(Round((Num Mod 1000000) / 10000)))) & " Million"
End Select
End Function
'\\Code//

Is this what your looking for?
0
 
BrownheadCommented:
oops, forgot the MakeDouble function, here you go:

'//Code\\
'This code requires nothing
Private Function MakeDouble(ByVal Num As Integer, Optional Zeroes As Integer = 1) As String
If (Len(CStr(Num)) = 1) Then MakeDouble = CStr(String(Zeroes, "0") & Num) Else MakeDouble = CStr(Num)
End Function
'\\Code//
0
 
BrianGEFF719Commented:
The code you mentioned:
           ' Select Case Int(Log(nTemp) / Log(10) + 0.5)
           '     Case 12: numToString = numToString & " trillion"
           '     Case 9: numToString = numToString & " billion"
           '     Case 6: numToString = numToString & " million"
           '     Case 3: numToString = numToString & " thousand"
           ' End Select
           
All this code does is figure out the number of decimal places in the number. Whats wrong with it for you?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
BrianGEFF719Commented:
Here, I modified that code for you so it does exactly what you want. 1430000 outputs 1.43 Million, good luck.


Dim nTemp As Long
Dim nDigits As Long

nTemp = 1430000

nDigits = Int(Log(nTemp) / Log(10)) + 0.5 ' convert to log base 10 and ceil

Select Case nDigits
      Case 12: numToString = numToString & " trillion"
      Case 9: numToString = numToString & " billion"
      Case 6: numToString = numToString & " million"
      Case 3: numToString = numToString & " thousand"
End Select
numToString = (nTemp / (10 ^ nDigits)) & " " & numToString

           
           
MsgBox numToString
0
 
BrianGEFF719Commented:
Or if you want everything but the first three digits rounded you could use this method:
For the number 1420493, the output will be 1.42 Million.



Private Sub Form_Load()
Dim nTemp As Long
Dim nDigits As Long
nTemp = 1420493
nDigits = Int(Log(nTemp) / Log(10)) + 0.5 ' convert to log base 10 and ceil
nTemp = Round(nTemp / (10 ^ nDigits), 3) * (10 ^ nDigits)


Select Case nDigits
      Case 12: numToString = numToString & " trillion"
      Case 9: numToString = numToString & " billion"
      Case 6: numToString = numToString & " million"
      Case 3: numToString = numToString & " thousand"
End Select
numToString = (nTemp / (10 ^ nDigits)) & " " & numToString

MsgBox numToString
End Sub
0
 
BrianGEFF719Commented:
If you need a larger number than millions just change nTemp to a double.


-Brian
0
 
bisola_4Author Commented:
Thanks BrianGEFF719 but i am still stuck somewhere (I am plain confused)

400000000000000 does not display the text after the number

can you figure out how

here is what i did
Public Function BigNum2Text(ByVal nTemp As Double)

Dim nDigits As Long
Dim NumToString As String
nDigits = Int(Log(nTemp) / Log(10)) + 0.5 ' convert to log base 10 and ceil
nTemp = Round(nTemp / (10 ^ nDigits), 3) * (10 ^ nDigits)


Select Case nDigits
      Case 12: NumToString = NumToString & " trillion"
      Case 9: NumToString = NumToString & " billion"
      Case 6: NumToString = NumToString & " million"
      Case 3: NumToString = NumToString & " thousand"
End Select
NumToString = FormatNumber((nTemp / (10 ^ nDigits)), 2, True, vbFalse, vbTrue) & " " & NumToString

Debug.Print NumToString
End Function


0
 
BrianGEFF719Commented:
Change dim nDigits As Long to -> dim nDigits as Double

And change the select statment to this:

Select Case nDigits
      Case 12,13,14: NumToString = NumToString & " trillion"
      Case 9,10,11: NumToString = NumToString & " billion"
      Case 6,7,8: NumToString = NumToString & " million"
      Case 3,4,5: NumToString = NumToString & " thousand"
End Select
0
 
bisola_4Author Commented:
I have but i am getting the wrong abbreviated result.. 1,000,000 (1 million) gives me 3.162

here is the full function, but you do not have to use the one below if you have a better version that does the same thing

Public Function BigNum2Text(ByVal nTemp As Double)

Dim nDigits As Double
Dim NumToString

nDigits = Int(Log(nTemp) / Log(10)) + 0.5 ' convert to log base 10 and ceil
nTemp = Round(nTemp / (10 ^ nDigits), 3) * (10 ^ nDigits)


Select Case nDigits
      Case 12, 13, 14: NumToString = NumToString & " trillion"
      Case 9, 10, 11: NumToString = NumToString & " billion"
      Case 6, 7, 8: NumToString = NumToString & " million"
      Case 3, 4, 5: NumToString = NumToString & " thousand"
End Select

NumToString = (nTemp / (10 ^ nDigits)) & " " & NumToString

Debug.Print NumToString
End Function
0
 
bisola_4Author Commented:
I have it.. thanks
here is full code i re-wrote (re-designed)
Function Num2Txt(ByVal nTemp As Double)
Dim nDigits As Double
Dim NumToString As String


nDigits = Int(Log(nTemp) / Log(10)) + 0.5 ' convert to log base 10 and ceil
nDigits = Int(nDigits)

Debug.Print nDigits

Select Case nDigits
      Case 12, 13, 14
      nTemp = nTemp / 1000000000000#
      nTemp = FormatNumber(nTemp, 2, vbTrue, vbFalse, vbTrue)
      NumToString = nTemp & " trillion"
     
      Case 9, 10, 11
      nTemp = nTemp / 1000000000
      nTemp = FormatNumber(nTemp, 2, vbTrue, vbFalse, vbTrue)
      NumToString = nTemp & " billion"
     
      Case 6, 7, 8
      nTemp = nTemp / 1000000
      nTemp = FormatNumber(nTemp, 2, vbTrue, vbFalse, vbTrue)
      NumToString = nTemp & " million"
     
      Case 3, 4, 5
      nTemp = nTemp / 1000
      nTemp = FormatNumber(nTemp, 2, vbTrue, vbFalse, vbTrue)
      NumToString = nTemp & " thousand"
     
      Case 0, 1, 2
      nTemp = FormatNumber(nTemp, 2, vbTrue, vbFalse, vbTrue)
      NumToString = nTemp

End Select
Num2Txt = NumToString

End Function


0
 
BrianGEFF719Commented:
Ok great.
0
 
bisola_4Author Commented:
here is 250
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now