Uncategorized

Number to word convert SpellNumber

Open Excel file. Click ALT+F11. Than Insert Module and paste the code. And save it. (=SpellNumber(Cell) Enter


Function SpellNumber(ByVal n As Double, _

                     Optional ByVal useword As Boolean = True, _

                     Optional ByVal ccy As String = “”, _

                     Optional ByVal cents As String = “”, _

                     Optional ByVal join As String = ” And”, _

                     Optional ByVal fraction As Boolean = False) As String

Dim myLength As Long

Dim i As Long

Dim myNum As Long

Dim Remainder As Long


    SpellNumber = “”

    Remainder = Round(100 * (n – Int(n)), 0)

    myLength = Int(Application.Log10(n) / 3)


    For i = myLength To 0 Step -1

        myNum = Int(n / 10 ^ (i * 3))

        n = n – myNum * 10 ^ (i * 3)

        If myNum > 0 Then

            SpellNumber = SpellNumber & MakeWord(Int(myNum)) & _

            Choose(i + 1, “”, ” thousand “, ” million “, ” billion “, ” trillion”)

        End If

    Next i

    SpellNumber = SpellNumber & IIf(useword, ” ” & ccy, “”) & _

                    IIf(Remainder > 0, join & ” ” & Format(Remainder, “00”), ” Only”) & _

                    IIf(fraction, “/100”, “”) & ” ” & cents

    SpellNumber = Application.Proper(Trim(SpellNumber))

End Function


Function MakeWord(ByVal inValue As Long) As String

Dim unitWord, tenWord

Dim n As Long

Dim unit As Long, ten As Long, hund As Long

    unitWord = Array(“”, “one”, “two”, “three”, “four”, _

                     “five”, “six”, “seven”, “eight”, _

                     “nine”, “ten”, “eleven”, “twelve”, _

                     “thirteen”, “fourteen”, “fifteen”, _

                     “sixteen”, “seventeen”, “eighteen”, “nineteen”)

    tenWord = Array(“”, “ten”, “twenty”, “thirty”, “forty”, _

                    “fifty”, “sixty”, “seventy”, “eighty”, “ninety”)

    MakeWord = “”

    n = inValue

    If n = 0 Then MakeWord = “zero”

    hund = n \ 100

    If hund > 0 Then MakeWord = MakeWord & MakeWord(Int(hund)) & ” hundred “

    n = n – hund * 100

    If n < 20 Then

        ten = n

        MakeWord = MakeWord & unitWord(ten) & ” “

    Else

        ten = n \ 10

        MakeWord = MakeWord & tenWord(ten) & ” “

        unit = n – ten * 10

        MakeWord = Trim(MakeWord & unitWord(unit))

    End If

    MakeWord = Application.Proper(Trim(MakeWord))

End Function

Leave a Reply