I am preparing my invoices in Excel 2003 for my customers, so when I use the
auto sum option for having the total, I need to type the total amount in
words before issuing the invoice to my customers. So I kindly request to
solve this serious issue by giving me an simple example or any inbuilt
fucntions in excel so as to make my work faster & precise.
Thanks,
PR Pillai

Hi

Take a look at the Microsoft site
http://support.microsoft.com/default...en-us%3B213360


--

Regards
Roger Govier

"PR Pillai" <(E-Mail Removed)> wrote in message
news:AF7BA2D3-87A5-450C-A3E8-(E-Mail Removed)...
> I am preparing my invoices in Excel 2003 for my customers, so when I use
> the
> auto sum option for having the total, I need to type the total amount in
> words before issuing the invoice to my customers. So I kindly request to
> solve this serious issue by giving me an simple example or any inbuilt
> fucntions in excel so as to make my work faster & precise.
> Thanks,
> PR Pillai
>

Look here:

http://support.microsoft.com/default...;EN-US;Q213360

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"PR Pillai" <(E-Mail Removed)> wrote in message news:AF7BA2D3-87A5-450C-A3E8-(E-Mail Removed)...
|I am preparing my invoices in Excel 2003 for my customers, so when I use the
| auto sum option for having the total, I need to type the total amount in
| words before issuing the invoice to my customers. So I kindly request to
| solve this serious issue by giving me an simple example or any inbuilt
| fucntions in excel so as to make my work faster & precise.
| Thanks,
| PR Pillai
|

Hello,

I suggest to be VERY careful if you are using some of these widespread
versions which are derived from your quoted example. I believe it was
meant as an example only - there is almost no input data check.

My UDF spellnumber IS doing some input data check:
http://www.sulprobil.com/html/spellnumber.html

I do not guaranteee any positive functionality nor absence of errors
but if you check the test input shown on this page you can maybe see
differences of behaviour of circulating versions (I like to put -1 in,
for example). And: My UDF is being used in some financial
institutions.

Regards,
Bernd

>I am preparing my invoices in Excel 2003 for my customers, so when I use
>the
> auto sum option for having the total, I need to type the total amount in
> words before issuing the invoice to my customers. So I kindly request to
> solve this serious issue by giving me an simple example or any inbuilt
> fucntions in excel so as to make my work faster & precise.

Perhaps you can use this from a previous posting of mine...

Go into the VB Editor (Alt+F11) and add a Module to the Workbook
(Insert/Module from the VBA menu) and then paste in all of the code
appearing after my signature into the Module's code window. You can call the
function from your work sheet like this =NumberAsText(A1) where A1 is
assumed to hold the number you want to convert. There is an Optional
argument you can use to format the results to your liking. Here are some
examples to show the options available...

A1: 123.45

In B1:
=NumberAsText(A1) ==> One Hundred Twenty Three Point Four Five
=NumberAsText(A1,"And") ==> One Hundred and Twenty Three Point Four Five
=NumberAsText(A1,"Check") ==> One Hundred Twenty Three and 45/100
=NumberAsText(A1,"Dollar") ==> One Hundred Twenty Three Dollars and Forty
Five Cents

To summarize, using "And" adds the word "and" in front of the tens/units
text; using "Check" formats any decimal values as would be written on a
check; and using "Dollar" adds the words "Dollars" and "Cents" in their
appropriate positions. The code is laid out in a reasonably straight forward
manner, so if Dollars/Cents is not you native currency designation, you
should be able to modify the program accordingly. In addition to the above,
for all modes, the Plus and Minus sign can be used and will be reported back
as a word; commas may be used to separate the numbers to the left of the
decimal point but they will not be reported back by the routine and are
permitted for the users convenience (however, if commas are used, they must
be placed in their correct positions). And, finally,if I remember correctly,
this function will work with a whole number part up to one less than a
quintillion (you can have as many decimal points as desired), but remember
to format large numbers as Text values... VB will convert large non-Text
values to Doubles (which will destroy the conversion).

Rick


Private sNumberText() As String

