Conv Number to Detailed Text String? 1

[matzos]

Is there a function in Access/Excel/VB that will convert a number value (i.e. 12,455) into a full string translation of that number (i.e. "Twelve-Thousand Four Hundred Fifty-Five&quot.

If not, do you know of a site that can provide that conversion logic in VB editor code?

I know this must be out there for check writing at least!

Thanks!
Kevin

 

[jebry]

Hi Kevin!

As you can see, this code was written by Joe Foster:


'************ Code Start **********
'This code was originally written by Joe Foster.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Joe Foster
'
' Convert a currency value into an (American) English string
Function English(ByVal N As Currency) As String
Const Thousand = 1000@
Const Million = Thousand * Thousand
Const Billion = Thousand * Million
Const Trillion = Thousand * Billion

If (N = 0@) Then
English = "zero"
Exit Function
End If

Dim Buf As String
If (N < 0@) Then
Buf = &quot;negative &quot;
Else
Buf = &quot;&quot;
End If

Dim Frac As Currency
Frac = Abs(N - Fix(N))

If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))

Dim AtLeastOne As Integer
AtLeastOne = N >= 1

If (N >= Trillion) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & &quot; trillion&quot;
N = N - Int(N / Trillion) * Trillion ' Mod overflows
If (N >= 1@) Then Buf = Buf & &quot; &quot;
End If

If (N >= Billion) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & &quot; billion&quot;
N = N - Int(N / Billion) * Billion ' Mod still overflows
If (N >= 1@) Then Buf = Buf & &quot; &quot;
End If

If (N >= Million) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N \ Million) & &quot; million&quot;
N = N Mod Million
If (N >= 1@) Then Buf = Buf & &quot; &quot;
End If

If (N >= Thousand) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N \ Thousand) & &quot; thousand&quot;
N = N Mod Thousand
If (N >= 1@) Then Buf = Buf & &quot; &quot;
End If

If (N >= 1@) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N)
End If

If (Frac = 0@) Then
Buf = Buf & &quot; exactly&quot;
ElseIf (Int(Frac * 100@) = Frac * 100@) Then
If AtLeastOne Then Buf = Buf & &quot; and &quot;
Buf = Buf & Format$(Frac * 100@, &quot;00&quot & &quot;/100&quot;
Else
If AtLeastOne Then Buf = Buf & &quot; and &quot;
Buf = Buf & Format$(Frac * 10000@, &quot;0000&quot & &quot;/10000&quot;
End If

English = Buf
End Function

' Support function to be used only by English()
Private Function EnglishDigitGroup(ByVal N As Integer) As String
Const Hundred = &quot; hundred&quot;
Const One = &quot;one&quot;
Const Two = &quot;two&quot;
Const Three = &quot;three&quot;
Const Four = &quot;four&quot;
Const Five = &quot;five&quot;
Const Six = &quot;six&quot;
Const Seven = &quot;seven&quot;
Const Eight = &quot;eight&quot;
Const Nine = &quot;nine&quot;
Dim Buf As String: Buf = &quot;&quot;
Dim Flag As Integer: Flag = False

'Do hundreds
Select Case (N \ 100)
Case 0: Buf = &quot;&quot;: Flag = False
Case 1: Buf = One & Hundred: Flag = True
Case 2: Buf = Two & Hundred: Flag = True
Case 3: Buf = Three & Hundred: Flag = True
Case 4: Buf = Four & Hundred: Flag = True
Case 5: Buf = Five & Hundred: Flag = True
Case 6: Buf = Six & Hundred: Flag = True
Case 7: Buf = Seven & Hundred: Flag = True
Case 8: Buf = Eight & Hundred: Flag = True
Case 9: Buf = Nine & Hundred: Flag = True
End Select

If (Flag <> False) Then N = N Mod 100
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & &quot; &quot;
Else
EnglishDigitGroup = Buf
Exit Function
End If

'Do tens (except teens)
Select Case (N \ 10)
Case 0, 1: Flag = False
Case 2: Buf = Buf & &quot;twenty&quot;: Flag = True
Case 3: Buf = Buf & &quot;thirty&quot;: Flag = True
Case 4: Buf = Buf & &quot;forty&quot;: Flag = True
Case 5: Buf = Buf & &quot;fifty&quot;: Flag = True
Case 6: Buf = Buf & &quot;sixty&quot;: Flag = True
Case 7: Buf = Buf & &quot;seventy&quot;: Flag = True
Case 8: Buf = Buf & &quot;eighty&quot;: Flag = True
Case 9: Buf = Buf & &quot;ninety&quot;: Flag = True
End Select

If (Flag <> False) Then N = N Mod 10
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & &quot;-&quot;
Else
EnglishDigitGroup = Buf
Exit Function
End If

'Do ones and teens
Select Case (N)
Case 0: ' do nothing
Case 1: Buf = Buf & One
Case 2: Buf = Buf & Two
Case 3: Buf = Buf & Three
Case 4: Buf = Buf & Four
Case 5: Buf = Buf & Five
Case 6: Buf = Buf & Six
Case 7: Buf = Buf & Seven
Case 8: Buf = Buf & Eight
Case 9: Buf = Buf & Nine
Case 10: Buf = Buf & &quot;ten&quot;
Case 11: Buf = Buf & &quot;eleven&quot;
Case 12: Buf = Buf & &quot;twelve&quot;
Case 13: Buf = Buf & &quot;thirteen&quot;
Case 14: Buf = Buf & &quot;fourteen&quot;
Case 15: Buf = Buf & &quot;fifteen&quot;
Case 16: Buf = Buf & &quot;sixteen&quot;
Case 17: Buf = Buf & &quot;seventeen&quot;
Case 18: Buf = Buf & &quot;eighteen&quot;
Case 19: Buf = Buf & &quot;nineteen&quot;
End Select

EnglishDigitGroup = Buf
End Function
'************ Code End **********

Jeff Bridgham
bridgham@purdue.edu