![]() |
Adding "and" to Spellnumber code
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, |
Adding "and" to Spellnumber code
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(spell number(A1),"Hundred","Hundred and")) -- Regards, Peo Sjoblom (No private emails please) "Ken G." wrote in message ... 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, |
Adding "and" to Spellnumber code
Yes, its only after "hundred" if more follows. Where would I put your code
into the existing code? "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(spell number(A1),"Hundred","Hundred and")) -- Regards, Peo Sjoblom (No private emails please) "Ken G." wrote in message ... 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, |
Adding "and" to Spellnumber code
Assuming the original function is called SpellNumber, just use Peo's formula
rather than =SpellNumber(A1) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ken G." wrote in message ... Yes, its only after "hundred" if more follows. Where would I put your code into the existing code? "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(spell number(A1),"Hundred","Hund red and")) -- Regards, Peo Sjoblom (No private emails please) "Ken G." wrote in message ... 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, |
Adding "and" to Spellnumber code
Hi Ken
Either modify Peo's formula to simply =SUBSTITUTE(spellnumber(A1),"Hundred","Hundred and")) or modify part of the Spellnumber function GetHundreds from 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 to the following MyNumber = Right("000" & MyNumber, 3) 'Convert the hundreds place If Mid(MyNumber, 1, 1) = "0" And Count < 2 Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " No Hundreds and " End If If Mid(MyNumber, 1, 1) < "0" Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred and " End If I presume you have changed Pounds and pence in the function to Dollars and Cents -- Regards Roger Govier "Ken G." wrote in message ... Yes, its only after "hundred" if more follows. Where would I put your code into the existing code? "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(spell number(A1),"Hundred","Hundred and")) -- Regards, Peo Sjoblom (No private emails please) "Ken G." wrote in message ... 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, |
Adding "and" to Spellnumber code
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(spell number(A1),"Hundred","Hundred and")) -- Regards, Peo Sjoblom (No private emails please) "Ken G." wrote in message ... 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, |
Adding "and" to Spellnumber code
Hi Ken
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. I think it was me, not Bob, who posted the revision to the Spell number code. The copy of Spellnumber I found on Google did have Count defined. The code loops through the GetHundreds routine a number of times, dependant upon the size of the figure being converted. The first time through, I thought it would be more correct to express the value £123,056 as One Hundred and Twenty Three Thousand No Hundreds and Fifty Six Pounds and No Pence rather than One Hundred and Twenty Three Thousand and Fifty Six Pounds and No Pence Whereas, I didn't think it would be right in the case of £23,056 to have No Hundreds and Twenty Three Thousand No Hundreds and Fifty Six Pounds and No Pence. (NB for Pounds read Dollars in your case) It's up to you, Ken. If you don't want the No Hundreds bit, just comment out the 3 lines in the section of code I posted. and just leave the only amendment as being the addition of the word "and" after "Hundreds" in the original code. For the sake of completeness, I enclose the complete sub-function from within the Spellnumber code that does the conversion of hundreds (The code is not mine originally, I do not know the source, I merely amended what I found with a Google search following your initial posting). '******************************************* ' 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" And Count < 2 Then ' Result = GetDigit(Mid(MyNumber, 1, 1)) & " No Hundreds and " ' End If If Mid(MyNumber, 1, 1) < "0" Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred and " 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 And the part of the code which Dims Count (incorrectly, it should be Dim Count as Integer) and uses it '****************' Main Function *'**************** 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 " ' String representation of amount MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none DecimalPlace = InStr(MyNumber, ".") 'Convert Pence and set MyNumber to Pound amount 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 ........ ............ I hope this clears things up and allows you to make the modification accordingly. -- Regards Roger Govier "Ken G." wrote in message ... 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(spell number(A1),"Hundred","Hundred and")) -- Regards, Peo Sjoblom (No private emails please) "Ken G." wrote in message ... 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, |
Adding "and" to Spellnumber code
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." wrote in message ... 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(spell number(A1),"Hundred","Hund red and")) -- Regards, Peo Sjoblom (No private emails please) "Ken G." wrote in message ... 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, |
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." wrote in message ... 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(spell number(A1),"Hundred","Hund red and")) -- Regards, Peo Sjoblom (No private emails please) "Ken G." wrote in message ... 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, |
Adding "and" to Spellnumber code
Thanks Peo, Bob, and Roger for your help with this. I've managed to find my
own solution by modifying the original spellnumber code. I changed this ... ' 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 to this ... ' Convert the tens and ones place. If Mid(MyNumber, 2, 1) < "0" Then Result = Result & "and " & GetTens(Mid(MyNumber, 2)) Else If Mid(MyNumber, 3, 1) < "0" Then Result = Result & "and " & GetDigit(Mid(MyNumber, 3)) Else Result = Result & GetDigit(Mid(MyNumber, 3)) End If End If GetHundreds = Result ..... and the end result is exactly what I was trying to do. "Ken G." wrote: 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, |
Adding "and" to Spellnumber code
"Ken G." wrote: 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, |
All times are GMT +1. The time now is 07:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com