Hi,
I have a data in numbers in A Column. Is there any macro which can
convert this number into words in Column B.
eg:
if A1 contains 1000 then i need in Colum B1 as one thousand.
Many Thanks for the help
Hello
Copy the code below to a module in your spreadsheet and save it. Then
on your spreadsheet, if your number is in cell A4 for example, enter
in another cell, the formula =ToWords(A4). You may use =proper(ToWords
(A4)) if you wish your words to be in proper case.
Code :
Public TW As String, Quotient As Long, Remainder As Long, Cents As
Long
Function ToWords(Amt)
TW = ""
Quotient = 0
Remainder = Int(Amt)
Cents = (Amt - Int(Amt)) * 100
If Cents > 0 Then
centsWord = "and cents " + Hundreds(Cents)
End If
TW = ""
If Remainder > 999999 Then
Quotient = Int(Remainder / 1000000)
Remainder = Remainder - Quotient * 1000000
TW = Hundreds(Quotient) + "million "
End If
If Remainder > 999 Then
Quotient = Int(Remainder / 1000)
Remainder = Remainder - Quotient * 1000
TW = Hundreds(Quotient) + "thousand "
End If
TW = Hundreds(Remainder)
ToWords = TW + centsWord
End Function
Function Hundreds(Amt)
H = Amt
If H > 99 Then
Quotient = Int(H / 100)
H = H - Quotient * 100
TW = TW + LessThan20(Quotient) + "hundred " + IIf(H > 0, "and
", "")
End If
Do While H > 20
Quotient = Int(H / 10) * 10
H = H - Quotient
TW = TW + LessThan100(Quotient)
Loop
TW = TW + LessThan20(H)
Hundreds = TW
End Function
Function LessThan20(No)
Select Case No
Case 1
LessThan20 = "one "
Case 2
LessThan20 = "two "
Case 3
LessThan20 = "three "
Case 4
LessThan20 = "four "
Case 5
LessThan20 = "five "
Case 6
LessThan20 = "six "
Case 7
LessThan20 = "seven "
Case 8
LessThan20 = "eight "
Case 9
LessThan20 = "nine "
Case 10
LessThan20 = "ten "
Case 11
LessThan20 = "eleven "
Case 12
LessThan20 = "twelve "
Case 13
LessThan20 = "thirteen "
Case 14
LessThan20 = "fourteen "
Case 15
LessThan20 = "fifteen "
Case 16
LessThan20 = "sixteen "
Case 17
LessThan20 = "seventeen "
Case 18
LessThan20 = "eighteen "
Case 19
LessThan20 = "nineteen "
Case 20
LessThan20 = "twenty "
End Select
End Function
Function LessThan100(No)
Select Case No
Case 20
LessThan100 = "twenty "
Case 30
LessThan100 = "thirty "
Case 40
LessThan100 = "forty "
Case 50
LessThan100 = "fifty "
Case 60
LessThan100 = "sixty "
Case 70
LessThan100 = "seventy "
Case 80
LessThan100 = "eighty "
Case 90
LessThan100 = "ninety "
End Select
End Function
Sub ToWord()
Amt = 9.45
TW = ""
Quotient = 0
Remainder = Int(Amt)
Cents = (Amt - Int(Amt)) * 100
If Cents > 0 Then
centsWord = "and cents " + Hundreds(Cents)
End If
TW = ""
If Remainder > 999999 Then
Quotient = Int(Remainder / 1000000)
Remainder = Remainder - Quotient * 1000000
TW = Hundreds(Quotient) + "million "
End If
If Remainder > 999 Then
Quotient = Int(Remainder / 1000)
Remainder = Remainder - Quotient * 1000
TW = Hundreds(Quotient) + "thousand "
End If
TW = Hundreds(Remainder)
Cells(1, 1).Value = TW + centsWord
End Sub
