OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

converting number to word
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
bordia
General User
General User


Joined: 24 Jul 2006
Posts: 19
Location: Ratlam MP India

PostPosted: Sun Jul 30, 2006 9:10 pm    Post subject: converting number to word Reply with quote

Cna any one help me for macro for converting number to text.

Like - Rs.12584729.25

to Ruppes One Cror Twenty Five Lacs Eighty Four Thousand Seven Hundred Twenty Nine and Paisa Twenty Five Only.

If you can provide the macro for this it will be of great help.

THanks
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Mon Jul 31, 2006 7:05 am    Post subject: Reply with quote

DannyB has written such a macro in this form. I have not the thread on hand Embarassed
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide
Back to top
View user's profile Send private message Visit poster's website
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Mon Jul 31, 2006 11:22 am    Post subject: Reply with quote

I demonstrate how to do this if you want to convert in English in my free macro document. Search for the text "billion" to find the code.
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
hol.sten
Super User
Super User


Joined: 14 Nov 2004
Posts: 3533
Location: Hamburg, Germany

PostPosted: Mon Jul 31, 2006 11:31 am    Post subject: Reply with quote

SergeM wrote:
DannyB has written such a macro in this form. I have not the thread on hand Embarassed

bordia already posted in that thread: http://www.oooforum.org/forum/viewtopic.phtml?t=5923. I think he only needs a translation.

With kind regards
hol.sten
Back to top
View user's profile Send private message
bordia
General User
General User


Joined: 24 Jul 2006
Posts: 19
Location: Ratlam MP India

PostPosted: Mon Jul 31, 2006 6:31 pm    Post subject: number to word Reply with quote

Sir,

Thanks for your reply. I tried to search on links you provided, but could not find it. Can you please inform me how to search on that page.

SergeM wrote:
DannyB has written such a macro in this form. I have not the thread on hand Embarassed
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
bordia
General User
General User


Joined: 24 Jul 2006
Posts: 19
Location: Ratlam MP India

PostPosted: Mon Jul 31, 2006 6:33 pm    Post subject: numebr to word Reply with quote

Thanks for your reply.

I do not have any programming skills, so can you proviode me conversion.

Thanks

pitonyak wrote:
I demonstrate how to do this if you want to convert in English in my
free macro document. Search for the text "billion" to find the code.
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
hol.sten
Super User
Super User


Joined: 14 Nov 2004
Posts: 3533
Location: Hamburg, Germany

PostPosted: Tue Aug 01, 2006 10:29 am    Post subject: Re: number to word Reply with quote

bordia wrote:
SergeM wrote:
DannyB has written such a macro in this form. I have not the thread on hand Embarassed
I tried to search on links you provided, but could not find it.

You already found the link and posted a similar question as I wrote in this thread.

And the problem with the people having programming skills is their lack of the needed language skill. I too have not the slightest idea how to translate all the numbers in the Basic script. So I guess as long as you don't provide the translations no one here can help you.

With kind regards
hol.sten
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Aug 01, 2006 1:32 pm    Post subject: Reply with quote

I've never visited India, but this one may be right:
Copy Danny's function NumToWords together with this one into some module in library "Standard" and test in a spreadsheet =indianCurrencyToWords(A1)
Code:

