Posted by: greensand | December 8, 2008

Merubah Nilai menjadi Kata (Value To Word) di Excel

Waktu pagi2 ngaskus eh ada yang tanya :

Guys mo tanya dong, gimana cara ngubah dari numerik ke teks.

contoh:

12.345 , maka terbilang
dua belas ribu tiga ratus empat puluh lima

trus misalnya kita kita ngebuat cara tersebut dalam fungsi, gimana sih cara masukkinnya??

Tengkyu atas bantuannya…

Karena baik hati, tak cariin deh jawabnnya yang nemu di sini

To Create the Sample Functions

1. Insert a module sheet into a workbook. To do this in Microsoft Excel 97 or Microsoft Excel 98, point to Macro on the Tools menu, and then click Visual Basic Editor. In the Visual Basic Editor, click Module on the Insert menu. In Microsoft Excel 5.0 or 7.0, point to Macro on the Insert menu and click Module.

2. Type the following code into the module sheet.

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

To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under “User Defined” in the Paste Function (Shift+F3).

Begitu deh, moga berguna…


Responses

  1. mbak, saya nyoba kok gak bisa ya??

  2. aku bisa sedikit dalam pascal yang inputnya angka keluarnya kata. Contoh :
    12532 menjadi ‘dua belas ribu lima ratus tiga puluh dua’
    Bila msh membutuhkan hubungi email saya xijramy@yahoo.com


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: