MS Excel : Nombor kepada Perkataan

 

Rujukan Lanjut : http://www.ablebits.com/office-addins-blog/2013/08/29/convert-numbers-words-excel/

KOD UNTUK RINGGIT MALAYSIA

Option Explicit

'Main Function

Function SpellNumber(ByVal MyNumber)

    Dim Ringgit, Sen, Temp

    Dim DecimalPlace, Count

    ReDim Place(9) As String

    Place(2) = " Ratus "

    Place(3) = " Juta "

    Place(4) = " Bilion "

    Place(5) = " Trilion "

    ' 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

        Sen = 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 Ringgit = Temp & Place(Count) & Ringgit

        If Len(MyNumber) > 3 Then

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

        Else

            MyNumber = ""

        End If

        Count = Count + 1

    Loop

    Select Case Ringgit

        Case ""

            Ringgit = "Tiada Ringgit"

        Case "One"

            Ringgit = "Satu Ringgit"

         Case Else

            Ringgit = Ringgit & " Ringgit"

    End Select

    Select Case Sen

        Case ""

            Sen = "   "

        Case "One"

            Sen = " dan satu sen"

              Case Else

            Sen = " dan " & Sen & " Sen"

    End Select

    SpellNumber = Ringgit & Sen

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)) & " Ratus "

    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 = "Sepuluh"

            Case 11: Result = "Sebelas"

            Case 12: Result = "Duabelas"

            Case 13: Result = "Tigabelas"

            Case 14: Result = "Empatbelas "

            Case 15: Result = "Limabelas"

            Case 16: Result = "Enambelas"

            Case 17: Result = "Tujuhbelas"

            Case 18: Result = "Lapanbelas"

            Case 19: Result = "Sembilanbelas"

            Case Else

        End Select

    Else                                 ' If value between 20-99...

        Select Case Val(Left(TensText, 1))

            Case 2: Result = "Duapuluh "

            Case 3: Result = "Tigapuluh "

            Case 4: Result = "Empatpuluh "

            Case 5: Result = "Limapuluh "

            Case 6: Result = "Enampuluh "

            Case 7: Result = "Tujuhpuluh "

            Case 8: Result = "Lapanpuluh "

            Case 9: Result = "Sembilanpuluh "

            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 = "Satu"

        Case 2: GetDigit = "Dua"

        Case 3: GetDigit = "Tiga"

        Case 4: GetDigit = "Empat"

        Case 5: GetDigit = "Lima"

        Case 6: GetDigit = "Enam"

        Case 7: GetDigit = "Tujuh"

        Case 8: GetDigit = "Lapan"

        Case 9: GetDigit = "Sembilan"

        Case Else: GetDigit = ""

    End Select

End Function

HASILNYA