Solved

# BIGNumber 2 Text

Posted on 2006-05-22
261 Views
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
Question by:bisola_4

LVL 1

Expert Comment

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

LVL 1

Expert Comment

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

LVL 19

Expert Comment

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

LVL 19

Expert Comment

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

LVL 19

Expert Comment

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.

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

LVL 19

Expert Comment

If you need a larger number than millions just change nTemp to a double.

-Brian
0

Author Comment

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

LVL 19

Accepted Solution

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

Author Comment

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

Author Comment

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

LVL 19

Expert Comment

Ok great.
0

Author Comment

here is 250
0

## Featured Post

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…