Public Function NumberAsText(NumberIn As Variant, _
Optional AND_or_CHECK_or_DOLLAR As String) As String
Dim cnt As Long
Dim DecimalPoint As Long
Dim CardinalNumber As Long
Dim CommaAdjuster As Long
Dim TestValue As Long
Dim CurrValue As Currency
Dim CentsString As String
Dim NumberSign As String
Dim WholePart As String
Dim BigWholePart As String
Dim DecimalPart As String
Dim tmp As String
Dim sStyle As String
Dim bUseAnd As Boolean
Dim bUseCheck As Boolean
Dim bUseDollars As Boolean
'----------------------------------------
' Begin setting conditions for formatting
'----------------------------------------
' Determine whether to apply special formatting.
' If nothing passed, return routine result
' converted only into its numeric equivalents,
' with no additional format text.
sStyle = LCase(AND_or_CHECK_or_DOLLAR)
' User passed "AND": "and" will be added
' between hundredths and tens of dollars,
' ie "Three Hundred and Forty Two"
bUseAnd = sStyle = "and"
' User passed "DOLLAR": "dollar(s)" and "cents"
' appended to string,
' ie "Three Hundred and Forty Two Dollars"
bUseDollars = sStyle = "dollar"
' User passed "CHECK" *or* "DOLLAR"
' If "check", cent amount returned as a fraction /100
' i.e. "Three Hundred Forty Two and 00/100"
' If "dollar" was passed, "dollar(s)" and "cents"
' Appended instead.
bUseCheck = (sStyle = "check") Or (sStyle = "dollar")
'----------------------------------------
' Check/create array. If this is the first
' time using this routine, create the text
' strings that will be used.
'----------------------------------------
If Not IsBounded(sNumberText) Then
Call BuildArray(sNumberText)
End If
'----------------------------------------
' Begin validating the number, and breaking
' into constituent parts
'----------------------------------------
' Prepare to check for valid value in
NumberIn = Trim$(NumberIn)
If Not IsNumeric(NumberIn) Then
' Invalid entry - abort
NumberAsText = "Error - Number improperly formed"
Exit Function
Else
' Decimal check
DecimalPoint = InStr(NumberIn, ".")
If DecimalPoint > 0 Then
' Split the fractional and primary numbers
DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
WholePart = Left$(NumberIn, DecimalPoint - 1)
Else
' Assume the decimal is the last char
DecimalPoint = Len(NumberIn) + 1
WholePart = NumberIn
End If
If InStr(NumberIn, ",,") Or _
InStr(NumberIn, ",.") Or _
InStr(NumberIn, ".,") Or _
InStr(DecimalPart, ",") Then
NumberAsText = "Error - Improper use of commas"
Exit Function
ElseIf InStr(NumberIn, ",") Then
CommaAdjuster = 0
WholePart = ""
For cnt = DecimalPoint - 1 To 1 Step -1
If Not Mid$(NumberIn, cnt, 1) Like "[,]" Then
WholePart = Mid$(NumberIn, cnt, 1) & WholePart
Else
CommaAdjuster = CommaAdjuster + 1
If (DecimalPoint - cnt - CommaAdjuster) Mod 3 Then
NumberAsText = "Error - Improper use of commas"
Exit Function
End If
End If
Next
End If
End If
If Left$(WholePart, 1) Like "[+-]" Then
NumberSign = IIf(Left$(WholePart, 1) = "-", "Minus ", "Plus ")
WholePart = Mid$(WholePart, 2)
End If
'----------------------------------------
' Begin code to assure decimal portion of
' check value is not inadvertently rounded
'----------------------------------------
If bUseCheck = True Then
CurrValue = CCur(Val("." & DecimalPart))
DecimalPart = Mid$(Format$(CurrValue, "0.00"), 3, 2)
If CurrValue >= 0.995 Then
If WholePart = String$(Len(WholePart), "9") Then
WholePart = "1" & String$(Len(WholePart), "0")
Else
For cnt = Len(WholePart) To 1 Step -1
If Mid$(WholePart, cnt, 1) = "9" Then
Mid$(WholePart, cnt, 1) = "0"
Else
Mid$(WholePart, cnt, 1) = _
CStr(Val(Mid$(WholePart, cnt, 1)) + 1)
Exit For
End If
Next
End If
End If
End If
'----------------------------------------
' Final prep step - this assures number
' within range of formatting code below
'----------------------------------------
If Len(WholePart) > 9 Then
BigWholePart = Left$(WholePart, Len(WholePart) - 9)
WholePart = Right$(WholePart, 9)
End If
If Len(BigWholePart) > 9 Then
NumberAsText = "Error - Number too large"
Exit Function
ElseIf Not WholePart Like String$(Len(WholePart), "#") Or _
(Not BigWholePart Like String$(Len(BigWholePart), "#") _
And Len(BigWholePart) > 0) Then
NumberAsText = "Error - Number improperly formed"
Exit Function
End If
'----------------------------------------
' Begin creating the output string
'----------------------------------------
' Very Large values
TestValue = Val(BigWholePart)
If TestValue > 999999 Then
CardinalNumber = TestValue \ 1000000
tmp = HundredsTensUnits(CardinalNumber) & "Quadrillion "
TestValue = TestValue - (CardinalNumber * 1000000)
End If
If TestValue > 999 Then
CardinalNumber = TestValue \ 1000
tmp = tmp & HundredsTensUnits(CardinalNumber) & "Trillion "
TestValue = TestValue - (CardinalNumber * 1000)
End If
If TestValue > 0 Then
tmp = tmp & HundredsTensUnits(TestValue) & "Billion "
End If
' Lesser values
TestValue = Val(WholePart)
If TestValue = 0 And BigWholePart = "" Then tmp = "Zero "
If TestValue > 999999 Then
CardinalNumber = TestValue \ 1000000
tmp = tmp & HundredsTensUnits(CardinalNumber) & "Million "
TestValue = TestValue - (CardinalNumber * 1000000)
End If
If TestValue > 999 Then
CardinalNumber = TestValue \ 1000
tmp = tmp & HundredsTensUnits(CardinalNumber) & "Thousand "
TestValue = TestValue - (CardinalNumber * 1000)
End If
If TestValue > 0 Then
If Val(WholePart) < 99 And BigWholePart = "" Then bUseAnd = False
tmp = tmp & HundredsTensUnits(TestValue, bUseAnd)
End If
' If in dollar mode, assure the text is the correct plurality
If bUseDollars = True Then
CentsString = HundredsTensUnits(DecimalPart)
If tmp = "One " Then
tmp = tmp & "Dollar"
Else
tmp = tmp & "Dollars"
End If
If Len(CentsString) > 0 Then
tmp = tmp & " and " & CentsString
If CentsString = "One " Then
tmp = tmp & "Cent"
Else
tmp = tmp & "Cents"
End If
End If
ElseIf bUseCheck = True Then
tmp = tmp & "and " & Left$(DecimalPart & "00", 2)
tmp = tmp & "/100"
Else
If Len(DecimalPart) > 0 Then
tmp = tmp & "Point"
For cnt = 1 To Len(DecimalPart)
tmp = tmp & " " & sNumberText(Mid$(DecimalPart, cnt, 1))
Next
End If
End If
' Done!
NumberAsText = NumberSign & tmp
End Function

