Conversion

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.

See

http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SVC" wrote in message
...
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.

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

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


"Bob Phillips" wrote in message
...
See

http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SVC" wrote in message
...
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.

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)


"SVC" wrote in message
...
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

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


"Bob Phillips" wrote in message
...
See

http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SVC" wrote in message
...
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.

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" wrote in message
...
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)


"SVC" wrote in message
...
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

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


"Bob Phillips" wrote in message
...
See

http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SVC" wrote in message
...
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.

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" wrote in message
...
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" wrote in message
...
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)


"SVC" wrote in message
...
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

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


"Bob Phillips" wrote in message
...
See

http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SVC" wrote in message
...
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.

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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)


"SVC" wrote in message
...
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

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


"Bob Phillips" wrote in message
...
See

http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SVC" wrote in message
...
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.

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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)


"SVC" wrote in message
...
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

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


"Bob Phillips" wrote in message
...
See

http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SVC" wrote in message
...
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.

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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)


"SVC" wrote in message
...
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

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


"Bob Phillips" wrote in
message
...
See

http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SVC" wrote in message
...
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.

Dear Bob

Thank you that solved all problems.

rgds
Vinoth. S

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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)


"SVC" wrote in message
...
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

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


"Bob Phillips" wrote in
message
...
See

http://www.xldynamic.com/source/xld.xlFAQ0004.html
Convert Numbers To Text

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SVC" wrote in message
...
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.