2 Replies Latest reply: Jul 4, 2012 5:27 AM by Tresesco B RSS

    how to convert number into words

      Hi all,

       

      I have a field as Number where the data is like 1,2,3....................... 100.

       

      I want o show this Numbers in Words like 1 = One, 2= Two........................... Hundred.

       

      Can anyone please suggest how can it be achived.

       

      Thanks & Regards,

      Pranav

        • Re: how to convert number into words
          Johannes Sunden

          I'd say you need to create a table (mapping table) with two columns:

           

          Num, Text

          1, One

          2, Two

          3, Three

          etc.

           

          Then use Applymap() to convert your number field to text.

          • Re: how to convert number into words
            Tresesco B

            Hello Pranav,

             

            The standard logic (implemented here in VB) is :

            ...............................................

            Option Explicit

            'Main Function

            Function SpellNumber(ByVal MyNumber)

                Dim Dollars, Cents, Temp

                Dim DecimalPlace, Count

                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 cents and set MyNumber to dollar amount.

                If DecimalPlace > 0 Then

                    Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _

                              "00", 2))

                    MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

                End If

                Count = 1

                Do While MyNumber <> ""

                    Temp = GetHundreds(Right(MyNumber, 3))

                    If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars

                    If Len(MyNumber) > 3 Then

                        MyNumber = Left(MyNumber, Len(MyNumber) - 3)

                    Else

                        MyNumber = ""

                    End If

                    Count = Count + 1

                Loop

                Select Case Dollars

                    Case ""

                        Dollars = "No Dollars"

                    Case "One"

                        Dollars = "One Dollar"

                     Case Else

                        Dollars = Dollars & " Dollars"

                End Select

                Select Case Cents

                    Case ""

                        Cents = " and No Cents"

                    Case "One"

                        Cents = " and One Cent"

                          Case Else

                        Cents = " and " & Cents & " Cents"

                End Select

                SpellNumber = Dollars & Cents

            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

             

             

            .............................

             

            Now you have to modify the logic accordinly...in QV function and script.

             

             

            and yes,  if you have limited numbers of numbers.. the best way is as mentioned by Sunden.

             

             

            Regards,

            tresesco