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


--
Sathisc
------------------------------------------------------------------------
Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94977

On May 9, 5:35*pm, Sathisc wrote:
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

--
Sathisc
------------------------------------------------------------------------
Sathisc's Profile:http://www.thecodecage.com/forumz/member.php?userid=187
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=94977

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

thank you so much mahen


--
Sathisc
------------------------------------------------------------------------
Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94977

Glad we could be of help!


--
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94977

hi,

the code really works perfect but when the number exceeds one lakh the
outcome comes as hundred thousand. Can any one help in change it to one
lakh instead of one hundred thousand.


--
Sathisc
------------------------------------------------------------------------
Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94977

what is one lakh?

looks like the code works fine for numbers <1 billion (1,000,000,000)

"Sathisc" wrote in message
...

hi,

the code really works perfect but when the number exceeds one lakh the
outcome comes as hundred thousand. Can any one help in change it to one
lakh instead of one hundred thousand.


--
Sathisc
------------------------------------------------------------------------
Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94977