Re: Adding "and" to Spellnumber code

Thanks Bob. This is pretty close, but there's still a problem with the pesky
"and".
In this example - $152,050 our convention would be "One hundred AND Fifty
two thousand, AND Fifty dollars." This code leaves out the "and" before the
Fifty dollars.
Basically the digits each side of the separating comma are treated in the
same way as far as the "and" is concerned, so its "One hundred AND twenty
thousand", or "One hundred AND two thousand" then after the comma its again
"One hundred AND twenty dollars" or "One hundred AND two dollars" OR if there
are no hundreds, but there are tens or ones, it would be "AND fifty dollars"
or "AND two dollars"

To summarize, if there's anything following the thousands digit but before
the separating comma, its preceeded by "and", and if there's anything
following the hundreds digit, its preceeded by "and".

I don't have the knowledge yet to modify your code, but I'm learning a lot
through this process.

Thanks,
Ken G.


"Bob Phillips" wrote:

> Ken,
>
> Try this version
>
> Option Explicit
>
> Function SpellNumber(ByVal MyNumber)
> Dim ipos As Long
> Dim Dollars, Cents, Temp
> Dim DecimalPlace, Count
> ReDim Place(9) As String
> Place(2) = "Thousand "
> Place(3) = "Million "
> Place(4) = "Billion "
> Place(5) = " rillion "
> ' String representation of amount.
> MyNumber = Trim(Str(MyNumber))
> ' Position of decimal place 0 if none.
> DecimalPlace = InStr(MyNumber, ".")
> ' Convert cents and set MyNumber to dollar amount.
> If DecimalPlace > 0 Then
> Cents = 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 Dollars = Temp & Place(Count) & Dollars
> If Len(MyNumber) > 3 Then
> MyNumber = Left(MyNumber, Len(MyNumber) - 3)
> Else
> MyNumber = ""
> End If
> Count = Count + 1
> Loop
> Select Case Dollars
> Case ""
> Dollars = "No Dollars"
> Case "One"
> Dollars = "One Dollar"
> Case Else
> Dollars = Dollars & " Dollars"
> End Select
> Select Case Cents
> Case ""
> Cents = " and No Cents"
> Case "One"
> Cents = " and One Cent"
> Case Else
> Cents = " and " & Cents & " Cents"
> End Select
> Dollars = Replace(Dollars, "Hundred ", "Hundred and ")
> Dollars = Replace(Dollars, "Hundred and Thousand", "Hundred Thousand")
> Dollars = Replace(Dollars, "Hundred and Million", "Hundred Million")
> Dollars = Replace(Dollars, "Thousand", "Thousand,")
> Dollars = Replace(Dollars, "Million", "Million,")
> SpellNumber = Dollars & Cents
> End Function
>
> ' Converts a number from 100-999 into text
> Function GetHundreds(ByVal MyNumber)
> Dim Result As String
> If Val(MyNumber) = 0 Then Exit Function
> MyNumber = Right("000" & MyNumber, 3)
> ' Convert the hundreds place.
> If Mid(MyNumber, 1, 1) <> "0" Then
> Result = GetDigit(Mid(MyNumber, 1, 1)) & "Hundred "
> End If
> ' Convert the tens and ones place.
> 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
>
> ' Converts a number from 10 to 99 into text.
> Function GetTens(TensText)
> Dim Result As String
> Result = "" ' Null out the temporary function value.
> If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
> 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 ' If value between 20-99...
> 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)) ' Retrieve ones place.
> End If
> GetTens = Result
> End Function
>
> ' Converts a number from 1 to 9 into text.
> 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
>
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Ken G." <KenG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:68E14747-4360-4A8D-A1C1-01BD0EB0229C@xxxxxxxxxxxxxxxx
> > Peo, this works for a number like $123,456, but for say $100,510 where
> there
> > is a neat hundred or hundreds in the thousands field, it says "One hundred
> > and thousand five hundred and ten dollars" The first "and" shouldn't be
> > there. We would write this as "One hundred thousand, five hundred and ten
> > dollars". I've tried adjusting your formula but I finish up with the word
> > "dollars" coming in after the thousands as well as at the end.
> >
> > I couldn't try Bob's solution as there's an un-dimensioned variable
> called
> > "Count" in there and I don't know what it refers to.
> >
> > "Peo Sjoblom" wrote:
> >
> > > Is it always after any Hundred unless an even Hundred? If so you maybe
> you
> > > could use something like
> > >
> > >
> =IF(MOD(A1,100)=0,spellnumber(A1),SUBSTITUTE(spellnumber(A1),"Hundred","Hund
> red
> > > and"))
> > >
> > > --
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > (No private emails please)
> > >
> > >
> > > "Ken G." <KenG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > news:F890BB7B-E5CA-4F17-8C2E-DB6CD4525DCD@xxxxxxxxxxxxxxxx
> > > > Here in Australia $261,345 would be written as "Two hundred and sixty
> one
> > > > thousand three hundred and forty five dollars" Note the two "ands" in
> > > > there.
> > > > Can some kind soul tell me how to modify the Spellnumber routine to do
> > > > this.
> > > > I've tried but can't get it right with either too many, or not enough
> > > > "and"s
> > > > appearing, or appearing in the wrong place. (Its interesting that we
> say
> > > > "two
> > > > hundred AND one" or "two hundred AND sixty one", but we don't say
> "forty
> > > > AND
> > > > five")
> > > >
> > > > Thanks,
> > >
> > >
>
>
>
.

Relevant Pages

  • Re: Treeview
    ... I'll have a B please Bob. ... > Public DirectoryPath as string ... > Dim BaseFilePath as FilePath 'the root object> 'open a stream from the file and read each line into a local variable. ... > 'now get the parent address which should be the same for every line ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Formating string as "#,###.00"
    ... Bob, ... Dim i As Long, iStart As Long, iEnd As Long ... Dim str1 As String ... Exit Sub 'If the Target Cell does not have a formula Exit Macro ...
    (microsoft.public.excel.programming)
  • Re: Treeview
    ... I'll have a B please Bob. ... "Nick Stansbury" wrote in message ... >>> Private Function BuildNodeList(ByVal currentPath As String, ... >>>> Dim TempCollection as new Collection ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Treeview
    ... I'll have a B please Bob. ... >> Private Function BuildNodeList(ByVal currentPath As String, ... >>> Dim TempCollection as new Collection ... 'now get the parent address which should be the same for every ...
    (microsoft.public.dotnet.languages.vb)
  • RE: How can i change this VBA project According to Indian Numeric
    ... > Function SpellNumber(ByVal MyNumber) ... > Dim Dollars, Cents, Temp ... > ' String representation of amount. ... > ' Convert cents and set MyNumber to dollar amount. ...
    (microsoft.public.excel.misc)
Loading