Changing Numbers into text...

PaulChilds · Nov 4, 2002

I have a module which changes numbers into text, so, eg, if I type 10000, it shows 'Ten Thousand' and the appropriate place. However I have a problem with the word 'and'.

When I want it to say "One Thousand and One", it shows "One Thousand One" instead. It happens when it should be "thousand/million/billion and", but it works with the hundreds (eg 105 shows 'One Hundred and Five'), so 1205 works (it shows 'One Thousand Two Hundred and Five') etc.

Below is the code I am using. I can't work out where the problem is.

Any ideas?

Paul C

PS - It's called Spellpound because it's part of a finacial database - there's no relevance of the word pound otherwise.

Function SpellPound(dblValue As Double) As String

' Usage - To test it, open the debug window (ctrl+G) and type: ?SpellPound(any number you want), and hit enter

Dim centspot As Integer, Poundlen As Integer
Dim Pounds As Double, Cents As Integer
Dim Hundreds As Integer, Thousands As Integer
Dim Millions As Integer, Billions As Integer
Dim centtext As String, huntext As String, thoutext As String
Dim milltext As String, billtext As String, temptext As String

If dblValue < 0.01 Then
temptext = "Zero"
Else
centspot = InStr(dblValue, ".&quot

If centspot > 0 Then
Cents = Right(dblValue, 2)
Pounds = Left(dblValue, centspot - 1)
Else
Cents = 0
Pounds = dblValue
End If

Poundlen = Len(CStr(Pounds))

If Poundlen > 0 Then
Select Case Pounds
Case 1 To 999
Select Case Pounds
Case 1 To 9
Hundreds = Right(Pounds, 1)
Case 10 To 99
Hundreds = Right(Pounds, 2)
Case 100 To 999
Hundreds = Right(Pounds, 3)
End Select
Case 1000 To 999999
Hundreds = Right(Pounds, 3)
Select Case Pounds
Case 1000 To 9999
Thousands = Left(Pounds, 1)
Case 10000 To 99999
Thousands = Left(Pounds, 2)
Case 100000 To 999999
Thousands = Left(Pounds, 3)
End Select
Case 1000000 To 999999999
Hundreds = Right(Pounds, 3)
Thousands = Mid(Pounds, (Poundlen - 5), 3)
Select Case Pounds
Case 1000000 To 9999999
Millions = Left(Pounds, 1)
Case 10000000 To 99999999
Millions = Left(Pounds, 2)
Case 100000000 To 999999999
Millions = Left(Pounds, 3)
End Select
Case 1000000000 To 999999999999#
Hundreds = Right(Pounds, 3)
Thousands = Mid(Pounds, (Poundlen - 5), 3)
Millions = Mid(Pounds, (Poundlen - 8), 3)
Select Case Pounds
Case 1000000000 To 9999999999#
Billions = Left(Pounds, 1)
Case 10000000000# To 99999999999#
Billions = Left(Pounds, 2)
Case 100000000000# To 999999999999#
Billions = Left(Pounds, 3)
End Select
End Select
End If

If Hundreds > 0 Then huntext = GetPoundString("hundreds", Hundreds)
If Thousands > 0 Then thoutext = GetPoundString("thousands", Thousands)
If Millions > 0 Then milltext = GetPoundString("millions", Millions)
If Billions > 0 Then billtext = GetPoundString("billions", Billions)

If billtext = "" Then
If milltext = "" Then
If thoutext = "" Then
If huntext = "" Then
temptext = centtext
Else
temptext = huntext
End If
Else
temptext = thoutext & " " & huntext
End If
Else
temptext = milltext & " " & thoutext & " " & huntext
End If
Else
temptext = billtext & " " & milltext & " " & thoutext & huntext
End If
End If

SpellPound = Trim(temptext)
End Function

Function GetPoundString(strPart As String, intPart As Integer) As String
Dim strPounds As String
If Right(Pounds, 2) = 0 Then
Select Case Mid(intPart, 1, 1)
Case 1: strPounds = "One Hundred and"
Case 2: strPounds = "Two Hundred and"
Case 3: strPounds = "Three Hundred and"
Case 4: strPounds = "Four Hundred and"
Case 5: strPounds = "Five Hundred and"
Case 6: strPounds = "Six Hundred and"
Case 7: strPounds = "Seven Hundred and"
Case 8: strPounds = "Eight Hundred and"
Case 9: strPounds = "Nine Hundred and"
End Select
Else
If intPart > 99 Then
Select Case Mid(intPart, 1, 1)
Case 1: strPounds = "One Hundred"
Case 2: strPounds = "Two Hundred"
Case 3: strPounds = "Three Hundred"
Case 4: strPounds = "Four Hundred"
Case 5: strPounds = "Five Hundred"
Case 6: strPounds = "Six Hundred"
Case 7: strPounds = "Seven Hundred"
Case 8: strPounds = "Eight Hundred"
Case 9: strPounds = "Nine Hundred"
End Select
End If
End If

Select Case Right(intPart, 2)
Case 1: strPounds = strPounds & " and One"
Case 2: strPounds = strPounds & " and Two"
Case 3: strPounds = strPounds & " and Three"
Case 4: strPounds = strPounds & " and Four"
Case 5: strPounds = strPounds & " and Five"
Case 6: strPounds = strPounds & " and Six"
Case 7: strPounds = strPounds & " and Seven"
Case 8: strPounds = strPounds & " and Eight"
Case 9: strPounds = strPounds & " and Nine"
Case 10: strPounds = strPounds & " and Ten"
Case 11: strPounds = strPounds & " and Eleven"
Case 12: strPounds = strPounds & " and Twelve"
Case 13: strPounds = strPounds & " and Thirteen"
Case 14: strPounds = strPounds & " and Fourteen"
Case 15: strPounds = strPounds & " and Fifteen"
Case 16: strPounds = strPounds & " and Sixteen"
Case 17: strPounds = strPounds & " and Seventeen"
Case 18: strPounds = strPounds & " and Eighteen"
Case 19: strPounds = strPounds & " and Nineteen"
Case 20: strPounds = strPounds & " and Twenty"
Case 21: strPounds = strPounds & " and Twenty One"
Case 22: strPounds = strPounds & " and Twenty Two"
Case 23: strPounds = strPounds & " and Twenty Three"
Case 24: strPounds = strPounds & " and Twenty Four"
Case 25: strPounds = strPounds & " and Twenty Five"
Case 26: strPounds = strPounds & " and Twenty Six"
Case 27: strPounds = strPounds & " and Twenty Seven"
Case 28: strPounds = strPounds & " and Twenty Eight"
Case 29: strPounds = strPounds & " and Twenty Nine"
Case 30: strPounds = strPounds & " and Thirty"
Case 31: strPounds = strPounds & " and Thirty One"
Case 32: strPounds = strPounds & " and Thirty Two"
Case 33: strPounds = strPounds & " and Thirty Three"
Case 34: strPounds = strPounds & " and Thirty Four"
Case 35: strPounds = strPounds & " and Thirty Five"
Case 36: strPounds = strPounds & " and Thirty Six"
Case 37: strPounds = strPounds & " and Thirty Seven"
Case 38: strPounds = strPounds & " and Thirty Eight"
Case 39: strPounds = strPounds & " and Thirty Nine"
Case 40: strPounds = strPounds & " and Forty"
Case 41: strPounds = strPounds & " and Forty One"
Case 42: strPounds = strPounds & " and Forty Two"
Case 43: strPounds = strPounds & " and Forty Three"
Case 44: strPounds = strPounds & " and Forty Four"
Case 45: strPounds = strPounds & " and Forty Five"
Case 46: strPounds = strPounds & " and Forty Six"
Case 47: strPounds = strPounds & " and Forty Seven"
Case 48: strPounds = strPounds & " and Forty Eight"
Case 49: strPounds = strPounds & " and Forty Nine"
Case 50: strPounds = strPounds & " and Fifty"
Case 51: strPounds = strPounds & " and Fifty One"
Case 52: strPounds = strPounds & " and Fifty Two"
Case 53: strPounds = strPounds & " and Fifty Three"
Case 54: strPounds = strPounds & " and Fifty Four"
Case 55: strPounds = strPounds & " and Fifty Five"
Case 56: strPounds = strPounds & " and Fifty Six"
Case 57: strPounds = strPounds & " and Fifty Seven"
Case 58: strPounds = strPounds & " and Fifty Eight"
Case 59: strPounds = strPounds & " and Fifty Nine"
Case 60: strPounds = strPounds & " and Sixty"
Case 61: strPounds = strPounds & " and Sixty One"
Case 62: strPounds = strPounds & " and Sixty Two"
Case 63: strPounds = strPounds & " and Sixty Three"
Case 64: strPounds = strPounds & " and Sixty Four"
Case 65: strPounds = strPounds & " and Sixty Five"
Case 66: strPounds = strPounds & " and Sixty Six"
Case 67: strPounds = strPounds & " and Sixty Seven"
Case 68: strPounds = strPounds & " and Sixty Eight"
Case 69: strPounds = strPounds & " and Sixty Nine"
Case 70: strPounds = strPounds & " and Seventy"
Case 71: strPounds = strPounds & " and Seventy One"
Case 72: strPounds = strPounds & " and Seventy Two"
Case 73: strPounds = strPounds & " and Seventy Three"
Case 74: strPounds = strPounds & " and Seventy Four"
Case 75: strPounds = strPounds & " and Seventy Five"
Case 76: strPounds = strPounds & " and Seventy Six"
Case 77: strPounds = strPounds & " and Seventy Seven"
Case 78: strPounds = strPounds & " and Seventy Eight"
Case 79: strPounds = strPounds & " and Seventy Nine"
Case 80: strPounds = strPounds & " and Eighty"
Case 81: strPounds = strPounds & " and Eighty One"
Case 82: strPounds = strPounds & " and Eighty Two"
Case 83: strPounds = strPounds & " and Eighty Three"
Case 84: strPounds = strPounds & " and Eighty Four"
Case 85: strPounds = strPounds & " and Eighty Five"
Case 86: strPounds = strPounds & " and Eighty Six"
Case 87: strPounds = strPounds & " and Eighty Seven"
Case 88: strPounds = strPounds & " and Eighty Eight"
Case 89: strPounds = strPounds & " and Eighty Nine"
Case 90: strPounds = strPounds & " and Ninety"
Case 91: strPounds = strPounds & " and Ninety One"
Case 92: strPounds = strPounds & " and Ninety Two"
Case 93: strPounds = strPounds & " and Ninety Three"
Case 94: strPounds = strPounds & " and Ninety Four"
Case 95: strPounds = strPounds & " and Ninety Five"
Case 96: strPounds = strPounds & " and Ninety Six"
Case 97: strPounds = strPounds & " and Ninety Seven"
Case 98: strPounds = strPounds & " and Ninety Eight"
Case 99: strPounds = strPounds & " and Ninety Nine"
End Select

ConstructString:
Select Case strPart
Case "hundreds"
GetPoundString = strPounds

If Right(GetPoundString, 4) = " and" Then GetPoundString = Left(GetPoundString, Len(GetPoundString) - 4)

If Left(GetPoundString, 4) = " and" Then GetPoundString = Right(GetPoundString, Len(GetPoundString) - 4)

Case "thousands"
GetPoundString = strPounds & " Thousand"

If Right(GetPoundString, 4) = " and" Then GetPoundString = Left(GetPoundString, Len(GetPoundString) - 4)

If Left(GetPoundString, 4) = " and" Then GetPoundString = Right(GetPoundString, Len(GetPoundString) - 4)

Case "millions"
GetPoundString = strPounds & " Million"

If Right(GetPoundString, 4) = " and" Then GetPoundString = Left(GetPoundString, Len(GetPoundString) - 4)

If Left(GetPoundString, 4) = " and" Then GetPoundString = Right(GetPoundString, Len(GetPoundString) - 4)

Case "billions"
GetPoundString = strPounds & " Billion"

If Right(GetPoundString, 4) = " and" Then GetPoundString = Left(GetPoundString, Len(GetPoundString) - 4)

If Left(GetPoundString, 4) = " and" Then GetPoundString = Right(GetPoundString, Len(GetPoundString) - 4)

End Select
End Function

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

MichaelRed · Nov 4, 2002

faq181-1740

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

mp9 · Nov 4, 2002

At a first glance, I'd say the following line near the beginning of

Code:

GetPoundString

is part of the problem:

Code:

 If Right(Pounds, 2) = 0 Then ...

I can't see where

Code:

Pounds

is declared within GetPoundString - should this be

Code:

strPounds

or something?
[pc2]

http://www22.brinkster.com/accessory/

MichaelRed · Nov 4, 2002

... but you ALSO need to consider the 'grammer', as it is generally correct to insert 'AND' between the major and minor divisions of currency (as in between the Pound adn Pence? (e.g. for U.S. currency between Dollars and Cents). So even your example is incorrect?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Changing Numbers into text...

PaulChilds

Technical User

MichaelRed

Programmer

mp9

Programmer

MichaelRed

Programmer

Similar threads

Part and Inventory Search

Sponsor