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, 2022 in Database by Edureka
• 13,670 points
20,783 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, 2022 by gaurav
• 23,260 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, 2022 in Database by gaurav
• 23,260 points
1,281 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, 2022 in Database by gaurav
• 23,260 points
7,188 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, 2022 in Database by gaurav
• 23,260 points
540 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, 2022 in Database by gaurav
• 23,260 points
773 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, 2022 in Database by Edureka
• 13,670 points
2,017 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, 2022 in Database by gaurav
• 23,260 points
30,919 views
0 votes
1 answer

Count colored cell on excel automatically

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

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
827 views
0 votes
0 answers

Count colored cell on excel automatically

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

Mar 28, 2022 in Database by Edureka
• 13,670 points
214 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, 2022 in Database by gaurav
• 23,260 points
571 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, 2022 in Database by gaurav
• 23,260 points
1,525 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