Tag: tips

The “spell number” function is a tool used by accountants and finance professionals to convert numerical values into their written word form. This function is typically used to generate the written representation of a specific amount or value, such as converting “1234.56” to “one thousand two hundred thirty-four and 56/100 dollars.”

The spell number function can be beneficial in various accounting and financial scenarios, including:

1. Writing checks: When preparing checks, it is customary to write out the amount in words to minimize the risk of alteration or fraud. The spell number function allows accountants to automatically generate the written form of the check amount.
2. Financial reports: In financial reports, especially those prepared for presentation or legal purposes, it can be useful to provide both the numerical value and the written form of amounts. The spell number function assists in generating the written representation of numbers, enhancing the clarity and comprehensibility of financial statements.
3. Invoicing and billing: Some organizations or industries may require invoices or billing statements to include the written form of the invoiced amounts. The spell number function helps automate this process, ensuring accuracy and consistency in the presentation of financial information.
4. Compliance and regulatory requirements: Certain regulations or jurisdictions might mandate the inclusion of written amounts in specific financial documents. The spell number function can facilitate compliance with these requirements, reducing the risk of non-compliance or errors.
5. Presentations and proposals: When delivering financial presentations or proposals, using the written form of amounts can enhance professionalism and improve the audience’s understanding. The spell number function allows accountants to quickly convert numerical data into a more easily digestible format.

It’s worth noting that the specific implementation and availability of a spell number function may vary depending on the accounting software, spreadsheet application, or programming language being used.

To update the code in your Excel workbook, follow these steps:

Open your Excel workbook.

Press Alt + F11 to open the VBA Editor.

In the VBA Editor, find the module where the code is currently
located. It may have the same name as the worksheet containing the
cell you want to use the spell-in-numbers function.

Double-click on the module to open it.

Replace the existing code with the updated code you received earlier.

Save the workbook by pressing Ctrl + S or by clicking on the save button.

Close the VBA Editor by clicking the close button (X) or by pressing Alt + Q.

Now you can use the SpellNumber function in your Excel worksheet.

In a cell where you want to display the spell-in-numbers conversion,
enter the following formula: =SpellNumber(A1) (assuming the number you
want to convert is in cell A1).

Press Enter to see the converted number in words.

Make sure to replace A1 with the cell reference that contains the
number you want to convert.

That’s it! The code is now updated in your Excel workbook, and you can
use the SpellNumber function to convert numbers to words.

Below codes (USD & Dirham) for your use(enjoy the benefits)

USD Codes:

Option Explicit

'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

Dirham Code:

Option Explicit

'Main Function
Function SpellNumber(ByVal MyNumber)
    Dim Dirhams, Fils, 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 fils and set MyNumber to dirham amount.
    If DecimalPlace > 0 Then
        Fils = 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 Dirhams = Temp & Place(Count) & Dirhams
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop

    Select Case Dirhams
        Case ""
            Dirhams = "No Dirhams"
        Case "One"
            Dirhams = "One Dirham"
        Case Else
            Dirhams = Dirhams & " Dirhams"
    End Select

    Select Case Fils
        Case ""
            Fils = " and No Fils"
        Case "One"
            Fils = " and One Fil"
        Case Else
            Fils = " and " & Fils & " Fils"
    End Select

    SpellNumber = Dirhams & Fils
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

Hope this will help you.

Thanks
Rohitashva Singhvi

• 
  Generally Accepted Accounting Principles
• 
  VAT(Identification of Designated Zones) – UAE
• 
  SFAS 78 With Summary
• 
  Transfer Pricing – Detail Overview
• 
  Shop
• 
  What is Accounting & how it is classified?

20 best websites for rich source of information and learning (generalfactsworld.blogspot.com)

Our Websites by Rohitashva Singhvi