Convert numbers to words in Excel VBA

0 votes

I found a code converting number to words. But how can I edit this code to remain the "cents" a number? I only wanted to trim the digits after the decimal point but not to convert it.

instead of this format: 45.50 - Forty-Five Dollars and Fifty cents it should be like this: 45.50 - Forty-Five Dollars and 25/100.

The first format is the result of this code but I want it differently. I want to make it like in the second format.


'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'

Mar 24 in Database by Edureka
• 13,640 points
1,713 views

1 answer to this question.

0 votes

In the cell where you wish to display a written number, type the formula =SpellNumber(A1), where A1 is the cell holding the number you want to convert. You can alternatively type the value explicitly, such as =SpellNumber (22.50).

To confirm the formula, press Enter.

Save the workbook with the SpellNumber function.
A workbook having macro functions can't be saved in the regular macro-free workbook format in Excel (.xlsx). If you choose File > Save, you will be taken to a page where you can save your work The dialogue box for VB projects appears. No, please.

In the VB project dialog box, click No.

To maintain your file in its current format, you can save it as an Excel Macro-Enabled Workbook (.xlsm).

Save As from the File menu.

Select Excel Macro-Enabled Workbook from the drop-down option under Save as type.

Save your work.

answered Mar 25 by gaurav
• 22,040 points

Related Questions In Database

0 votes
1 answer

how to Convert EST time to IST time in excel?

Use the following calculation since Indian Standard ...READ MORE

answered Mar 25 in Database by gaurav
• 22,040 points
344 views
0 votes
1 answer

how to Convert EST time to IST time in excel?

Use the following calculation since Indian Standard ...READ MORE

answered Mar 30 in Database by gaurav
• 22,040 points
1,644 views
0 votes
1 answer

Excel formula to remove space between words in a cell

There are three fast techniques to get ...READ MORE

answered Mar 30 in Database by gaurav
• 22,040 points
120 views
0 votes
1 answer

How to Convert Excel Numeric Cell Value into Words

To translate numbers to words, create the ...READ MORE

answered Mar 31 in Database by gaurav
• 22,040 points
176 views
0 votes
0 answers

How to loop in excel without VBA or macros?

Is it possible to iterate (loop) a ...READ MORE

Mar 30 in Database by Edureka
• 13,640 points
816 views
0 votes
1 answer

Convert Date to Text without losing the format in Excel?

The steps are as follows: Copy the dates ...READ MORE

answered Apr 1 in Database by gaurav
• 22,040 points
6,446 views
0 votes
1 answer

Count colored cell on excel automatically

Use Filter and SUBTOTAL to Count Colored ...READ MORE

answered Mar 30 in Database by gaurav
• 22,040 points
164 views
0 votes
0 answers

Count colored cell on excel automatically

I'm trying to count the number of ...READ MORE

Mar 28 in Database by Edureka
• 13,640 points
45 views
0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23 in Database by gaurav
• 22,040 points
140 views
0 votes
1 answer

[Excel][VBA] How to draw a line in a graph?

Sub MakeChart() Dim x(20) ...READ MORE

answered Mar 24 in Database by gaurav
• 22,040 points
383 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP