Closed Thread
Results 1 to 9 of 9

Convert Number to Words (Indian Format)

  1. #1
    Tilak Minocha
    Guest

    Convert Number to Words (Indian Format)


    How to convert numbers into words in Microsoft excel
    for example to convert 1,23,123.56 into "Rupees one lac twenty three
    thousand one hundred twenty three and paise fifty six only"


    --
    Tilak Minocha
    ------------------------------------------------------------------------
    Tilak Minocha's Profile: http://www.officehelp.in/member.php?userid=43
    View this thread: http://www.officehelp.in/showthread.php?t=646497
    Visit - http://www.officehelp.in | http://www.officehelp.in/index/index.php/


  2. #2
    Bob Phillips
    Guest

    Re: Convert Number to Words (Indian Format)


    Here is a function I gave some time ago that caters for Rupees, Lakhs,
    Crore and Paise. To call it, enter something like


    =SpellNumber(115) in a cell


    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 Phillips

    "Tilak Minocha" <[email protected]> wrote in message
    news:[email protected]...
    >
    > How to convert numbers into words in Microsoft excel
    > for example to convert 1,23,123.56 into "Rupees one lac twenty three
    > thousand one hundred twenty three and paise fifty six only"
    >
    >
    > --
    > Tilak Minocha
    > ------------------------------------------------------------------------
    > Tilak Minocha's Profile: http://www.officehelp.in/member.php?userid=43
    > View this thread: http://www.officehelp.in/showthread.php?t=646497
    > Visit - http://www.officehelp.in |

    http://www.officehelp.in/index/index.php/
    >




  3. #3
    Tilak Minocha
    Guest

    Re: Convert Number to Words (Indian Format)


    Thanks for the coding.
    its working very fine
    regards
    tilak minocha


    --
    Tilak Minocha
    ------------------------------------------------------------------------
    Tilak Minocha's Profile: http://www.officehelp.in/member.php?userid=43
    View this thread: http://www.officehelp.in/showthread.php?t=646497
    Visit - http://www.officehelp.in | http://www.officehelp.in/index/index.php/


  4. #4
    Bob Phillips
    Guest

    Re: Convert Number to Words (Indian Format)

    Glad you liked it.

    --
    HTH

    Bob Phillips

    "Tilak Minocha" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Thanks for the coding.
    > its working very fine
    > regards
    > tilak minocha
    >
    >
    > --
    > Tilak Minocha
    > ------------------------------------------------------------------------
    > Tilak Minocha's Profile: http://www.officehelp.in/member.php?userid=43
    > View this thread: http://www.officehelp.in/showthread.php?t=646497
    > Visit - http://www.officehelp.in |

    http://www.officehelp.in/index/index.php/
    >




  5. #5
    Registered User
    Join Date
    09-10-2011
    Location
    TRICHY INDIA
    MS-Off Ver
    Excel 2007
    Posts
    57

    Smile Re: Convert Number to Words (Indian Format)

    Hi Dear
    Thank you very much for quoting the Macro.Its Working
    Nice very kind of you. I am New to this forum. Excuse for the belated reply
    Regards
    M Nadarajan.

  6. #6
    Registered User
    Join Date
    05-11-2007
    Location
    Bangalore, India
    MS-Off Ver
    MS Office 2013
    Posts
    58

    Re: Convert Number to Words (Indian Format)

    Hi Bob,

    Thanks a lot for the code...

    Regards,
    Harish S

  7. #7
    Registered User
    Join Date
    02-16-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    3

    Exclamation Re: Convert Number to Words (Indian Format)

    [QUOTE=Bob Phillips;980072]Here is a function I gave some time ago that caters for Rupees, Lakhs,
    Crore and Paise.

    The program is excellent but when it comes to more than 99 crores it gives an error message. How to change the code in such a way that it will give 500 crores and 5000 crores etc?

  8. #8
    Registered User
    Join Date
    04-02-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Convert Number to Words (Indian Format)

    Superb...thanks for sharing...venu(mkv)

  9. #9
    Registered User
    Join Date
    07-04-2012
    Location
    Chennai, India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Convert Number to Words (Indian Format)

    very useful, but i don't want paisa.

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Convert Number to Words (Indian Format)

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1