SVC said:Dear BOB,
Hi!
i read the mail & followed the link. pl go through my prev reply carefully
your reply in in it.
any way i will try to make it as simple as possible for you now hopefully.
Money [or] numbers in india is split into units, tens, hundreds, thousands,
ten thousands, lakhs, ten lakhs, crorers, ten crorers
from left to right i shall detail you how each valu is called so that you
can code easily.
After decimel point (upto 2 decimal point) it is called paise (as cents in
US)
from left moving towards right each place is represented as follows
the eg value is Rs. 12,34,56,789.11 is called as twelve crores thirty four
lakhs fifty six thousand seven hundered and eleven paise
now
9 = units
8 = tens
6 = thousands
5 = ten thousands
4 = lakh
3 = ten lakh
2 = crore
1 = ten crores
if you follow the above meathod my orginal value which is 12345678.11 will
represent twelve crores thirty four lakhs fifty six thousand seven hundered
and eleven paise
in your code the same value returns
Rupees OneBillion Two Hundred Twenty ThreeMillion Four Hundred Fifty
SixThousand Seven Hundred Eighty Nine and Paise Eleven Only
I hope you understand what we want exactly.
rgds
Vinoth. S
Bob Phillips said:I answered a similar question to this a few weeks back. Check out if this
helps, but as I recall, the OP didn't answer my last question
http://tinyurl.com/4m4sp
--
HTH
RP
(remove nothere from the email address if mailing direct)
to
my
Bob Phillips said:Vinoth,
How about this?
Function SpellNumber(ByVal MyNumber)
Dim Crores, Lakhs, Rupees, Paise, Temp
Dim DecimalPlace, 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 = "Rupees Zero "
Case "One": Rupees = "Rupee One "
Case Else: Rupees = "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 = 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
RP
(remove nothere from the email address if mailing direct)
SVC said:Dear BOB,
Hi!
i read the mail & followed the link. pl go through my prev reply
carefully
your reply in in it.
any way i will try to make it as simple as possible for you now
hopefully.
Money [or] numbers in india is split into units, tens, hundreds, thousands,
ten thousands, lakhs, ten lakhs, crorers, ten crorers
from left to right i shall detail you how each valu is called so that you
can code easily.
After decimel point (upto 2 decimal point) it is called paise (as cents
in
US)
from left moving towards right each place is represented as follows
the eg value is Rs. 12,34,56,789.11 is called as twelve crores thirty four
lakhs fifty six thousand seven hundered and eleven paise
now
9 = units
8 = tens
6 = thousands
5 = ten thousands
4 = lakh
3 = ten lakh
2 = crore
1 = ten crores
if you follow the above meathod my orginal value which is 12345678.11
will
represent twelve crores thirty four lakhs fifty six thousand seven hundered
and eleven paise
in your code the same value returns
Rupees OneBillion Two Hundred Twenty ThreeMillion Four Hundred Fifty
SixThousand Seven Hundred Eighty Nine and Paise Eleven Only
I hope you understand what we want exactly.
rgds
Vinoth. S
Bob Phillips said:I answered a similar question to this a few weeks back. Check out if
this
helps, but as I recall, the OP didn't answer my last question
http://tinyurl.com/4m4sp
--
HTH
RP
(remove nothere from the email address if mailing direct)
The faq is useful for the american style but i need the indian style
which
is detailed below. please help at the earliest in the group or direct to
my
mail id which is (e-mail address removed)
Currency symbol = Rs.
Currency names = Rupees(as Dollors) & Paise(as Cents)
denomations = units, tens, hundreds, thousands, ten thousands, lakhs, ten
lakhs, crorers, ten crorers
the digit sepration from the left to right is in 3 by 2 by 2 by 2
formation.
eg: Rs. 12,34,56,789.11 is called as twelve crores thirty four lakhs
fifty
six thousand seven hundered and eleven paise
See
http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text
--
HTH
RP
(remove nothere from the email address if mailing direct)
I want to convert the amount entered in excel sheet to their
respective
text
value.
eg: 1,24,525
to be converted as
one lakh twenty four thousand five hundered and twenty five.
SVC said:Dear Bob,
Thanks a million it worked just as i wanted. i am sure many many people will
be benifitted from this. there is a small problem
the result i get in the excell cell is
Twelve Crores Thirty Four Lakhs Rupees Fifty Six Thousand Seven Hundred
Eighty Nine and Paise Eleven Only
the word rupees appears in between it should appear at the begining or never
appear. pl mail me what changes i should make in the vb code for this
effect.
rgds
vinoth.
Bob Phillips said:Vinoth,
How about this?
Function SpellNumber(ByVal MyNumber)
Dim Crores, Lakhs, Rupees, Paise, Temp
Dim DecimalPlace, 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 = "Rupees Zero "
Case "One": Rupees = "Rupee One "
Case Else: Rupees = "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 = 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
RP
(remove nothere from the email address if mailing direct)
directSVC said:Dear BOB,
Hi!
i read the mail & followed the link. pl go through my prev reply
carefully
your reply in in it.
any way i will try to make it as simple as possible for you now
hopefully.
Money [or] numbers in india is split into units, tens, hundreds, thousands,
ten thousands, lakhs, ten lakhs, crorers, ten crorers
from left to right i shall detail you how each valu is called so that you
can code easily.
After decimel point (upto 2 decimal point) it is called paise (as cents
in
US)
from left moving towards right each place is represented as follows
the eg value is Rs. 12,34,56,789.11 is called as twelve crores thirty four
lakhs fifty six thousand seven hundered and eleven paise
now
9 = units
8 = tens
6 = thousands
5 = ten thousands
4 = lakh
3 = ten lakh
2 = crore
1 = ten crores
if you follow the above meathod my orginal value which is 12345678.11
will
represent twelve crores thirty four lakhs fifty six thousand seven hundered
and eleven paise
in your code the same value returns
Rupees OneBillion Two Hundred Twenty ThreeMillion Four Hundred Fifty
SixThousand Seven Hundred Eighty Nine and Paise Eleven Only
I hope you understand what we want exactly.
rgds
Vinoth. S
I answered a similar question to this a few weeks back. Check out if
this
helps, but as I recall, the OP didn't answer my last question
http://tinyurl.com/4m4sp
--
HTH
RP
(remove nothere from the email address if mailing direct)
The faq is useful for the american style but i need the indian style
which
is detailed below. please help at the earliest in the group or
tolakhs,my
mail id which is (e-mail address removed)
Currency symbol = Rs.
Currency names = Rupees(as Dollors) & Paise(as Cents)
denomations = units, tens, hundreds, thousands, ten thousands,
tenlakhs, crorers, ten crorers
the digit sepration from the left to right is in 3 by 2 by 2 by 2
formation.
eg: Rs. 12,34,56,789.11 is called as twelve crores thirty four lakhs
fifty
six thousand seven hundered and eleven paise
See
http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text
--
HTH
RP
(remove nothere from the email address if mailing direct)
I want to convert the amount entered in excel sheet to their
respective
text
value.
eg: 1,24,525
to be converted as
one lakh twenty four thousand five hundered and twenty five.
Bob Phillips said:Vinoth,
Do you mean like this
Rupees Twelve Crores Thirty Four Lakhs Fifty Six Thousand Seven Hundred
Eighty Nine and Paise Eleven Only
or
Twelve Crores Thirty Four Lakhs Fifty Six Thousand Seven Hundred Eighty Nine
and Paise Eleven Only
I put Rupees there as I was told that was how it is represented in India
(but that was before I learnt about Lakhs and Crores).
Which way do you prefer?
--
HTH
RP
(remove nothere from the email address if mailing direct)
SVC said:Dear Bob,
Thanks a million it worked just as i wanted. i am sure many many people will
be benifitted from this. there is a small problem
the result i get in the excell cell is
Twelve Crores Thirty Four Lakhs Rupees Fifty Six Thousand Seven Hundred
Eighty Nine and Paise Eleven Only
the word rupees appears in between it should appear at the begining or never
appear. pl mail me what changes i should make in the vb code for this
effect.
rgds
vinoth.
Bob Phillips said:Vinoth,
How about this?
Function SpellNumber(ByVal MyNumber)
Dim Crores, Lakhs, Rupees, Paise, Temp
Dim DecimalPlace, 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 = "Rupees Zero "
Case "One": Rupees = "Rupee One "
Case Else: Rupees = "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 = 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
RP
(remove nothere from the email address if mailing direct)
Dear BOB,
Hi!
i read the mail & followed the link. pl go through my prev reply
carefully
your reply in in it.
any way i will try to make it as simple as possible for you now
hopefully.
Money [or] numbers in india is split into units, tens, hundreds,
thousands,
ten thousands, lakhs, ten lakhs, crorers, ten crorers
from left to right i shall detail you how each valu is called so that you
can code easily.
After decimel point (upto 2 decimal point) it is called paise (as cents
in
US)
from left moving towards right each place is represented as follows
the eg value is Rs. 12,34,56,789.11 is called as twelve crores thirty
four
lakhs fifty six thousand seven hundered and eleven paise
now
9 = units
8 = tens
6 = thousands
5 = ten thousands
4 = lakh
3 = ten lakh
2 = crore
1 = ten crores
if you follow the above meathod my orginal value which is 12345678.11
will
represent twelve crores thirty four lakhs fifty six thousand seven
hundered
and eleven paise
in your code the same value returns
Rupees OneBillion Two Hundred Twenty ThreeMillion Four Hundred Fifty
SixThousand Seven Hundred Eighty Nine and Paise Eleven Only
I hope you understand what we want exactly.
rgds
Vinoth. S
I answered a similar question to this a few weeks back. Check out if
this
helps, but as I recall, the OP didn't answer my last question
http://tinyurl.com/4m4sp
--
HTH
RP
(remove nothere from the email address if mailing direct)
The faq is useful for the american style but i need the indian style
which
is detailed below. please help at the earliest in the group or direct
to
my
mail id which is (e-mail address removed)
Currency symbol = Rs.
Currency names = Rupees(as Dollors) & Paise(as Cents)
denomations = units, tens, hundreds, thousands, ten thousands, lakhs,
ten
lakhs, crorers, ten crorers
the digit sepration from the left to right is in 3 by 2 by 2 by 2
formation.
eg: Rs. 12,34,56,789.11 is called as twelve crores thirty four lakhs
fifty
six thousand seven hundered and eleven paise
See
http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text
--
HTH
RP
(remove nothere from the email address if mailing direct)
I want to convert the amount entered in excel sheet to their
respective
text
value.
eg: 1,24,525
to be converted as
one lakh twenty four thousand five hundered and twenty five.
Bob Phillips said:Vinoth,
A solution that does either. To include Rupees, use something like
=SpellNumber(123456789,11)
To exclude
=SpellNumber(123456789.11,False)
--
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
RP
(remove nothere from the email address if mailing direct)
Bob Phillips said:Vinoth,
Do you mean like this
Rupees Twelve Crores Thirty Four Lakhs Fifty Six Thousand Seven Hundred
Eighty Nine and Paise Eleven Only
or
Twelve Crores Thirty Four Lakhs Fifty Six Thousand Seven Hundred Eighty Nine
and Paise Eleven Only
I put Rupees there as I was told that was how it is represented in India
(but that was before I learnt about Lakhs and Crores).
Which way do you prefer?
--
HTH
RP
(remove nothere from the email address if mailing direct)
SVC said:Dear Bob,
Thanks a million it worked just as i wanted. i am sure many many people will
be benifitted from this. there is a small problem
the result i get in the excell cell is
Twelve Crores Thirty Four Lakhs Rupees Fifty Six Thousand Seven Hundred
Eighty Nine and Paise Eleven Only
the word rupees appears in between it should appear at the begining or never
appear. pl mail me what changes i should make in the vb code for this
effect.
rgds
vinoth.
Vinoth,
How about this?
Function SpellNumber(ByVal MyNumber)
Dim Crores, Lakhs, Rupees, Paise, Temp
Dim DecimalPlace, 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 = "Rupees Zero "
Case "One": Rupees = "Rupee One "
Case Else: Rupees = "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 = 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
RP
(remove nothere from the email address if mailing direct)
Dear BOB,
Hi!
i read the mail & followed the link. pl go through my prev reply
carefully
your reply in in it.
any way i will try to make it as simple as possible for you now
hopefully.
Money [or] numbers in india is split into units, tens, hundreds,
thousands,
ten thousands, lakhs, ten lakhs, crorers, ten crorers
from left to right i shall detail you how each valu is called so
that you
can code easily.
After decimel point (upto 2 decimal point) it is called paise (as cents
in
US)
from left moving towards right each place is represented as follows
the eg value is Rs. 12,34,56,789.11 is called as twelve crores thirty
four
lakhs fifty six thousand seven hundered and eleven paise
now
9 = units
8 = tens
6 = thousands
5 = ten thousands
4 = lakh
3 = ten lakh
2 = crore
1 = ten crores
if you follow the above meathod my orginal value which is
12345678.11
will
represent twelve crores thirty four lakhs fifty six thousand seven
hundered
and eleven paise
in your code the same value returns
Rupees OneBillion Two Hundred Twenty ThreeMillion Four Hundred Fifty
SixThousand Seven Hundred Eighty Nine and Paise Eleven Only
I hope you understand what we want exactly.
rgds
Vinoth. S
I answered a similar question to this a few weeks back. Check out
if
this
helps, but as I recall, the OP didn't answer my last question
http://tinyurl.com/4m4sp
--
HTH
RP
(remove nothere from the email address if mailing direct)
The faq is useful for the american style but i need the indian style
which
is detailed below. please help at the earliest in the group or direct
to
my
mail id which is (e-mail address removed)
Currency symbol = Rs.
Currency names = Rupees(as Dollors) & Paise(as Cents)
denomations = units, tens, hundreds, thousands, ten thousands, lakhs,
ten
lakhs, crorers, ten crorers
the digit sepration from the left to right is in 3 by 2 by 2 by 2
formation.
eg: Rs. 12,34,56,789.11 is called as twelve crores thirty four lakhs
fifty
six thousand seven hundered and eleven paise
See
http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text
--
HTH
RP
(remove nothere from the email address if mailing direct)
I want to convert the amount entered in excel sheet to their
respective
text
value.
eg: 1,24,525
to be converted as
one lakh twenty four thousand five hundered and twenty five.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.