To say number in words
Hello,
I appreciate if any one canhelp me in saying number in words For example in Cell A1 : 1000 cell A2: Rp. in Cell B1 will automatically say : One thousand rupiah Thanks in advance -- H. Frank Situmorang |
To say number in words
Here is a function that you can call using say =SpellNumber(1000)
Option Explicit '-----------------------------------------------------------*------ Function SpellNumber(ByVal MyNumber, _ Optional incRupees As Boolean = True) '-----------------------------------------------------------*------ Dim Crores, Lakhs, Rupees, Paise, Temp Dim DecimalPlace As Long, Count As Long Dim myLakhs, myCrores 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 Paise and set MyNumber to Rupees amount. If DecimalPlace 0 Then Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If myCrores = MyNumber \ 10000000 myLakhs = (MyNumber - myCrores * 10000000) \ 100000 MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000 Count = 1 Do While myCrores < "" Temp = GetHundreds(Right(myCrores, 3)) If Temp < "" Then Crores = Temp & Place(Count) & Crores If Len(myCrores) 3 Then myCrores = Left(myCrores, Len(myCrores) - 3) Else myCrores = "" End If Count = Count + 1 Loop Count = 1 Do While myLakhs < "" Temp = GetHundreds(Right(myLakhs, 3)) If Temp < "" Then Lakhs = Temp & Place(Count) & Lakhs If Len(myLakhs) 3 Then myLakhs = Left(myLakhs, Len(myLakhs) - 3) Else myLakhs = "" End If Count = Count + 1 Loop Count = 1 Do While MyNumber < "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp < "" Then Rupees = Temp & Place(Count) & Rupees If Len(MyNumber) 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Crores Case "": Crores = "" Case "One": Crores = " One Crore " Case Else: Crores = Crores & " Crores " End Select Select Case Lakhs Case "": Lakhs = "" Case "One": Lakhs = " One Lakh " Case Else: Lakhs = Lakhs & " Lakhs " End Select Select Case Rupees Case "": Rupees = "Zero " Case "One": Rupees = "One " Case Else: Rupees = Rupees End Select Select Case Paise Case "": Paise = " and Paise Zero Only " Case "One": Paise = " and Paise One Only " Case Else: Paise = " and Paise " & Paise & " Only " End Select SpellNumber = IIf(incRupees, "Rupees ", "") & Crores & Lakhs & Rupees & Paise 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 (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... Hello, I appreciate if any one canhelp me in saying number in words For example in Cell A1 : 1000 cell A2: Rp. in Cell B1 will automatically say : One thousand rupiah Thanks in advance -- H. Frank Situmorang |
To say number in words
Thanks Bob for your quick response. But I do not know what to do, should we
put =SpellNumber(1000) in Celll where we want it saying?, where shall we put the VBA. Is it a macro? -- H. Frank Situmorang "Bob Phillips" wrote: Here is a function that you can call using say =SpellNumber(1000) Option Explicit '-----------------------------------------------------------Â*------ Function SpellNumber(ByVal MyNumber, _ Optional incRupees As Boolean = True) '-----------------------------------------------------------Â*------ Dim Crores, Lakhs, Rupees, Paise, Temp Dim DecimalPlace As Long, Count As Long Dim myLakhs, myCrores 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 Paise and set MyNumber to Rupees amount. If DecimalPlace 0 Then Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If myCrores = MyNumber \ 10000000 myLakhs = (MyNumber - myCrores * 10000000) \ 100000 MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000 Count = 1 Do While myCrores < "" Temp = GetHundreds(Right(myCrores, 3)) If Temp < "" Then Crores = Temp & Place(Count) & Crores If Len(myCrores) 3 Then myCrores = Left(myCrores, Len(myCrores) - 3) Else myCrores = "" End If Count = Count + 1 Loop Count = 1 Do While myLakhs < "" Temp = GetHundreds(Right(myLakhs, 3)) If Temp < "" Then Lakhs = Temp & Place(Count) & Lakhs If Len(myLakhs) 3 Then myLakhs = Left(myLakhs, Len(myLakhs) - 3) Else myLakhs = "" End If Count = Count + 1 Loop Count = 1 Do While MyNumber < "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp < "" Then Rupees = Temp & Place(Count) & Rupees If Len(MyNumber) 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Crores Case "": Crores = "" Case "One": Crores = " One Crore " Case Else: Crores = Crores & " Crores " End Select Select Case Lakhs Case "": Lakhs = "" Case "One": Lakhs = " One Lakh " Case Else: Lakhs = Lakhs & " Lakhs " End Select Select Case Rupees Case "": Rupees = "Zero " Case "One": Rupees = "One " Case Else: Rupees = Rupees End Select Select Case Paise Case "": Paise = " and Paise Zero Only " Case "One": Paise = " and Paise One Only " Case Else: Paise = " and Paise " & Paise & " Only " End Select SpellNumber = IIf(incRupees, "Rupees ", "") & Crores & Lakhs & Rupees & Paise 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 (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... Hello, I appreciate if any one canhelp me in saying number in words For example in Cell A1 : 1000 cell A2: Rp. in Cell B1 will automatically say : One thousand rupiah Thanks in advance -- H. Frank Situmorang |
To say number in words
Yes it is a macro. See http://www.mvps.org/dmcritchie/excel/getstarted.htm
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... Thanks Bob for your quick response. But I do not know what to do, should we put =SpellNumber(1000) in Celll where we want it saying?, where shall we put the VBA. Is it a macro? -- H. Frank Situmorang "Bob Phillips" wrote: Here is a function that you can call using say =SpellNumber(1000) Option Explicit '-----------------------------------------------------------*------ Function SpellNumber(ByVal MyNumber, _ Optional incRupees As Boolean = True) '-----------------------------------------------------------*------ Dim Crores, Lakhs, Rupees, Paise, Temp Dim DecimalPlace As Long, Count As Long Dim myLakhs, myCrores 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 Paise and set MyNumber to Rupees amount. If DecimalPlace 0 Then Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If myCrores = MyNumber \ 10000000 myLakhs = (MyNumber - myCrores * 10000000) \ 100000 MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000 Count = 1 Do While myCrores < "" Temp = GetHundreds(Right(myCrores, 3)) If Temp < "" Then Crores = Temp & Place(Count) & Crores If Len(myCrores) 3 Then myCrores = Left(myCrores, Len(myCrores) - 3) Else myCrores = "" End If Count = Count + 1 Loop Count = 1 Do While myLakhs < "" Temp = GetHundreds(Right(myLakhs, 3)) If Temp < "" Then Lakhs = Temp & Place(Count) & Lakhs If Len(myLakhs) 3 Then myLakhs = Left(myLakhs, Len(myLakhs) - 3) Else myLakhs = "" End If Count = Count + 1 Loop Count = 1 Do While MyNumber < "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp < "" Then Rupees = Temp & Place(Count) & Rupees If Len(MyNumber) 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Crores Case "": Crores = "" Case "One": Crores = " One Crore " Case Else: Crores = Crores & " Crores " End Select Select Case Lakhs Case "": Lakhs = "" Case "One": Lakhs = " One Lakh " Case Else: Lakhs = Lakhs & " Lakhs " End Select Select Case Rupees Case "": Rupees = "Zero " Case "One": Rupees = "One " Case Else: Rupees = Rupees End Select Select Case Paise Case "": Paise = " and Paise Zero Only " Case "One": Paise = " and Paise One Only " Case Else: Paise = " and Paise " & Paise & " Only " End Select SpellNumber = IIf(incRupees, "Rupees ", "") & Crores & Lakhs & Rupees & Paise 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 (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... Hello, I appreciate if any one canhelp me in saying number in words For example in Cell A1 : 1000 cell A2: Rp. in Cell B1 will automatically say : One thousand rupiah Thanks in advance -- H. Frank Situmorang |
To say number in words
Thanks Bob for all your help, but I appreciate if you could help me again. It
works in spelling the number, but I do not know, when I changed the Rupees to Rupiah it is ok, but this is the outcome for example: 2,500,000,- Rupiah Twenty Five Lakhs Zero and Paise Zero Only I do not understand the meaning of lakhs dan paise, can you help me just the way to spell it in English?. Although it is in rupiah it does not matter if we can spell it like English System. Thanks a lot -- H. Frank Situmorang "Bob Phillips" wrote: Yes it is a macro. See http://www.mvps.org/dmcritchie/excel/getstarted.htm -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... Thanks Bob for your quick response. But I do not know what to do, should we put =SpellNumber(1000) in Celll where we want it saying?, where shall we put the VBA. Is it a macro? -- H. Frank Situmorang "Bob Phillips" wrote: Here is a function that you can call using say =SpellNumber(1000) Option Explicit '-----------------------------------------------------------Â*------ Function SpellNumber(ByVal MyNumber, _ Optional incRupees As Boolean = True) '-----------------------------------------------------------Â*------ Dim Crores, Lakhs, Rupees, Paise, Temp Dim DecimalPlace As Long, Count As Long Dim myLakhs, myCrores 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 Paise and set MyNumber to Rupees amount. If DecimalPlace 0 Then Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If myCrores = MyNumber \ 10000000 myLakhs = (MyNumber - myCrores * 10000000) \ 100000 MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000 Count = 1 Do While myCrores < "" Temp = GetHundreds(Right(myCrores, 3)) If Temp < "" Then Crores = Temp & Place(Count) & Crores If Len(myCrores) 3 Then myCrores = Left(myCrores, Len(myCrores) - 3) Else myCrores = "" End If Count = Count + 1 Loop Count = 1 Do While myLakhs < "" Temp = GetHundreds(Right(myLakhs, 3)) If Temp < "" Then Lakhs = Temp & Place(Count) & Lakhs If Len(myLakhs) 3 Then myLakhs = Left(myLakhs, Len(myLakhs) - 3) Else myLakhs = "" End If Count = Count + 1 Loop Count = 1 Do While MyNumber < "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp < "" Then Rupees = Temp & Place(Count) & Rupees If Len(MyNumber) 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Crores Case "": Crores = "" Case "One": Crores = " One Crore " Case Else: Crores = Crores & " Crores " End Select Select Case Lakhs Case "": Lakhs = "" Case "One": Lakhs = " One Lakh " Case Else: Lakhs = Lakhs & " Lakhs " End Select Select Case Rupees Case "": Rupees = "Zero " Case "One": Rupees = "One " Case Else: Rupees = Rupees End Select Select Case Paise Case "": Paise = " and Paise Zero Only " Case "One": Paise = " and Paise One Only " Case Else: Paise = " and Paise " & Paise & " Only " End Select SpellNumber = IIf(incRupees, "Rupees ", "") & Crores & Lakhs & Rupees & Paise 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 (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... Hello, I appreciate if any one canhelp me in saying number in words For example in Cell A1 : 1000 cell A2: Rp. in Cell B1 will automatically say : One thousand rupiah Thanks in advance -- H. Frank Situmorang |
To say number in words
Thanks Bob for your idea, after searching these threads I found it on how to
say it in English version. With many thanks -- H. Frank Situmorang "Bob Phillips" wrote: Yes it is a macro. See http://www.mvps.org/dmcritchie/excel/getstarted.htm -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... Thanks Bob for your quick response. But I do not know what to do, should we put =SpellNumber(1000) in Celll where we want it saying?, where shall we put the VBA. Is it a macro? -- H. Frank Situmorang "Bob Phillips" wrote: Here is a function that you can call using say =SpellNumber(1000) Option Explicit '-----------------------------------------------------------Â*------ Function SpellNumber(ByVal MyNumber, _ Optional incRupees As Boolean = True) '-----------------------------------------------------------Â*------ Dim Crores, Lakhs, Rupees, Paise, Temp Dim DecimalPlace As Long, Count As Long Dim myLakhs, myCrores 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 Paise and set MyNumber to Rupees amount. If DecimalPlace 0 Then Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If myCrores = MyNumber \ 10000000 myLakhs = (MyNumber - myCrores * 10000000) \ 100000 MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000 Count = 1 Do While myCrores < "" Temp = GetHundreds(Right(myCrores, 3)) If Temp < "" Then Crores = Temp & Place(Count) & Crores If Len(myCrores) 3 Then myCrores = Left(myCrores, Len(myCrores) - 3) Else myCrores = "" End If Count = Count + 1 Loop Count = 1 Do While myLakhs < "" Temp = GetHundreds(Right(myLakhs, 3)) If Temp < "" Then Lakhs = Temp & Place(Count) & Lakhs If Len(myLakhs) 3 Then myLakhs = Left(myLakhs, Len(myLakhs) - 3) Else myLakhs = "" End If Count = Count + 1 Loop Count = 1 Do While MyNumber < "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp < "" Then Rupees = Temp & Place(Count) & Rupees If Len(MyNumber) 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Crores Case "": Crores = "" Case "One": Crores = " One Crore " Case Else: Crores = Crores & " Crores " End Select Select Case Lakhs Case "": Lakhs = "" Case "One": Lakhs = " One Lakh " Case Else: Lakhs = Lakhs & " Lakhs " End Select Select Case Rupees Case "": Rupees = "Zero " Case "One": Rupees = "One " Case Else: Rupees = Rupees End Select Select Case Paise Case "": Paise = " and Paise Zero Only " Case "One": Paise = " and Paise One Only " Case Else: Paise = " and Paise " & Paise & " Only " End Select SpellNumber = IIf(incRupees, "Rupees ", "") & Crores & Lakhs & Rupees & Paise 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 (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... Hello, I appreciate if any one canhelp me in saying number in words For example in Cell A1 : 1000 cell A2: Rp. in Cell B1 will automatically say : One thousand rupiah Thanks in advance -- H. Frank Situmorang |
To say number in words
On Wednesday, February 20, 2008 2:31:26 PM UTC+5:30, Bob Phillips wrote:
Here is a function that you can call using say =SpellNumber(1000) Option Explicit '-----------------------------------------------------------*------ Function SpellNumber(ByVal MyNumber, _ Optional incRupees As Boolean = True) '-----------------------------------------------------------*------ Dim Crores, Lakhs, Rupees, Paise, Temp Dim DecimalPlace As Long, Count As Long Dim myLakhs, myCrores 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 Paise and set MyNumber to Rupees amount. If DecimalPlace 0 Then Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If myCrores = MyNumber \ 10000000 myLakhs = (MyNumber - myCrores * 10000000) \ 100000 MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000 Count = 1 Do While myCrores < "" Temp = GetHundreds(Right(myCrores, 3)) If Temp < "" Then Crores = Temp & Place(Count) & Crores If Len(myCrores) 3 Then myCrores = Left(myCrores, Len(myCrores) - 3) Else myCrores = "" End If Count = Count + 1 Loop Count = 1 Do While myLakhs < "" Temp = GetHundreds(Right(myLakhs, 3)) If Temp < "" Then Lakhs = Temp & Place(Count) & Lakhs If Len(myLakhs) 3 Then myLakhs = Left(myLakhs, Len(myLakhs) - 3) Else myLakhs = "" End If Count = Count + 1 Loop Count = 1 Do While MyNumber < "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp < "" Then Rupees = Temp & Place(Count) & Rupees If Len(MyNumber) 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Crores Case "": Crores = "" Case "One": Crores = " One Crore " Case Else: Crores = Crores & " Crores " End Select Select Case Lakhs Case "": Lakhs = "" Case "One": Lakhs = " One Lakh " Case Else: Lakhs = Lakhs & " Lakhs " End Select Select Case Rupees Case "": Rupees = "Zero " Case "One": Rupees = "One " Case Else: Rupees = Rupees End Select Select Case Paise Case "": Paise = " and Paise Zero Only " Case "One": Paise = " and Paise One Only " Case Else: Paise = " and Paise " & Paise & " Only " End Select SpellNumber = IIf(incRupees, "Rupees ", "") & Crores & Lakhs & Rupees & Paise 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 (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... Hello, I appreciate if any one canhelp me in saying number in words For example in Cell A1 : 1000 cell A2: Rp. in Cell B1 will automatically say : One thousand rupiah Thanks in advance -- H. Frank Situmorang |
All times are GMT +1. The time now is 02:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com