I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents. I have tried the vb module from article id 213360 but it
erros out at the cents = gettens line. any help would be great!

It sounds like you only copied the SpellNumber function and not the other 3
helper functions located under it on that webpage... you need to copy *all*
the code shown there into your code window.

Rick


"rekfish" wrote in message
...
I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents. I have tried the vb module from article id 213360 but
it
erros out at the cents = gettens line. any help would be great!

Rick,

I corrected the vb module with all info. I now get a name #name? error?

Thanks
rekfish

"Rick Rothstein (MVP - VB)" wrote:

It sounds like you only copied the SpellNumber function and not the other 3
helper functions located under it on that webpage... you need to copy *all*
the code shown there into your code window.

Rick


"rekfish" wrote in message
...
I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents. I have tried the vb module from article id 213360 but
it
erros out at the cents = gettens line. any help would be great!

It works for me. You did put all the code in a Module, not a UserForm or
Sheet code window, right? If you are not sure, you get a Module by clicking
Insert/Module from the VB editor's menu bar. UDFs must be placed in a
regular Module (not a Class Module) in order to work.

Rick


"rekfish" wrote in message
...
Rick,

I corrected the vb module with all info. I now get a name #name? error?

Thanks
rekfish

"Rick Rothstein (MVP - VB)" wrote:

It sounds like you only copied the SpellNumber function and not the other
3
helper functions located under it on that webpage... you need to copy
*all*
the code shown there into your code window.

Rick


"rekfish" wrote in message
...
I need to convert dollar value to english word, 100.21 to one hundred
and
twenty one cents. I have tried the vb module from article id 213360
but
it
erros out at the cents = gettens line. any help would be great!

Rick,

Thanks so much!! I removed the module completely and recreated it and it
worked this time.

Thanks again!

rekfish

"Rick Rothstein (MVP - VB)" wrote:

It works for me. You did put all the code in a Module, not a UserForm or
Sheet code window, right? If you are not sure, you get a Module by clicking
Insert/Module from the VB editor's menu bar. UDFs must be placed in a
regular Module (not a Class Module) in order to work.

Rick


"rekfish" wrote in message
...
Rick,

I corrected the vb module with all info. I now get a name #name? error?

Thanks
rekfish

"Rick Rothstein (MVP - VB)" wrote:

It sounds like you only copied the SpellNumber function and not the other
3
helper functions located under it on that webpage... you need to copy
*all*
the code shown there into your code window.

Rick


"rekfish" wrote in message
...
I need to convert dollar value to english word, 100.21 to one hundred
and
twenty one cents. I have tried the vb module from article id 213360
but
it
erros out at the cents = gettens line. any help would be great!

I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents.



rekfis wrote:

2007 excel formula spellnumber
22-Aug-08

I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents. I have tried the vb module from article id 213360 but it
erros out at the cents = gettens line. any help would be great!

Previous Posts In This Thread:

On Friday, August 22, 2008 8:06 AM
rekfis wrote:

2007 excel formula spellnumber
I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents. I have tried the vb module from article id 213360 but it
erros out at the cents = gettens line. any help would be great!

On Friday, August 22, 2008 9:41 AM
Rick Rothstein \(MVP - VB\) wrote:

It sounds like you only copied the SpellNumber function and not the other 3
It sounds like you only copied the SpellNumber function and not the other 3
helper functions located under it on that webpage... you need to copy *all*
the code shown there into your code window.

Rick


"rekfish" wrote in message
...

On Friday, August 22, 2008 10:43 AM
rekfis wrote:

Rick,I corrected the vb module with all info. I now get a name #name? error?
Rick,

I corrected the vb module with all info. I now get a name #name? error?

Thanks
rekfish

"Rick Rothstein (MVP - VB)" wrote:

On Friday, August 22, 2008 10:53 AM
Rick Rothstein \(MVP - VB\) wrote:

It works for me.
It works for me. You did put all the code in a Module, not a UserForm or
Sheet code window, right? If you are not sure, you get a Module by clicking
Insert/Module from the VB editor's menu bar. UDFs must be placed in a
regular Module (not a Class Module) in order to work.

Rick


"rekfish" wrote in message
...

On Friday, August 22, 2008 3:13 PM
rekfis wrote:

Rick,Thanks so much!!
Rick,

Thanks so much!! I removed the module completely and recreated it and it
worked this time.

Thanks again!

rekfish

"Rick Rothstein (MVP - VB)" wrote:

On Friday, August 07, 2009 1:05 AM
sunil jain wrote:

SpellNumber (help)
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Missed PDC 2005? Here's the content Download Page
http://www.eggheadcafe.com/tutorials...5-heres-t.aspx

The following function will convert a numerical value to a text string... in
Pounds and pence but easily adapted to Dollars & Cents. To use it send the
value to the function SpellNumber(123.45) or by reference in a cell
=SpellNumber(A1)

