SPELLNUMBER VBA IN EXCEL
Solved/Closed
khamlanathphasouk
Posts
1
Registration date
Wednesday February 17, 2016
Status
Member
Last seen
February 17, 2016

Feb 17, 2016 at 11:16 AM
Lan  Mar 5, 2016 at 10:32 AM
Lan  Mar 5, 2016 at 10:32 AM
Related:
 Spellnumber file download
 Spell number in excel download  Best answers
 Spellnumber.xlam free download  Best answers
 Kmspico download  Download  Other
 Davinci resolve 17 download  Download  Video editing
 Minecraft java edition free download  Download  Sandbox
 Google meet download for pc  Download  Video calls
 Gta 5 download apk pc  Download  Action and adventure
1 response
YasserKhalil
Posts
6
Registration date
Saturday February 20, 2016
Status
Member
Last seen
July 31, 2016
1
Feb 20, 2016 at 09:54 AM
Feb 20, 2016 at 09:54 AM
Try this UDF function
You can use it :
if your number in A1 put this formula in B1
Function SpellNumber(ByVal MyNumber, _
Optional pbNum As Boolean = True, _
Optional ptCur As String = "", _
Optional ptDec As String = "", _
Optional ptPlu As String = "")
Dim Curr, Decm, Temp
Dim DecimalPlace, Count
Dim vtPHolder As String
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
'' String representation of amount
If Mid(MyNumber, 1, 1) = "" Then
MyNumber = Mid(MyNumber, 2, Len(MyNumber)  1)
End If
MyNumber = Trim(Str(MyNumber))
'' Position of decimal place 0 if none
DecimalPlace = InStr(MyNumber, ".")
'' Convert decimal part, and set MyNumber to currency amount
If DecimalPlace > 0 Then
vtPHolder = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
If pbNum = True Then
Decm = GetTens(vtPHolder)
Else
Decm = vtPHolder
End If
MyNumber = Trim(Left(MyNumber, DecimalPlace  1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Curr = Temp & Place(Count) & Curr
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber)  3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Curr
Case ""
Curr = ""
Case "One"
Curr = "One " & ptCur
Case Else
Curr = Curr & " " & ptCur & ""
End Select
Select Case Decm
Case ""
Decm = ""
Case "One", "01"
If Curr = 0 Or Curr = "" Then
Decm = Decm & " " & ptDec
Else
Decm = " and " & Decm & " " & ptDec
End If
Case Else
If Curr = 0 Or Curr = "" Then
Decm = Decm & " " & ptDec & ptPlu
Else
Decm = " and " & Decm & " " & ptDec & ptPlu
End If
End Select
SpellNumber = Curr & Decm
End Function
'*******************************************
' Converts a number from 100999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
'Convert the hundreds place
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
'Convert the tens and ones place
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************
Function GetTens(TensText)
Dim Result As String
Result = "" 'null out the temporary function value
If Val(Left(TensText, 1)) = 1 Then 'If value between 1019
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else 'If value between 2099
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit(Right(TensText, 1)) 'Retrieve ones place
End If
GetTens = Result
End Function
'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
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
You can use it :
if your number in A1 put this formula in B1
=TRIM(SpellNumber(A1))
Mar 5, 2016 at 10:32 AM
Thank for helping, I have tried it but still unsolved
Best regards
khamlan