Report

SPELLNUMBER VBA IN EXCEL [Solved]

Ask a question khamlanathphasouk 1Posts Wednesday February 17, 2016Registration date February 17, 2016 Last seen - Last answered on Mar 5, 2016 10:32AM
Hello,

I alreadly use spellnunber but the problem is it does not convert incorrect for my currency.
Present Situation
=SpellNumber(1,100,000) = Eleven Lac Only
Request
=SpellNumber(1,100,000) = One million and On San only.

Best regard
Khamlan
See more 
Helpful
+1
moins plus
Try this UDF function
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 100-999 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 10-19
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 20-99
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))
Lan- Mar 5, 2016 10:32AM
Hi ! YasserKhalil

Thank for helping, I have tried it but still unsolved

Best regards

khamlan
Reply
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!