Function SpellNumber(ByVal MyNumber)
Dim Pounds, Pence, Temp
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

MyNumber = Trim(Str(MyNumber))

DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace 0 Then
Pence = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber < ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp < "" Then Pounds = Temp & Place(Count) & Pounds
If Len(MyNumber) 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case Pounds
Case ""
Pounds = "No Pounds"
Case "One"
Pounds = "One Pound"
Case Else
Pounds = Pounds & " Pounds"
End Select

Select Case Pence
Case ""
Pence = " and No Pence"
Case "One"
Pence = " and One Pence"
Case Else
Pence = " and " & Pence & " Pence"
End Select

SpellNumber = Pounds & Pence
End Function



Function GetHundreds(ByVal MyNumber)
Dim Result As String

If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)

If Mid(MyNumber, 1, 1) < "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If

If Mid(MyNumber, 2, 1) < "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function



Function GetTens(TensText)
Dim Result As String

Result = ""
If Val(Left(TensText, 1)) = 1 Then
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1))
End If
GetTens = Result
End Function

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

--

Regards,
Nigel




"RAVI VARMA" wrote in message ...
I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents.



rekfis wrote:

2007 excel formula spellnumber
22-Aug-08

I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents. I have tried the vb module from article id 213360 but
it
erros out at the cents = gettens line. any help would be great!

Previous Posts In This Thread:

On Friday, August 22, 2008 8:06 AM
rekfis wrote:

2007 excel formula spellnumber
I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents. I have tried the vb module from article id 213360 but
it
erros out at the cents = gettens line. any help would be great!

On Friday, August 22, 2008 9:41 AM
Rick Rothstein \(MVP - VB\) wrote:

It sounds like you only copied the SpellNumber function and not the other
3
It sounds like you only copied the SpellNumber function and not the other
3
helper functions located under it on that webpage... you need to copy
*all*
the code shown there into your code window.

Rick


"rekfish" wrote in message
...

On Friday, August 22, 2008 10:43 AM
rekfis wrote:

Rick,I corrected the vb module with all info. I now get a name #name?
error?
Rick,

I corrected the vb module with all info. I now get a name #name? error?

Thanks
rekfish

"Rick Rothstein (MVP - VB)" wrote:

On Friday, August 22, 2008 10:53 AM
Rick Rothstein \(MVP - VB\) wrote:

It works for me.
It works for me. You did put all the code in a Module, not a UserForm or
Sheet code window, right? If you are not sure, you get a Module by
clicking
Insert/Module from the VB editor's menu bar. UDFs must be placed in a
regular Module (not a Class Module) in order to work.

Rick


"rekfish" wrote in message
...

On Friday, August 22, 2008 3:13 PM
rekfis wrote:

Rick,Thanks so much!!
Rick,

Thanks so much!! I removed the module completely and recreated it and it
worked this time.

Thanks again!

rekfish

"Rick Rothstein (MVP - VB)" wrote:

On Friday, August 07, 2009 1:05 AM
sunil jain wrote:

SpellNumber (help)
You are required to be a member to post replies. After logging in or
becoming a member, you will be redirected back to this page.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Missed PDC 2005? Here's the content Download Page
http://www.eggheadcafe.com/tutorials...5-heres-t.aspx

I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents.

I'm guessing from your example that you want to suppress the display of the
word "dollar(s)" from your text. Since you seem to have liked my function
originally, I have modified it for your request. Here is all the code I
posted previously modified to include an "ONLYCENTS" option for you (just
use "ONLYCENTS" for the optional last argument and only the word "cent(s)"
will be printed). Note that only the NumberAsText function itself has been
modified... all the rest of the code remains unchanged (in case you just
want to replace that one item only). The code is shown after my signature.

--
Rick (MVP - Excel)


Private sNumberText() As String

Public Function NumberAsText(NumberIn As Variant, Optional _
AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR_or_ONLYCENTS _
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_or_ONL YCENTS)
' 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" Or sStyle = "onlycents"
' 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 sStyle = "dollars" Then
If tmp = "One " Then
tmp = tmp & "Dollar"
Else
tmp = tmp & "Dollars"
End If
Else
tmp = RTrim(tmp)
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

Hello... I am looking for a modification of the spellnumber formula that will return what would show up if you wrote a check. For example, spellnumber(A1) would return "Fifteen thousand one hundred twenty and 01/100" if cell A1 is 15,120.01. Is this possible? Please advise.

I currently use Excel 2007. I have already inserted the spellnumber module found here into my spreadsheet: http://support.microsoft.com/kb/213360. But I'm not a module writer and am not experienced enough to tear this apart and amend it to fit my needs.

Thanks, in advance, for any help you can offer me in this regard!

Best regards,
Sheri



Rick Rothstein wrote:

I am guessing from your example that you want to suppress the display of
02-Dec-09

