Excel: Convert Number (1) to Text (one)

  • Thread starter Thread starter alexg
  • Start date Start date
A

alexg

I am working on a excel document that is linked to a word document. I
need to know if there is a function that will allow me to do the
following for instance:

"1" changes to "one"
"20" changes to "twenty"
"256" changes to "two-hundred and fifty six"

I have been unsuccessful so far and really could use some help.

Thanks
 
Hi Alex, The following is a nice comprehensive answer compiled by Norman Harker.

You should try a Google search for questions as you might imagine that this
is a fairly common question.

See:

XL2000: How to Convert a Numeric Value into English Words
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q213360&

and:

XL: How to Convert a Numeric Value into English Words
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q140704&

and:

(courtesy of a cut and paste from a Tom Ogilvy post):

If you want an addin that provides a worksheet function that does this,
download Laurent Longre's free morefunc.xll addin found here:

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

It is downloaded in a zip file which also contains an informative file in
'hlp' format that describes the 33 or so very useful functions included, one
of which does the number to words conversion you describe (supports various
languages and currencies).


and:
(Courtesy of Andy Wiggins FCCA) of www.BygSoftware.com

This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/num2wrds.zip

It's in the "Accountants" section on page:
http://www.bygsoftware.com/examples/examples.htm
It contains two methods to convert numbers to words and two check writing
routines.

The code is open and commented.

And, finally:

http://groups.google.com/[email protected]

A post containing a UDF by Bernie Deitrick that will take you into US budget
territory by covering amounts into trillions.
 
Thanks Ken,

You definitely had a lot of places for me to check out. They were all
really good. But I notice that none of them simply give you the number
output alone, no dollars and cents for instance.

You know a lot about this, do you know I how I can go about getting
just the number to text?

Of course, I could always try tweaking the other people's code...


Thanks again Ken!:)
 
Alex,

Here's mine, already converted:

Put the functions below into a standard codemodule - watch line
wrapping errors - and use it by entering

=MakeWords(12345)

or

=MakeWords(A2)

where A2 contains 12345

HTH,
Bernie

Function MakeWords(ByVal InValue As Double) As String

MakeWords = ""

n = InValue

trill = n / 1000000000000#
If Int(trill) > 0 Then
MakeWords = MakeWord(Int(trill)) & " trillion "
End If

n = n - Int(trill) * 1000000000000#
bill = n / 1000000000
If Int(bill) > 0 Then
MakeWords = MakeWords & MakeWord(Int(bill)) & " billion "
End If

n = n - Int(bill) * 1000000000
mill = n / 1000000
If Int(mill) > 0 Then
MakeWords = MakeWords & MakeWord(Int(mill)) & " million "
End If

n = n - Int(mill) * 1000000
thou = n / 1000
If Int(thou) > 0 Then
MakeWords = MakeWords & MakeWord(Int(thou)) & " thousand "

End If

n = n - Int(thou) * 1000
If n > 0 Then
MakeWords = MakeWords & MakeWord(Int(n))
End If

MakeWords = Application.WorksheetFunction.Proper(Trim(MakeWords))
End Function

Function MakeWord(InValue As Integer) As String
unitWord = Array("", "one", "two", "three", "four", "five", _
"six", "seven", "eight", "nine", "ten", "eleven", _
"twelve", "thirteen", "fourteen", "fifteen", "sixteen", _
"seventeen", "eighteen", "nineteen")
tenWord = Array("", "ten", "twenty", "thirty", "forty", "fifty", _
"sixty", "seventy", "eighty", "ninety")
MakeWord = ""
n = InValue
If n = 0 Then
MakeWord = "zero"
End If
hund = n \ 100
If hund > 0 Then
MakeWord = MakeWord & MakeWord(Int(hund)) & " hundred "
End If
n = n - hund * 100
If n < 20 Then
ten = n
MakeWord = MakeWord & unitWord(ten) & " "
Else
ten = n \ 10
MakeWord = MakeWord & tenWord(ten) & " "
unit = n - ten * 10
MakeWord = Trim(MakeWord & unitWord(unit))
End If
MakeWord = Application.WorksheetFunction.Proper(Trim(MakeWord))
End Function
 
First off, I would like to thank everyone who has posted an answer.

Is there something simpler out there? Without modules? I just think
Excel would have something to handle this.

Thanks again.
 
Alex,

Sorry - Excel doesn't do it automatically, which is why all these
solutions have been developed.

HTH,
Bernie
 
Cheers Bernie.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Bernie Deitrick said:
Alex,

Here's mine, already converted:

Put the functions below into a standard codemodule - watch line
wrapping errors - and use it by entering

=MakeWords(12345)

or

=MakeWords(A2)

where A2 contains 12345

HTH,
Bernie

Function MakeWords(ByVal InValue As Double) As String

MakeWords = ""

n = InValue

trill = n / 1000000000000#
If Int(trill) > 0 Then
MakeWords = MakeWord(Int(trill)) & " trillion "
End If

n = n - Int(trill) * 1000000000000#
bill = n / 1000000000
If Int(bill) > 0 Then
MakeWords = MakeWords & MakeWord(Int(bill)) & " billion "
End If

n = n - Int(bill) * 1000000000
mill = n / 1000000
If Int(mill) > 0 Then
MakeWords = MakeWords & MakeWord(Int(mill)) & " million "
End If

n = n - Int(mill) * 1000000
thou = n / 1000
If Int(thou) > 0 Then
MakeWords = MakeWords & MakeWord(Int(thou)) & " thousand "

End If

n = n - Int(thou) * 1000
If n > 0 Then
MakeWords = MakeWords & MakeWord(Int(n))
End If

MakeWords = Application.WorksheetFunction.Proper(Trim(MakeWords))
End Function

Function MakeWord(InValue As Integer) As String
unitWord = Array("", "one", "two", "three", "four", "five", _
"six", "seven", "eight", "nine", "ten", "eleven", _
"twelve", "thirteen", "fourteen", "fifteen", "sixteen", _
"seventeen", "eighteen", "nineteen")
tenWord = Array("", "ten", "twenty", "thirty", "forty", "fifty", _
"sixty", "seventy", "eighty", "ninety")
MakeWord = ""
n = InValue
If n = 0 Then
MakeWord = "zero"
End If
hund = n \ 100
If hund > 0 Then
MakeWord = MakeWord & MakeWord(Int(hund)) & " hundred "
End If
n = n - hund * 100
If n < 20 Then
ten = n
MakeWord = MakeWord & unitWord(ten) & " "
Else
ten = n \ 10
MakeWord = MakeWord & tenWord(ten) & " "
unit = n - ten * 10
MakeWord = Trim(MakeWord & unitWord(unit))
End If
MakeWord = Application.WorksheetFunction.Proper(Trim(MakeWord))
End Function
Click to expand...
 
If you want an addin that provides a worksheet function that does this,
download Laurent Longre's free morefunc.xll addin found here:

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

It is downloaded in a zip file which also contains an informative file in
'hlp' format that describes the 33 or so very useful functions included, one
of which does the number to words conversion you describe (supports various
languages and currencies).


would be the equivalent of having this in Excel. No modules required.
 
Back
Top