I just tried the check template that can be found at the following
url.

http://groups.google.com/group/micro...a7a17efd08ce6d


http://tinyurl.com/NumberAsText


It did exactly what I needed it to do. Well, almost exactly. And of
course that's the problem.

The Function converts the following:

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

The problem is that the format I write checks in is a combination of
the last two.

ie.

=NumberAsText(A1,"MyCheckFormat") == One Hundred Twenty Three
Dollars and 45/100

I would be grateful if someone could revise this function to have an
option of such a format.

Thanks.

I just tried the check template that can be found at the
following url.

http://groups.google.com/group/micro...a7a17efd08ce6d


http://tinyurl.com/NumberAsText


It did exactly what I needed it to do. Well, almost exactly. And
of course that's the problem.

The Function converts the following:

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

The problem is that the format I write checks in is a combination of
the last two.

ie.

=NumberAsText(A1,"MyCheckFormat") == One Hundred Twenty Three
Dollars and 45/100

Well, since that is my function, I guess I should be the one to modify it
for you.<g In the code below, use "CheckDollar" as the optional second
argument (all other options should still work as they did originally.
Although I only changed one procedure, I decided to post the complete code
(with instructions) so that anyone viewing this in the future in the
archives will have everything they need from their one search.

--
Rick (MVP - Excel)

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
=NumberAsText(A1,"CheckDollar") == One Hundred Twenty Three Dollars and
45/100

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

Private sNumberText() As String

Public Function NumberAsText(NumberIn As Variant, Optional _
AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR 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
Dim bUseCheckDollar 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_or_CHECKDOLLAR)
' 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")
bUseCheckDollar = sStyle = "checkdollar"
'----------------------------------------
' 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"
ElseIf bUseCheckDollar = True Then
If tmp = "One " Then
tmp = tmp & "Dollar"
Else
tmp = tmp & "Dollars"
End If
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

On Jul 22, 5:28*pm, "Rick Rothstein"
wrote:

Well, since that is my function, I guess I should be the one to modify it
for you.<g In the code below, use "CheckDollar" as the optional second
argument (all other options should still work as they did originally.
Although I only changed one procedure, I decided to post the complete code
(with instructions) so that anyone viewing this in the future in the
archives will have everything they need from their one search.

--
Rick (MVP - Excel)

Thank you very much.

You are really a lifesaver here.

I used this function in version 2003 and it worked very well!..... However..
in 2007 version it gave me a #NAME? error... what do I need to do to convert
this function to 2007?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson


"Rick Rothstein" wrote:

I just tried the check template that can be found at the
following url.

http://groups.google.com/group/micro...a7a17efd08ce6d


http://tinyurl.com/NumberAsText


It did exactly what I needed it to do. Well, almost exactly. And
of course that's the problem.

The Function converts the following:

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

The problem is that the format I write checks in is a combination of
the last two.

ie.

=NumberAsText(A1,"MyCheckFormat") == One Hundred Twenty Three
Dollars and 45/100

Well, since that is my function, I guess I should be the one to modify it
for you.<g In the code below, use "CheckDollar" as the optional second
argument (all other options should still work as they did originally.
Although I only changed one procedure, I decided to post the complete code
(with instructions) so that anyone viewing this in the future in the
archives will have everything they need from their one search.

--
Rick (MVP - Excel)

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
=NumberAsText(A1,"CheckDollar") == One Hundred Twenty Three Dollars and
45/100

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

Private sNumberText() As String

Public Function NumberAsText(NumberIn As Variant, Optional _
AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR 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
Dim bUseCheckDollar 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_or_CHECKDOLLAR)
' 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")
bUseCheckDollar = sStyle = "checkdollar"
'----------------------------------------
' 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"
ElseIf bUseCheckDollar = True Then
If tmp = "One " Then
tmp = tmp & "Dollar"
Else
tmp = tmp & "Dollars"
End If
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"

It works fine in my copy of XL2007. Since you got a #NAME? error, I'm going
to guess you didn't install the code in the correct place... in order to be
use as a UDF, it must be located in a Module (Insert/Module from the VB menu
bar).

--
Rick (MVP - Excel)


"Cydney" wrote in message
...
I used this function in version 2003 and it worked very well!.....
However..
in 2007 version it gave me a #NAME? error... what do I need to do to
convert
this function to 2007?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson


"Rick Rothstein" wrote:

I just tried the check template that can be found at the
following url.

http://groups.google.com/group/micro...a7a17efd08ce6d


http://tinyurl.com/NumberAsText


It did exactly what I needed it to do. Well, almost exactly. And
of course that's the problem.

The Function converts the following:

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

The problem is that the format I write checks in is a combination of
the last two.

ie.

=NumberAsText(A1,"MyCheckFormat") == One Hundred Twenty Three
Dollars and 45/100

Well, since that is my function, I guess I should be the one to modify it
for you.<g In the code below, use "CheckDollar" as the optional second
argument (all other options should still work as they did originally.
Although I only changed one procedure, I decided to post the complete
code
(with instructions) so that anyone viewing this in the future in the
archives will have everything they need from their one search.

--
Rick (MVP - Excel)

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
=NumberAsText(A1,"CheckDollar") == One Hundred Twenty Three Dollars and
45/100

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

Private sNumberText() As String

Public Function NumberAsText(NumberIn As Variant, Optional _
AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR 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
Dim bUseCheckDollar 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_or_CHECKDOLLAR)
' 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")
bUseCheckDollar = sStyle = "checkdollar"
'----------------------------------------
' 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"
ElseIf bUseCheckDollar = True Then
If tmp = "One " Then
tmp = tmp & "Dollar"
Else
tmp = tmp & "Dollars"
End If
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"

Actually it's a copy of the same file created in 2003 and then just opened in
2007. I did create the module in the 2003 version. Wouldn't it remain the
same? Could it be that there is a Reference needed?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson


"Rick Rothstein" wrote:

It works fine in my copy of XL2007. Since you got a #NAME? error, I'm going
to guess you didn't install the code in the correct place... in order to be
use as a UDF, it must be located in a Module (Insert/Module from the VB menu
bar).

--
Rick (MVP - Excel)


"Cydney" wrote in message
...
I used this function in version 2003 and it worked very well!.....
However..
in 2007 version it gave me a #NAME? error... what do I need to do to
convert
this function to 2007?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson


"Rick Rothstein" wrote:

I just tried the check template that can be found at the
following url.

http://groups.google.com/group/micro...a7a17efd08ce6d


http://tinyurl.com/NumberAsText


It did exactly what I needed it to do. Well, almost exactly. And
of course that's the problem.

The Function converts the following:

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

The problem is that the format I write checks in is a combination of
the last two.

ie.

=NumberAsText(A1,"MyCheckFormat") == One Hundred Twenty Three
Dollars and 45/100

Well, since that is my function, I guess I should be the one to modify it
for you.<g In the code below, use "CheckDollar" as the optional second
argument (all other options should still work as they did originally.
Although I only changed one procedure, I decided to post the complete
code
(with instructions) so that anyone viewing this in the future in the
archives will have everything they need from their one search.

--
Rick (MVP - Excel)

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
=NumberAsText(A1,"CheckDollar") == One Hundred Twenty Three Dollars and
45/100

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

Private sNumberText() As String

Public Function NumberAsText(NumberIn As Variant, Optional _
AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR 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
Dim bUseCheckDollar 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_or_CHECKDOLLAR)
' 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")
bUseCheckDollar = sStyle = "checkdollar"
'----------------------------------------
' 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"

Are macros disabled in your 2007 Excel?

Check your security settings.


Gord Dibben MS Excel MVP

On Fri, 24 Jul 2009 09:52:03 -0700, Cydney
wrote:

Actually it's a copy of the same file created in 2003 and then just opened in
2007. I did create the module in the 2003 version. Wouldn't it remain the
same? Could it be that there is a Reference needed?

That worked! Thanks.. I'm new to 2007 and I didn't think to look at the macro
security options. And it didn't give me the warning that it was disabling the
macros.
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson


"Gord Dibben" wrote:

Are macros disabled in your 2007 Excel?

Check your security settings.


Gord Dibben MS Excel MVP

On Fri, 24 Jul 2009 09:52:03 -0700, Cydney
wrote:

Actually it's a copy of the same file created in 2003 and then just opened in
2007. I did create the module in the 2003 version. Wouldn't it remain the
same? Could it be that there is a Reference needed?

On Jul 22, 5:28*pm, "Rick Rothstein"
wrote:
I just tried the check template that can be found at the
following url.

http://groups.google.com/group/micro...orksheet.funct...

http://tinyurl.com/NumberAsText

It did exactly what I needed it to do. Well, almost exactly. And
of course that's the problem.

The Function converts the following:

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

The problem is that the format I write checks in is a combination of
the last two.

ie.

=NumberAsText(A1,"MyCheckFormat") *== *One Hundred Twenty Three
Dollars and 45/100

Well, since that is my function, I guess I should be the one to modify it
for you.<g In the code below, use "CheckDollar" as the optional second
argument (all other options should still work as they did originally.
Although I only changed one procedure, I decided to post the complete code
(with instructions) so that anyone viewing this in the future in the
archives will have everything they need from their one search.

--
Rick (MVP - Excel)

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
=NumberAsText(A1,"CheckDollar") *== One Hundred Twenty Three Dollars and
45/100

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

Private sNumberText() As String

Public Function NumberAsText(NumberIn As Variant, Optional _
* * * * * * * * AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR 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
* *Dim bUseCheckDollar 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_or_CHECKDOLLAR)
* ' *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")
* *bUseCheckDollar = sStyle = "checkdollar"
* '----------------------------------------
* ' *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 =
...

read more »

If you really want to customize this I have seen people write on their
checks NO/100 instead of 00/100.

But what you have done already totally meets my needs. Thank you very
very much.

If you really want to customize this I have seen people write on
their checks NO/100 instead of 00/100.

But what you have done already totally meets my needs. Thank
you very very much.

You are welcome, of course, but the option you mentioned is very easy to add
to the function. Change the NumberAsText procedure declaration to this....

Public Function NumberAsText(NumberIn As Variant, Optional _
AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR As String, _
Optional NOfor00 As Boolean = False) As String

and add this statement...

If NOfor00 Then NumberAsText = Replace(NumberAsText, "00/100", "NO/100")

at the end of the function (right before the End Sub statement which would
put it immediately after this statement...

NumberAsText = NumberSign & tmp

This adds an extra optional argument that allows you to tell the function to
use NO instead of 00 in the fraction... simply enter True for the 3rd
argument. For example...

MoneyValue = 123
MsgBox NumberAsText(MoneyValue "CHECKDOLLAR", True)

If you omit the argument, or specify False for it, then the fraction prints
out with the 00 as before.

--
Rick (MVP - Excel)