Convert number to words in excel

Closed
mdsarfarazalam Posts 1 Registration date Monday February 3, 2014 Status Member Last seen February 3, 2014 - Feb 3, 2014 at 12:35 AM
 Blocked Profile - Feb 3, 2014 at 09:12 AM
-----------------------------------------------------------------------------------
there is some problem while we convert number to words ............
if we type 20524.62 it has given two space after the word in excel. but we need only one space how we can solve this issue
20524.62 TWENTY THOUSAND , FIVE HUNDRED TWENTY FOUR.SIX TWO
but we want TWENTY THOUSAND , FIVE HUNDRED TWENTY FOUR.SIX TWO
another problem is it does not add zero after decimal point ................
after decimal if two number are come and if the last number is zero it does not take the zero... example is like this if we want to write 12.20 but in excel it just take 12.2 it not take 0. so how to add zero when we convert into number to words.
1255.6 ONE THOUSAND TWO HUNDRED FIFTY FIVE.SIX
but we want ONE THOUSAND TWO HUNDRED FIFTY FIVE.SIX ZERO
------------------------------------------------------------------------------------

Option Explicit
Public Numbers As Variant, Tens As Variant
Sub SetNums()
Numbers = Array("", "ONE", "TWO", "THREE", "FOUR", "FIVE", "SIX", "SEVEN", "EIGHT", "NINE", "TEN", "ELEVEN", "TWELVE", "THIRTEEN", "FOURTEEN", "FIFTEEN", "SIXTEEN", "SEVENTEEN", "EIGHTEEN", "NINETEEN")
Tens = Array("", "", "TWENTY", "THIRTY", "FOURTY", "FIFTY", "SIXTY", "SEVENTY", "EIGHTY", "NINETY")
End Sub
Function WordNum(MyNumber As Double) As String
Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
' This macro was written by Chris Mead - www.MeadInKent.co.uk
If Abs(MyNumber) > 999999999 Then
WordNum = "Value too large"
Exit Function
End If
SetNums
' String representation of amount (excl decimals)
NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))
For n = 3 To 1 Step -1 'analyse the absolute number as 3 sets of 3 digits
StrNo = Format(ValNo(n), "000")
If ValNo(n) > 0 Then
Temp1 = GetTens(Val(Right(StrNo, 2)))
If Left(StrNo, 1) <> "0" Then
Temp2 = Numbers(Val(Left(StrNo, 1))) & " HUNDRED"
If Temp1 <> "" Then Temp2 = Temp2 & " " ' this is used for given the space after the hundred
Else
Temp2 = ""
End If
If n = 3 Then
If Temp2 = " " And ValNo(1) + ValNo(2) > 0 Then Temp2 = ""
WordNum = Trim(Temp2 & Temp1)
End If
If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " THOUSAND " & WordNum)
If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million " & WordNum)
End If
Next n
NumStr = Trim(Str(Abs(MyNumber)))
' Values after the decimal place
DecimalPosition = InStr(NumStr, ".")
Numbers(0) = "Zero"
If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
Temp1 = "."
For n = DecimalPosition + 1 To Len(NumStr)
Temp1 = Temp1 & "" & Numbers(Val(Mid(NumStr, n, 1))) & " "
Next n
WordNum = WordNum & Temp1
End If
If Len(WordNum) = 0 Or Left(WordNum, 2) = " point" Then
WordNum = "Zero" & WordNum
End If
End Function
Function GetTens(TensNum As Integer) As String
' Converts a number from 0 to 99 into text.
If TensNum <= 19 Then
GetTens = Numbers(TensNum)
Else
Dim MyNo As String
MyNo = Format(TensNum, "00")
GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
End If
End Function

1 reply

Blocked Profile
Feb 3, 2014 at 09:12 AM
Well, zero is not in your array. Build your array with a "zero" index, and default to it when it finds nothing.

Give that a go!
3