Private Sub BuildArray(sNumberText() As String)
ReDim sNumberText(0 To 27) As String
sNumberText(0) = "Zero"
sNumberText(1) = "One"
sNumberText(2) = "Two"
sNumberText(3) = "Three"
sNumberText(4) = "Four"
sNumberText(5) = "Five"
sNumberText(6) = "Six"
sNumberText(7) = "Seven"
sNumberText(8) = "Eight"
sNumberText(9) = "Nine"
sNumberText(10) = "Ten"
sNumberText(11) = "Eleven"
sNumberText(12) = "Twelve"
sNumberText(13) = "Thirteen"
sNumberText(14) = "Fourteen"
sNumberText(15) = "Fifteen"
sNumberText(16) = "Sixteen"
sNumberText(17) = "Seventeen"
sNumberText(18) = "Eighteen"
sNumberText(19) = "Nineteen"
sNumberText(20) = "Twenty"
sNumberText(21) = "Thirty"
sNumberText(22) = "Forty"
sNumberText(23) = "Fifty"
sNumberText(24) = "Sixty"
sNumberText(25) = "Seventy"
sNumberText(26) = "Eighty"
sNumberText(27) = "Ninety"
End Sub

Private Function IsBounded(vntArray As Variant) As Boolean
' Note: the application in the IDE will stop
' at this line when first run if the IDE error
' mode is not set to "Break on Unhandled Errors"
' (Tools/Options/General/Error Trapping)
On Error Resume Next
IsBounded = IsNumeric(UBound(vntArray))
End Function

Private Function HundredsTensUnits(ByVal TestValue As Integer, _
Optional bUseAnd As Boolean) As String
Dim CardinalNumber As Integer
If TestValue > 99 Then
CardinalNumber = TestValue \ 100
HundredsTensUnits = sNumberText(CardinalNumber) & " Hundred "
TestValue = TestValue - (CardinalNumber * 100)
End If
If bUseAnd = True Then
HundredsTensUnits = HundredsTensUnits & "and "
End If
If TestValue > 20 Then
CardinalNumber = TestValue \ 10
HundredsTensUnits = HundredsTensUnits & _
sNumberText(CardinalNumber + 18) & " "
TestValue = TestValue - (CardinalNumber * 10)
End If
If TestValue > 0 Then
HundredsTensUnits = HundredsTensUnits & _
sNumberText(TestValue) & " "
End If
End Function

