VBA Express : Access - Convert a number to text. Originally developed by Legare Coleman.

Access

Convert a number to text. Originally developed by Legare Coleman.

Ease of Use

Easy

Version tested with

97, 2002

Submitted by:

Kieran

Description:

Will convert a number to it's text equivalent

Discussion:

It works for up to Sextllions. It will also give you something like 'Three Hundred Thirty Dollars and Sixty Seven Cents if you pass it the optional second and third parameters. like this: =NumberToText(330.67,"Dollars","Cents") (c) Legare Coleman

Code:

instructions for use

			Public Function NumberToText(Num As Variant, Optional vCurName As Variant, Optional vCent As Variant) As Variant 
    Dim TMBT As Variant 
    Dim sNum As String, sDec As String, sHun As String, IC As Integer 
    Dim Result As String, sCurName As String, sCent As String 
     
    If Application.IsNumber(Num) = False Then 
        NumberToText = CVErr(xlValue) 
        Exit Function 
    End If 
     
    If IsMissing(vCurName) Then 
        sCurName = "" 
    Else 
        sCurName = Trim(CStr(vCurName)) 
    End If 
    If IsMissing(vCent) Then 
        sCent = "" 
    Else 
        sCent = Trim(CStr(vCent)) 
    End If 
     
    TMBT = Array("", "Thousand", "Million", "Billion", "Trillion", "Quadrillion", "Quintillion", "Sextillion") 
     
    If IsMissing(sCent) Or IsNull(sCent) Then 
        sNum = Format(Application.Round(Num, 0), "0") 
    Else 
        sNum = Format(Application.Round(Num, 2), "0.00") 
        sDec = Right(sNum, 2) 
        sNum = Left(sNum, Len(sNum) - 3) 
        If CInt(sDec) <> 0 Then 
            sDec = "and " & Trim(HundredsToText(CVar(sDec)) & " " & sCent) 
        Else 
            sDec = "" 
        End If 
    End If 
     
    IC = 0 
    While Len(sNum) > 0 
        sHun = Right(sNum, 3) 
        sNum = Left(sNum, Application.Max(Len(sNum) - 3, 0)) 
        If CInt(sHun) <> 0 Then 
            Result = Trim(Trim(HundredsToText(CVar(sHun)) & " " & TMBT(IC)) & " " & Result) 
        End If 
        IC = IC + 1 
    Wend 
    Result = Trim(Result & " " & sCurName) 
    Result = Trim(Result & " " & sDec) 
     
    NumberToText = Result 
     
End Function 
 
Private Function HundredsToText(Num As Integer) As String 
    Dim Units As Variant, Teens As Variant, Tens As Variant 
    Dim i As Integer, IUnit As Integer, ITen As Integer, IHundred As Integer 
    Dim Result As String 
     
    Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine") 
    Teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen") 
    Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety") 
     
    Result = "" 
    IUnit = Num Mod 10 
    i = Int(Num / 10) 
    ITen = i Mod 10 
    IHundred = Int(i / 10) 
    If IHundred > 0 Then 
        Result = Units(IHundred) & " Hundred" 
    End If 
    If ITen = 1 Then 
        Result = Result & " " & Teens(IUnit) 
    Else 
        If ITen > 1 Then 
            Result = Trim(Result & " " & Tens(ITen) & " " & Units(IUnit)) 
        Else 
            Result = Trim(Result & " " & Units(IUnit)) 
        End If 
    End If 
     
    HundredsToText = Result 
     
End Function

How to use:

Add the code to the personal workbook to make available globally to all worksheets or to the affected workbook if you want to distribute the workbook and ensure that all users will ahve access.
The code is inserted in a general module
Use as a worksheet function like
=NumberToText(330.67,"Dollars","Cents") or
=NumberToText(A2,"Dollars","Cents") where A2 holds a number value

Test the code:

enter =NumberToText(330.67,"Dollars","Cents") in any cell.
the text shoud be displayed as
Three Hundred Thirty Dollars and Sixty Seven Cents

Sample File:

num2Text.zip 11.1KB

Approved by mdmackillop

This entry has been viewed 169 times.