Posted by Abhay on December 15, 2001 2:35 AM

I want to convert a resultant figure in digits to a figure in words in another cell. e.g. Say 125 to "One Hundred Twenty Five"

Posted by Tom Urtis on December 15, 2001 3:31 AM

There have been a few posts on this board regarding this topic. Here are 2 such links among others that you may find on your own if you search back a couple months:

17021.html

26293.html

Tom Urtis

Posted by Aladin Akyurek on December 16, 2001 7:11 AM

http://longre.free.fr/english/

A free add-in that contains NUMTEXT or NUM2TEXT (or whatever) and lot more...

=====

Posted by Marcellus on December 16, 2001 10:35 AM

Hi there!
Although not state-of-the-art code, the following does get the job done up to 9999. Ive put it together with (as Im sure you have noticed) a very limited knowledge of VB. If you need to increase the functionality beyond the 9999, it is extremely easy to add.

I can get by on-the-fly by recording macros and then modifying them to suit my needs in Excel by making use of the Help files, shipped with various editions of Excel.

The Fuzzy Logic challenge, posted by Mr. Excel, still keeps me up at nights, so I had a stab at this one to make me feel less (only slightly) of an idiot. The and is also in the right places :)

Sub NumberToText()

Dim a, b, ba, c, d, e, f, g, h, i '(Set variables)

a = Cells(2, 1) '(Content of A2)
z = Len(a) '(Number of digits in a)
b = Right(a, 2) '(First two digits, from the right)
ba = Right(a, 1) '(First digit, from the right)
c = Left(Right(a, 2), 1) '(Second digit, from the right)
d = Left(Right(a, 3), 1) '(Third digit, from the right)
e = Left(Right(a, 4), 1) '(Fourth digit, from the right)

If a = 0 Then GoTo NoNumbers
If a > 9999 Then GoTo ShowError
If a > 999 Then GoTo ThousandStart
If a > 99 Then GoTo HundredStart
If a &LT; 20 Then GoTo SingleDigit
GoTo TwentyStart

ThousandStart:
If e = 0 Then i = "" Else _
If e = 1 Then i = "one thousand " Else _
If e = 2 Then i = "two thousand " Else _
If e = 3 Then i = "three thousand " Else _
If e = 4 Then i = "four thousand " Else _
If e = 5 Then i = "five thousand " Else _
If e = 6 Then i = "six thousand " Else _
If e = 7 Then i = "seven thousand " Else _
If e = 8 Then i = "eight thousand " Else _
If e = 9 Then i = "nine thousand "

HundredStart:
If d = 0 Then h = "" Else _
If d = 1 Then h = "one hundred " Else _
If d = 2 Then h = "two hundred " Else _
If d = 3 Then h = "three hundred " Else _
If d = 4 Then h = "four hundred " Else _
If d = 5 Then h = "five hundred " Else _
If d = 6 Then h = "six hundred " Else _
If d = 7 Then h = "seven hundred " Else _
If d = 8 Then h = "eight hundred " Else _
If d = 9 Then h = "nine hundred "

If b &LT; 20 Then GoTo AndFirstNumber

AndTwentyStart:
If c = 0 Then GoTo AndFirstNumber Else _
If c = 2 Then g = "and twenty " Else _
If c = 3 Then g = "and thirty " Else _
If c = 4 Then g = "and fourty " Else _
If c = 5 Then g = "and fifty " Else _
If c = 6 Then g = "and sixty " Else _
If c = 7 Then g = "and seventy " Else _
If c = 8 Then g = "and eighty " Else _
If c = 9 Then g = "and ninety " Else _

GetFirstNumber:
If ba = 1 Then f = "one" Else _
If ba = 2 Then f = "two" Else _
If ba = 3 Then f = "three" Else _
If ba = 4 Then f = "four" Else _
If ba = 5 Then f = "five" Else _
If ba = 6 Then f = "six" Else _
If ba = 7 Then f = "seven" Else _
If ba = 8 Then f = "eight" Else _
If ba = 9 Then f = "nine"