There's a NumsToWords() user defined function (UDF) file you can
download at this website:
http://www.contextures.com/excelfilesRon.html

The file is completely unprotected, so you have full access to all of the
VBA code. The cover sheet in that file has instructions and several usage
examples.

If you prefer not to touch the VBA programming, it also has
a button that will export the NumsToWords program into any open
workbook.

(There's also a NON-VBA NumsToWords approach at that website.)

Other sites that may be of interest:
http://support.microsoft.com/default...b;en-us;213360
http://www.ozgrid.com/VBA/ValueToWords.htm

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"PR Pillai" <(E-Mail Removed)> wrote in message
news:AF7BA2D3-87A5-450C-A3E8-(E-Mail Removed)...
>I am preparing my invoices in Excel 2003 for my customers, so when I use
>the
> auto sum option for having the total, I need to type the total amount in
> words before issuing the invoice to my customers. So I kindly request to
> solve this serious issue by giving me an simple example or any inbuilt
> fucntions in excel so as to make my work faster & precise.
> Thanks,
> PR Pillai
>

I am preparing my invoices in Excel 2003 for my customers, so when I use the
auto sum option for having the total, I need to type the total amount in
words before issuing the invoice to my customers. So I kindly request to
solve this serious issue by giving me an simple example or any inbuilt
fucntions in excel so as to make my work faster & precise.
Thanks,




PRPilla wrote:

Converting total amount figures in excel to english words
26-Jan-08

I am preparing my invoices in Excel 2003 for my customers, so when I use th
auto sum option for having the total, I need to type the total amount i
words before issuing the invoice to my customers. So I kindly request t
solve this serious issue by giving me an simple example or any inbuil
fucntions in excel so as to make my work faster & precise
Thanks
PR Pillai

Previous Posts In This Thread:

On Saturday, January 26, 2008 11:43 PM
PRPilla wrote:

Converting total amount figures in excel to english words
I am preparing my invoices in Excel 2003 for my customers, so when I use th
auto sum option for having the total, I need to type the total amount i
words before issuing the invoice to my customers. So I kindly request t
solve this serious issue by giving me an simple example or any inbuil
fucntions in excel so as to make my work faster & precise
Thanks
PR Pillai

On Sunday, January 27, 2008 1:42 AM
Roger Govier wrote:

Re: Converting total amount figures in excel to english words
H

Take a look at the Microsoft sit
http://support.microsoft.com/default...Ben-us%3B21336

-

Regard
Roger Govier

On Sunday, January 27, 2008 3:53 AM
Niek Otten wrote:

Re: Converting total amount figures in excel to english words
Look here

http://support.microsoft.com/default...B;EN-US;Q21336

-
Kind regards

Niek Otte
Microsoft MVP - Excel

On Sunday, January 27, 2008 8:16 AM
Ron Coderre wrote:

There's a NumsToWords() user defined function (UDF) file you candownload at
There's a NumsToWords() user defined function (UDF) file you ca
download at this website
http://www.contextures.com/excelfilesRon.htm

The file is completely unprotected, so you have full access to all of th
VBA code. The cover sheet in that file has instructions and several usag
examples

If you prefer not to touch the VBA programming, it also ha
a button that will export the NumsToWords program into any ope
workbook

(There's also a NON-VBA NumsToWords approach at that website.

Other sites that may be of interest
http://support.microsoft.com/default...kb;en-us;21336
http://www.ozgrid.com/VBA/ValueToWords.ht

Does that help
Post back if you have more questions
-------------------------

Regards

Ro
Microsoft MVP (Excel
(XL2003, Win XP

"PR Pillai" <(E-Mail Removed)> wrote in message
news:AF7BA2D3-87A5-450C-A3E8-(E-Mail Removed)...

On Wednesday, February 06, 2008 7:54 AM
Bernd P wrote:

Hello,I suggest to be VERY careful if you are using some of these
Hello

I suggest to be VERY careful if you are using some of these widesprea
versions which are derived from your quoted example. I believe it wa
meant as an example only - there is almost no input data check

My UDF spellnumber IS doing some input data check
http://www.sulprobil.com/html/spellnumber.htm

I do not guaranteee any positive functionality nor absence of error
but if you check the test input shown on this page you can maybe se
differences of behaviour of circulating versions (I like to put -1 in
for example). And: My UDF is being used in some financia
institutions

Regards
Bernd

EggHeadCafe - Software Developer Portal of Choice
..NET Security [aPress]
http://www.eggheadcafe.com/tutorials...ty-apress.aspx

The following is a nice comprehensive answer compiled by Norman Harker.

Bernie


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...EN-US;Q213360&

and:

XL: How to Convert a Numeric Value into English Words
http://support.microsoft.com/default...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/...s/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/groups?(E-Mail Removed)

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


"solaiman mazumder" wrote in message news:(E-Mail Removed)...
>I am preparing my invoices in Excel 2003 for my customers, so when I use
>the
> auto sum option for having the total, I need to type the total amount in
> words before issuing the invoice to my customers. So I kindly request to
> solve this serious issue by giving me an simple example or any inbuilt
> fucntions in excel so as to make my work faster & precise.
> Thanks,
>
>
>
>
> PRPilla wrote:
>
> Converting total amount figures in excel to english words
> 26-Jan-08
>
> I am preparing my invoices in Excel 2003 for my customers, so when I use
> the
> auto sum option for having the total, I need to type the total amount in
> words before issuing the invoice to my customers. So I kindly request to
> solve this serious issue by giving me an simple example or any inbuilt
> fucntions in excel so as to make my work faster & precise.
> Thanks,
> PR Pillai
>
> Previous Posts In This Thread:
>
> On Saturday, January 26, 2008 11:43 PM
> PRPilla wrote:
>
> Converting total amount figures in excel to english words
> I am preparing my invoices in Excel 2003 for my customers, so when I use
> the
> auto sum option for having the total, I need to type the total amount in
> words before issuing the invoice to my customers. So I kindly request to
> solve this serious issue by giving me an simple example or any inbuilt
> fucntions in excel so as to make my work faster & precise.
> Thanks,
> PR Pillai
>
> On Sunday, January 27, 2008 1:42 AM
> Roger Govier wrote:
>
> Re: Converting total amount figures in excel to english words
> Hi
>
> Take a look at the Microsoft site
> http://support.microsoft.com/default...en-us%3B213360
>
>
> --
>
> Regards
> Roger Govier
>
> On Sunday, January 27, 2008 3:53 AM
> Niek Otten wrote:
>
> Re: Converting total amount figures in excel to english words
> Look here:
>
> http://support.microsoft.com/default...;EN-US;Q213360
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> On Sunday, January 27, 2008 8:16 AM
> Ron Coderre wrote:
>
> There's a NumsToWords() user defined function (UDF) file you candownload
> at
> There's a NumsToWords() user defined function (UDF) file you can
> download at this website:
> http://www.contextures.com/excelfilesRon.html
>
> The file is completely unprotected, so you have full access to all of the
> VBA code. The cover sheet in that file has instructions and several usage
> examples.
>
> If you prefer not to touch the VBA programming, it also has
> a button that will export the NumsToWords program into any open
> workbook.
>
> (There's also a NON-VBA NumsToWords approach at that website.)
>
> Other sites that may be of interest:
> http://support.microsoft.com/default...b;en-us;213360
> http://www.ozgrid.com/VBA/ValueToWords.htm
>
> Does that help?
> Post back if you have more questions.
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
>
> "PR Pillai" <(E-Mail Removed)> wrote in message
> news:AF7BA2D3-87A5-450C-A3E8-(E-Mail Removed)...
>
> On Wednesday, February 06, 2008 7:54 AM
> Bernd P wrote:
>
> Hello,I suggest to be VERY careful if you are using some of these
> Hello,
>
> I suggest to be VERY careful if you are using some of these widespread
> versions which are derived from your quoted example. I believe it was
> meant as an example only - there is almost no input data check.
>
> My UDF spellnumber IS doing some input data check:
> http://www.sulprobil.com/html/spellnumber.html
>
> I do not guaranteee any positive functionality nor absence of errors
> but if you check the test input shown on this page you can maybe see
> differences of behaviour of circulating versions (I like to put -1 in,
> for example). And: My UDF is being used in some financial
> institutions.
>
> Regards,
> Bernd
>
> EggHeadCafe - Software Developer Portal of Choice
> .NET Security [aPress]
> http://www.eggheadcafe.com/tutorials...ty-apress.aspx