I am guessing from your example that you want to suppress the display of the
word "dollar(s)" from your text. Since you seem to have liked my function
originally, I have modified it for your request. Here is all the code I
posted previously modified to include an "ONLYCENTS" option for you (just
use "ONLYCENTS" for the optional last argument and only the word "cent(s)"
will be printed). Note that only the NumberAsText function itself has been
modified... all the rest of the code remains unchanged (in case you just
want to replace that one item only). The code is shown after my signature.

--
Rick (MVP - Excel)


Private sNumberText() As String

Public Function NumberAsText(NumberIn As Variant, Optional _
AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR_or_ONLYCENTS _
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_or_ONL YCENTS)
' 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" Or sStyle = "onlycents"
' 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 = ""

Previous Posts In This Thread:

On Friday, August 22, 2008 8:06 AM
rekfis wrote:

2007 excel formula spellnumber
I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents. I have tried the vb module from article id 213360 but it
erros out at the cents = gettens line. any help would be great!

On Friday, August 22, 2008 9:41 AM
Rick Rothstein \(MVP - VB\) wrote:

It sounds like you only copied the SpellNumber function and not the other 3
It sounds like you only copied the SpellNumber function and not the other 3
helper functions located under it on that webpage... you need to copy *all*
the code shown there into your code window.

Rick


"rekfish" wrote in message
...

On Friday, August 22, 2008 10:43 AM
rekfis wrote:

Rick,I corrected the vb module with all info. I now get a name #name? error?
Rick,

I corrected the vb module with all info. I now get a name #name? error?

Thanks
rekfish

"Rick Rothstein (MVP - VB)" wrote:

On Friday, August 22, 2008 10:53 AM
Rick Rothstein \(MVP - VB\) wrote:

It works for me.
It works for me. You did put all the code in a Module, not a UserForm or
Sheet code window, right? If you are not sure, you get a Module by clicking
Insert/Module from the VB editor's menu bar. UDFs must be placed in a
regular Module (not a Class Module) in order to work.

Rick


"rekfish" wrote in message
...

On Friday, August 22, 2008 3:13 PM
rekfis wrote:

Rick,Thanks so much!!
Rick,

Thanks so much!! I removed the module completely and recreated it and it
worked this time.

Thanks again!

rekfish

"Rick Rothstein (MVP - VB)" wrote:

On Friday, August 07, 2009 1:05 AM
sunil jain wrote:

SpellNumber (help)
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.

On Wednesday, December 02, 2009 1:17 AM
RAVI VARMA wrote:

ADVANCE COURSE FOR EXCEL FORMULA 2007
I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents.

On Wednesday, December 02, 2009 12:48 PM
Nigel wrote:

The following function will convert a numerical value to a text string...
The following function will convert a numerical value to a text string... in
Pounds and pence but easily adapted to Dollars & Cents. To use it send the
value to the function SpellNumber(123.45) or by reference in a cell
=SpellNumber(A1)

Function SpellNumber(ByVal MyNumber)
Dim Pounds, Pence, Temp
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

MyNumber = Trim(Str(MyNumber))

DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace 0 Then
Pence = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber < ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp < "" Then Pounds = Temp & Place(Count) & Pounds
If Len(MyNumber) 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case Pounds
Case ""
Pounds = "No Pounds"
Case "One"
Pounds = "One Pound"
Case Else
Pounds = Pounds & " Pounds"
End Select

Select Case Pence
Case ""
Pence = " and No Pence"
Case "One"
Pence = " and One Pence"
Case Else
Pence = " and " & Pence & " Pence"
End Select

SpellNumber = Pounds & Pence
End Function



Function GetHundreds(ByVal MyNumber)
Dim Result As String

If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)

If Mid(MyNumber, 1, 1) < "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If

If Mid(MyNumber, 2, 1) < "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function



Function GetTens(TensText)
Dim Result As String

Result = ""
If Val(Left(TensText, 1)) = 1 Then
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
Select Case Val(Left(TensText, 1))

On Wednesday, December 02, 2009 2:08 PM
Rick Rothstein wrote:

I am guessing from your example that you want to suppress the display of
I am guessing from your example that you want to suppress the display of the
word "dollar(s)" from your text. Since you seem to have liked my function
originally, I have modified it for your request. Here is all the code I
posted previously modified to include an "ONLYCENTS" option for you (just
use "ONLYCENTS" for the optional last argument and only the word "cent(s)"
will be printed). Note that only the NumberAsText function itself has been
modified... all the rest of the code remains unchanged (in case you just
want to replace that one item only). The code is shown after my signature.

--
Rick (MVP - Excel)


Private sNumberText() As String

Public Function NumberAsText(NumberIn As Variant, Optional _
AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR_or_ONLYCENTS _
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_or_ONL YCENTS)
' 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" Or sStyle = "onlycents"
' 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 = ""


Submitted via EggHeadCafe - Software Developer Portal of Choice
Making Silverlight Emulate Synchronous Requests
http://www.eggheadcafe.com/tutorials...ht-emulat.aspx