Function indianCurrencyToWords(dblNum#) as String
'3.26.51424.48
'Rupees Three crores twenty six lacs fifty one thousand four hunderd twenty four and paisa forty eight only.
   'we operate on a string, representing a 2-digits-rounded number
   sNum = format(dblNum,"#.00")
   if dblNum < 0 then
      sNegative = "negative "
      sNum = mid(sNum,2) 'cut off minus char
   else
      sNegative = ""
   endif
   'do we have paisa at left two digits?
   if NOT (right(sNum,2) = "00") then
      lNum = cDbl(Right(sNum,2)
      sRight = "paisa "& NumToWords(lNum) &" only"
   endif
   sNum = left(sNum,len(sNum)-3) 'cut off decimals and point
   If len(sNum) > 0 then
      iDigits = len(sNum)
      if iDigits > 5 then iDigits = 5
      lNum = cLng(right(sNum,iDigits))
      if lNum > 0 then
         if len(sRight)> 0  then sRight = " and "& sRight
         sRight = "lacs "& NumToWords(lNum) & sRight
      endif
      sNum = left(sNum,len(sNum)-iDigits) 'cut off lacs
   endif
   If len(sNum) > 0 then
      iDigits = len(sNum)
      if iDigits > 2 then iDigits = 2
      lNum = cLng(right(sNum,iDigits))
      If lNum > 0 then sRight = "crores "& NumToWords(lNum) &" "& sRight
      sNum = left(sNum,len(sNum)-iDigits) 'cut off crores
   endif
   If len(sNum) > 0 then
      lNum = cLng(sNum)
      if lNum > 0 then sRight = "rupies "& NumToWords(lNum) &" "& sRight
   endif
   'convert to lower case
   indianCurrencyToWords = LCase(sNegative & sRight)
End Function

EDIT (2 hours later): I had to edit the function, because it got an overflow with large numbers.
some results:
Code:

0   
1   lacs one
1,2345   lacs one and paisa twenty three only
123   lacs one hundred twenty three
23456,12987   lacs twenty three thousand four hundred fifty six and paisa thirteen only
9800023   crores ninty eight lacs twenty three
100000,01   crores one paisa one only
10023000   rupies one lacs twenty three thousand
12345678   rupies one crores twenty three lacs forty five thousand six hundred seventy eight
10000000,129   rupies one paisa thirteen only

_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Wed Aug 02, 2006 3:59 am    Post subject: Reply with quote

Villeroy,

What the original post failed to mention was that he had a complete solution in VBA that did not work. He sent it to me July 31. It took me a few minutes to fix it so that it ran. I asked him to post the solution here so that it would be understood that he finally had a working solution. I see that he did not. Here is what he had (just so that you know).
Code:
REM  *****  BASIC  *****
Option Explicit

Sub Main
  Print getAmountInWords("999999999.99")
End Sub


'********************************************************************************************************
'Function Name      :   getAmountInWords
'Description        :   To convert the Amount value into words(formatted as "000000000.00")
'                       (Maximum allowed limit 999999999.99)
'Input Parameters   :   Amount
'Returns            :   String
'Specific Logic used:   None
'********************************************************************************************************
Public Function getAmountInWords(strAmount As String) As String
    Dim strIntPart          As String
    Dim strDecPart          As String
    Dim strCroresPart       As String
    Dim strLakhsPart        As String
    Dim strThousandsPart    As String
    Dim strHundredsPart     As String
    Dim strTensPart         As String
    Dim strOnesPart         As String
    Dim strDecTensPart      As String
    Dim strDecOnesPart      As String
    Dim strAmtWords         As String
    Dim dblIntPart          As Double
    Dim intDecPart          As Integer
    Dim strDecWords         As String
    Dim iErr As Integer

    'handle for typemismatch error
    '??Err.Clear
    '??On Error Resume Next
    On Error Goto BadError
    strAmount = CDbl(strAmount)
    '??If Err.Number = "13" Then
    '??    getAmountInWords = ""
    '??    Exit Function
    '??End If
    '??Err.Clear
    strAmount = Format(Trim(strAmount), "000000000.00")

    'if the value is negative,zero above the limit then give error message
    If Val(strAmount) < 0 Then
        getAmountInWords = ""
        Exit Function
    ElseIf Val(strAmount) = 0 Then
        getAmountInWords = "Rupees Zero"
        Exit Function
    ElseIf Val(strAmount) > 999999999.99 Then
        getAmountInWords = ""
        Exit Function
    End If

    'store the integer and decimal parts separately
    strIntPart = Mid(strAmount, 1, 9)
    strDecPart = Mid(strAmount, 11, 2)

    'store the individual places in variables
    strCroresPart = Mid(strIntPart, 1, 2)
    strLakhsPart = Mid(strIntPart, 3, 2)
    strThousandsPart = Mid(strIntPart, 5, 2)
    strHundredsPart = Mid(strIntPart, 7, 1)
    strTensPart = Mid(strIntPart, 8, 1)
    strOnesPart = Mid(strIntPart, 9, 1)
    strDecTensPart = Mid(strDecPart, 1, 1)
    strDecOnesPart = Mid(strDecPart, 2, 1)

    strAmtWords = ""
    'To make the Crores Part
    If Val(strCroresPart) <> 0 Then
        strAmtWords = strAmtWords & getCroresPart(strCroresPart)
    End If

    'To make the Lakhs Part
    If Val(strLakhsPart) <> 0 Then
        strAmtWords = strAmtWords & getLakhsPart(strLakhsPart)
    End If

    'To make the Thousands Part
    If Val(strThousandsPart) <> 0 Then
        strAmtWords = strAmtWords & getThousandsPart(strThousandsPart)
    End If

    'To make the hundreds Part
    If Val(strHundredsPart) <> 0 Then
        strAmtWords = strAmtWords & getOnesColumn(Format(strHundredsPart, "00")) & " Hundred "
    End If

    'To make Tens and Ones part
    If Val(strTensPart & strOnesPart) <> 0 And Val(strAmount) > 100 Then
        strAmtWords = strAmtWords & "and " & getTensOnesPart(strAmount, strTensPart, strOnesPart)
    Else
        strAmtWords = strAmtWords & getTensOnesPart(strAmount, strTensPart, strOnesPart)
    End If

    strDecWords = ""
    'To make Tens and Ones part in the decimal part
    If Val(strDecTensPart & strDecOnesPart) <> 0 Then
        strDecWords = strDecWords & getTensOnesPart(Val(strAmount), strDecTensPart, strDecOnesPart)
    End If

    'If both integer and decimal part are not Zero then add Rupees and Paise only
    If Val(strDecPart) <> 0 And Val(strIntPart) <> 0 Then
        getAmountInWords = "Rupees " & strAmtWords & " and " & strDecWords & " Paise only"
    'If deciaml part is zero then add Rupees and only
    ElseIf Val(strIntPart) <> 0 And Val(strDecPart) = 0 Then
        getAmountInWords = "Rupees " & strAmtWords & " Only"
    'If Integer part is zero then add Paise only
    ElseIf Val(strIntPart) = 0 And Val(strDecPart) <> 0 Then
        getAmountInWords = strDecWords & " Paise Only"
    End If
    '??Replace is not supported in OOo Basic
    '??getAmountInWords = Trim(Replace(getAmountInWords, "  ", " "))
    getAmountInWords = Trim(getAmountInWords, "  ", " ")
    Exit Function
BadError:
   iErr = Err
   If Err = 13 Then
     getAmountInWords = ""
     Exit Function
   Else
     REM OK, do what?
     getAmountInWords = ""
     Print "Did not expect to get here with err " & iErr
   End If
End Function

'********************************************************************************************************
'Function Name      :   getOnesColumn
'Description        :   To convert the number in ones column into words
'Input Parameters   :   String(ones value formated as "00")
'Returns            :   String
'Specific Logic used:   None
'********************************************************************************************************
Public Function getOnesColumn(strValue As String) As String
    Select Case strValue
        Case "01"
            getOnesColumn = "One"
        Case "02"
            getOnesColumn = "Two"
        Case "03"
            getOnesColumn = "Three"
        Case "04"
            getOnesColumn = "Four"
        Case "05"
            getOnesColumn = "Five"
        Case "06"
            getOnesColumn = "Six"
        Case "07"
            getOnesColumn = "Seven"
        Case "08"
            getOnesColumn = "Eight"
        Case "09"
            getOnesColumn = "Nine"
    End Select
End Function

'********************************************************************************************************
'Function Name      :   getTensColumnWithOne
'Description        :   To convert the number in tens and ones column into words
'                       if the combined(tens+ones) value is between 10 and 19
'Input Parameters   :   String
'Returns            :   String
'Specific Logic used:   None
'********************************************************************************************************
Public Function getTensColumnWithOne(strValue As String) As String
    Select Case strValue
        Case "10"
            getTensColumnWithOne = "Ten"
        Case "11"
            getTensColumnWithOne = "Eleven"
        Case "12"
            getTensColumnWithOne = "Twelve"
        Case "13"
            getTensColumnWithOne = "Thirteen"
        Case "14"
            getTensColumnWithOne = "Fourteen"
        Case "15"
            getTensColumnWithOne = "Fifteen"
        Case "16"
            getTensColumnWithOne = "Sixteen"
        Case "17"
            getTensColumnWithOne = "Seventeen"
        Case "18"
            getTensColumnWithOne = "Eighteen"
        Case "19"
            getTensColumnWithOne = "Nineteen"
    End Select
End Function

'********************************************************************************************************
'Function Name      :   getTensColumn
'Description        :   To convert the number in tens column into words
'                       if the combined(tens+ones) value is between 20 and 99
'Input Parameters   :   String
'Returns            :   String
'Specific Logic used:   None
'********************************************************************************************************
Public Function getTensColumn(strValue As String) As String
    Select Case strValue
        Case "2"
            getTensColumn = "Twenty"
        Case "3"
            getTensColumn = "Thirty"
        Case "4"
            getTensColumn = "Fourty"
        Case "5"
            getTensColumn = "Fifty"
        Case "6"
            getTensColumn = "Sixty"
        Case "7"
            getTensColumn = "Seventy"
        Case "8"
            getTensColumn = "Eighty"
        Case "9"
            getTensColumn = "Ninety"
    End Select
End Function

'********************************************************************************************************
'Function Name      :   getCroresPart
'Description        :   To convert the crore part into words
'Input Parameters   :   String(crore value)
'Returns            :   String
'Specific Logic used:   None
'********************************************************************************************************
Public Function getCroresPart(strCrore As String) As String
    If Val(strCrore) < 10 Then
        If Val(strCrore) = 1 Then
            getCroresPart = getOnesColumn(strCrore) & " Crore "
        Else
            getCroresPart = getOnesColumn(strCrore) & " Crores "
        End If
    ElseIf Val(strCrore) < 20 Then
        getCroresPart = getCroresPart & getTensColumnWithOne(strCrore) & " Crores "
    Else
        getCroresPart = getCroresPart & getTensColumn(Mid(strCrore, 1, 1))
        If Mid(strCrore, 2, 1) <> "0" Then
            getCroresPart = getCroresPart & " " & _
                            getOnesColumn(Format(Mid(strCrore, 2, 1), "00"))
        End If
        getCroresPart = getCroresPart & " Crores "
    End If
End Function

'********************************************************************************************************
'Function Name      :   getLakhsPart
'Description        :   To convert the lakh part into words
'Input Parameters   :   String(Lakh value)
'Returns            :   String
'Specific Logic used:   None
'********************************************************************************************************
Public Function getLakhsPart(strLakh As String) As String
    If Val(strLakh) < 10 Then
        If Val(strLakh) = 1 Then
            getLakhsPart = getLakhsPart & getOnesColumn(strLakh) & " Lakh "
        Else
            getLakhsPart = getLakhsPart & getOnesColumn(strLakh) & " Lakhs "
        End If
    ElseIf Val(strLakh) < 20 Then
        getLakhsPart = getLakhsPart & getTensColumnWithOne(strLakh) & " Lakhs "
    Else
        getLakhsPart = getLakhsPart & getTensColumn(Mid(strLakh, 1, 1))
        If Mid(strLakh, 2, 1) <> "0" Then
            getLakhsPart = getLakhsPart & " " & _
                            getOnesColumn(Format(Mid(strLakh, 2, 1), "00"))
        End If
        getLakhsPart = getLakhsPart & " Lakhs "
    End If
End Function

'********************************************************************************************************
'Function Name      :   getThousandsPart
'Description        :   To convert the thousand part into words
'Input Parameters   :   String(thousand value)
'Returns            :   String
'Specific Logic used:   None
'********************************************************************************************************
Public Function getThousandsPart(strThousand As String) As String
    If Val(strThousand) < 10 Then
        getThousandsPart = getThousandsPart & getOnesColumn(strThousand) & " Thousand "
    ElseIf Val(strThousand) < 20 Then
        getThousandsPart = getThousandsPart & getTensColumnWithOne(strThousand) & " Thousand "
    Else
        getThousandsPart = getThousandsPart & getTensColumn(Mid(strThousand, 1, 1))
        If Mid(strThousand, 2, 1) <> "0" Then
            getThousandsPart = getThousandsPart & " " & _
                            getOnesColumn(Format(Mid(strThousand, 2, 1), "00"))
        End If
        getThousandsPart = getThousandsPart & " Thousand "
    End If
End Function

'********************************************************************************************************
'Function Name      :   getTensOnesPart
'Description        :   To convert the tens and ones part into words
'Input Parameters   :   actual total amount,tens value,ones value
'Returns            :   String
'Specific Logic used:   None
'********************************************************************************************************
Public Function getTensOnesPart(strAmount As String, strTensPart As String, strOnesPart As String) As String
    If Val(strTensPart & strOnesPart) < 10 Then
        getTensOnesPart = getTensOnesPart & getOnesColumn(strTensPart & strOnesPart)
    ElseIf Val(strTensPart & strOnesPart) < 20 Then
        getTensOnesPart = getTensOnesPart & getTensColumnWithOne(strTensPart & strOnesPart)
    Else
        getTensOnesPart = getTensOnesPart & getTensColumn(strTensPart)
        If Mid(strOnesPart, 2, 1) <> "0" Then
            getTensOnesPart = getTensOnesPart & " " & getOnesColumn(Format(strOnesPart, "00"))
        End If
    End If
End Function


_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Aug 02, 2006 4:11 pm    Post subject: Reply with quote

Laughing Intersting! At least I learned how to count money in India.
My function does not spell correctly. Should have consulted wikipedia first.
Your function has a limit of 10^9 Rupies (~17 million Euros) and the limit is 10^7 with region-settings having another decimal separator than dot.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Wed Aug 02, 2006 6:49 pm    Post subject: Reply with quote

I only translated what was sent to me.

I wrote a version that translates numbers into English that can handle a "string" number up to about 10^303, now that is a lot of numbers!
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
bordia
General User
General User


Joined: 24 Jul 2006
Posts: 19
Location: Ratlam MP India

PostPosted: Mon Aug 07, 2006 2:14 am    Post subject: numebr to word Reply with quote

Sir,
Firstly thankyou very much for helping and giving me your valuable time.

I did not show your reply till 07.08.2006. Now when I saw this, I tried to copy this macro and inserted as basic module as :

Tools-Macros-Organise Macros-OpenOffice.org Basic-My Macros-Standard-Module1
and pasted complete macro here.

Now on on clicking on Run buttion it is giving BASIC Syntex error. Syntax error.

Can you please inform me why this is coming. Process I followed to run macro is correct or not ?

Thanks
Rajesh Bordia
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Aug 07, 2006 3:21 am    Post subject: Reply with quote

You did not tell us the line where the error occures.
This is the default content of a new module that should be removed before inserting Andrew's macro:
Code:

REM  *****  BASIC  *****

Sub Main

End Sub

Sometimes pasting code from a browser causes sytax errors when a long line has been split in two.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Mon Aug 07, 2006 11:04 am    Post subject: Reply with quote

I thought that I sent a working copy as a document attachement by mail... Did that not work for you?
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
bordia
General User
General User


Joined: 24 Jul 2006
Posts: 19
Location: Ratlam MP India

PostPosted: Mon Aug 07, 2006 8:13 pm    Post subject: numebr to word Reply with quote

Yes, I have removed these default lines and then copy-paste the module provided here.
On clicking Compile or run button it gives error Basic Suntex Error. Syntax error :

at line

Print getAmountInWords("999999999.99")

If I remove one space from beginign of this line and then it gives same error at all lines of

DIM strIntPart As String

If Again I remove one space at begining on all DIM lines then it give error at

Public Function getAmountInWords(strAmount As String) As String

It Highlights etAm from getAmountInWords.

I do not have any programming knowledge, so it is not possible for me to understand why this erroars are coming.
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group