If z = 4 Then GoTo FourNumbers
If z = 3 Then GoTo ThreeNumbers

AndFirstNumber:
If b = 0 Then f = "" _
Else If b = 1 Then f = "and one" _
Else If b = 2 Then f = "and two" _
Else If b = 3 Then f = "and three" _
Else If b = 4 Then f = "and four" _
Else If b = 5 Then f = "and five" _
Else If b = 6 Then f = "and six" _
Else If b = 7 Then f = "and seven" _
Else If b = 8 Then f = "and eight" _
Else If b = 9 Then f = "and nine" _
Else If b = 10 Then f = "and ten" _
Else If b = 11 Then f = "and eleven" _
Else If b = 12 Then f = "and twelve" _
Else If b = 13 Then f = "and thirteen" _
Else If b = 14 Then f = "and fourteen" _
Else If b = 15 Then f = "and fifteen" _
Else If b = 16 Then f = "and sixteen" _
Else If b = 17 Then f = "and seventeen" _
Else If b = 18 Then f = "and eighteen" _
Else If b = 19 Then f = "and nineteen" _
Else GoTo EndOfCode

If z = 4 Then GoTo FourNumbers
If z = 3 Then GoTo ThreeNumbers

TwentyStart:
If c = 0 Then GoTo SingleDigit Else _
If c = 1 Then GoTo SingleDigit Else _
If c = 2 Then g = "twenty " Else _
If c = 3 Then g = "thirty " Else _
If c = 4 Then g = "fourty " Else _
If c = 5 Then g = "fifty " Else _
If c = 6 Then g = "sixty " Else _
If c = 7 Then g = "seventy " Else _
If c = 8 Then g = "eighty " Else _
If c = 9 Then g = "ninety " Else _

If ba = 0 Then f = " " Else _
If ba = 1 Then f = "one" Else _
If ba = 2 Then f = "two" Else _
If ba = 3 Then f = "three" Else _
If ba = 4 Then f = "four" Else _
If ba = 5 Then f = "five" Else _
If ba = 6 Then f = "six" Else _
If ba = 7 Then f = "seven" Else _
If ba = 8 Then f = "eight" Else _
If ba = 9 Then f = "nine"

GoTo TwoNumbers

SingleDigit:
If b = 0 Then f = " " Else _
If b = 1 Then f = "one" Else _
If b = 2 Then f = "two" Else _
If b = 3 Then f = "three" Else _
If b = 4 Then f = "four" Else _
If b = 5 Then f = "five" Else _
If b = 6 Then f = "six" Else _
If b = 7 Then f = "seven" Else _
If b = 8 Then f = "eight" Else _
If b = 9 Then f = "nine" Else _
If b = 10 Then f = "ten" Else _
If b = 11 Then f = "eleven" Else _
If b = 12 Then f = "twelve" Else _
If b = 13 Then f = "thirteen" Else _
If b = 14 Then f = "fourteen" Else _
If b = 15 Then f = "fifteen" Else _
If b = 16 Then f = "sixteen" Else _
If b = 17 Then f = "seventeen" Else _
If b = 18 Then f = "eighteen" Else _
If b = 19 Then f = "nineteen"

If z = 2 Then GoTo TwoNumbers

OneNumber:
Cells(3, 1) = f
GoTo EndOfCode

TwoNumbers:
Cells(3, 1) = g + f
GoTo EndOfCode

ThreeNumbers:
Cells(3, 1) = h + g + f

FourNumbers:
Cells(3, 1) = i + h + g + f
GoTo EndOfCode

ShowError:
Cells(3, 1) = "Number not valid or larger than 9999"
GoTo EndOfCode

NoNumbers:
Cells(3, 1) = "Zero"

EndOfCode:

End Sub