ExcelBanter - Converitng Numbers into text (4 as four)

Hi,

I had made a function quite sometime back for this.
It is not very well orgranized, but well it still works.
It is only for integers (Long), and not for floating points.
If the number entered is higher than Long data type permits it will give
error "#Num". You can use it up to 2 billions approx.

Add a module and cut pase the below code in to it.

You can use the function in worksheet, sameway like a normal workseet
function formula.
e.g. =numTotext(A1) where the number will be in cell A1
Or you can use it in code, yourvariable = numbTotext(number)

Sharad

Public Function numTotext(numB As Long) As String
Dim numbStr As String
Dim s1 As String, s10 As String, s100 As String, _
s1000 As String, sMillion As String, sBillion As String

numbStr = numB
Select Case Right(numbStr, 1)
Case 0
s1 = "Zero"
Case 1
s1 = "One"
Case 2
s1 = "Two"
Case 3
s1 = "Three"
Case 4
s1 = "Four"
Case 5
s1 = "Five"
Case 6
s1 = "Six"
Case 7
s1 = "Seven"
Case 8
s1 = "Eight"
Case 9
s1 = "Nine"
End Select
If Len(numbStr) = 1 Then
numTotext = s1
GoTo bye:
End If
If s1 = "Zero" Then s1 = ""
Select Case Left(Right(numbStr, 2), 1)
Case 0
s10 = ""
Case 1
s10 = "Ten"
Case 2
s10 = "Twenty"
Case 3
s10 = "Thirty"
Case 4
s10 = "Forty"
Case 5
s10 = "Fifty"
Case 6
s10 = "Sixty"
Case 7
s10 = "Seventy"
Case 8
s10 = "Eighty"
Case 9
s10 = "Ninety"
End Select

s10 = s10 & " " & s1
If Left(s10, 3) = "Ten" Then
Select Case Right(numbStr, 1)
Case 1
s10 = "Eleven"
Case 2
s10 = "Twelve"
Case 3
s10 = "Thirteen"
Case 4
s10 = "Fourteen"
Case 5
s10 = "Fifteen"
Case 6
s10 = "Sixteen"
Case 7
s10 = "Seventeen"
Case 8
s10 = "Eighteen"
Case 9
s10 = "Nineteen"
End Select
End If
If Len(numbStr) = 2 Then
numTotext = s10
GoTo bye:
End If
If s10 = " " Then
s100 = numTotext(Left(Right(numbStr, 3), 1)) & " Hundred"
Else
s100 = numTotext(Left(Right(numbStr, 3), 1)) & " Hundred and "
End If
If Left(Right(numB, 3), 1) = 0 Then s100 = ""
s100 = s100 & s10
If Len(numbStr) = 3 Then
numTotext = s100
GoTo bye:
End If
s1000 = numTotext(Right((Left(numB, Len(numbStr) - 3)), 3)) & "
Thousand, "

If Right((Left(numB, Len(numbStr) - 3)), 3) = 0 Then s1000 = ""
s1000 = Trim(s1000 & s100)
If Right(s1000, 1) = "," Then s1000 = Left(s1000, Len(s1000) - 1)
If Len(numbStr) 3 And Len(numbStr) < 7 Then
numTotext = s1000
GoTo bye:
End If
sMillion = numTotext(Right((Left(numB, Len(numbStr) - 6)), 3)) & "
Million, "
If Right((Left(numB, Len(numbStr) - 6)), 3) = 0 Then sMillion = ""
sMillion = Trim(sMillion & s1000)
If Right(sMillion, 1) = "," Then sMillion = Left(sMillion,
Len(sMillion) - 1)
If Len(numbStr) 6 And Len(numbStr) < 10 Then
numTotext = sMillion
GoTo bye:
End If
sBillion = numTotext(Left(numB, 1)) & " billion, "
sBillion = Trim(sBillion & sMillion)
If Right(sBillion, 1) = "," Then sBillion = Left(sBillion,
Len(sBillion) - 1)
numTotext = sBillion
bye:
End Function

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!