MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Please help me on converting a figure in digits to a figure in words.


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

Two links to help answer your question

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

Plus another... (Re: Two links to help answer your question)

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

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

Aladin

=====

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 < 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 < 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