Directly from Microsoft:
Convert numbers into words - Microsoft Support
If you need something more specific - the code can be altered or you can come up with a more creative formula.
Cheers.
Hi Sir,
I am not an excel pro sir. I'm trying to alter many times but it did not come up
to the result i need. See below a result that i need:
I have a VBA code but this code will give me this result:
See below VBA CODE I used...
Option Explicit
Function SpellNumber(ByVal Number)
Dim Pesos, Cents, T
Dim Dec, Cnt
ReDim Position(9) As String
Position(2) = " Thousand "
Position(3) = " Million "
Position(4) = " Billion "
Position(5) = " Trillion "
Number = Trim(Str(Number))
Dec = InStr(Number, ".")
If Dec > 0 Then
Cents = GetTens2(Left(Mid(Number, Dec + 1) & _
"00", 2))
Number = Trim(Left(Number, Dec - 1))
End If
Cnt = 1
Do While Number <> ""
T = GetHundreds(Right(Number, 3))
If T <> "" Then Pesos = T & Position(Cnt) & Pesos
If Len(Number) > 3 Then
Number = Left(Number, Len(Number) - 3)
Else
Number = ""
End If
Cnt = Cnt + 1
Loop
Select Case Pesos
Case ""
Pesos = "No Pesos"
Case "One"
Pesos = "One Peso"
Case Else
Pesos = Pesos & ""
End Select
Select Case Cents
Case ""
Cents = ""
Case "One"
Cents = " and One Cent"
Case Else
Cents = " & " & Cents & "/100"
End Select
SpellNumber = "***" & Pesos & Cents & "***"
End Function
Function GetHundreds(ByVal Number)
Dim R As String
If Val(Number) = 0 Then Exit Function
Number = Right("000" & Number, 3)
If Mid(Number, 1, 1) <> "0" Then
R = GetDigit(Mid(Number, 1, 1)) & " Hundred "
End If
If Mid(Number, 2, 1) <> "0" Then
R = R & GetTens(Mid(Number, 2))
Else
R = R & GetDigit(Mid(Number, 3))
End If
GetHundreds = R
End Function
Function GetTens(TensText)
Dim R As String
R = ""
If Val(Left(TensText, 1)) = 1 Then
Select Case Val(TensText)
Case 10: R = "Ten"
Case 11: R = "Eleven"
Case 12: R = "Twelve"
Case 13: R = "Thirteen"
Case 14: R = "Fourteen"
Case 15: R = "Fifteen"
Case 16: R = "Sixteen"
Case 17: R = "Seventeen"
Case 18: R = "Eighteen"
Case 19: R = "Nineteen"
Case Else
End Select
Else
Select Case Val(Left(TensText, 1))
Case 2: R = "Twenty "
Case 3: R = "Thirty "
Case 4: R = "Forty "
Case 5: R = "Fifty "
Case 6: R = "Sixty "
Case 7: R = "Seventy "
Case 8: R = "Eighty "
Case 9: R = "Ninety "
Case Else
End Select
R = R & GetDigit _
(Right(TensText, 1))
End If
GetTens = R
End Function
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
Function GetTens2(TensText)
Dim R As String
R = ""
If Val(Left(TensText, 1)) = 1 Then
Select Case Val(TensText)
Case 10: R = "10"
Case 11: R = "11"
Case 12: R = "12"
Case 13: R = "13"
Case 14: R = "14"
Case 15: R = "15"
Case 16: R = "16"
Case 17: R = "17"
Case 18: R = "18"
Case 19: R = "19"
Case Else
End Select
Else
Select Case Val(Left(TensText, 1))
Case 2: R = "2"
Case 3: R = "3"
Case 4: R = "4"
Case 5: R = "5"
Case 6: R = "6"
Case 7: R = "7"
Case 8: R = "8"
Case 9: R = "9"
Case Else
End Select
R = R & GetDigit2 _
(Right(TensText, 1))
End If
GetTens2 = R
End Function
Function GetDigit2(Digit)
Select Case Val(Digit)
Case 0: GetDigit2 = "0"
Case 1: GetDigit2 = "1"
Case 2: GetDigit2 = "2"
Case 3: GetDigit2 = "3"
Case 4: GetDigit2 = "4"
Case 5: GetDigit2 = "5"
Case 6: GetDigit2 = "6"
Case 7: GetDigit2 = "7"
Case 8: GetDigit2 = "8"
Case 9: GetDigit2 = "9"
Case Else: GetDigit2 = ""
End Select
End Function
