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_ONLYCENTS)
' 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:
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!
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
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
:
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
Rick,Thanks so much!!
Rick,
Thanks so much!! I removed the module completely and recreated it and it
worked this time.
Thanks again!
rekfish
:
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.
ADVANCE COURSE FOR EXCEL FORMULA 2007
I need to convert dollar value to english word, 100.21 to one hundred and
twenty one cents.
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))
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_ONLYCENTS)
' 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/tutorial...1-c5c717c9b184/making-silverlight-emulat.aspx
KINDLY MODIFY THIS SO I'LL JUST COPY -PASTE THIS TO MY MODULE) PLEAAASEEE....THANK YOU.