Rohitashva Singhvi
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:
- 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.
- 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.
- 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.
- 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.
- 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 FunctionDirham 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)
RSS Error: https://wealthcreatorhub.in/web-stories/feed/ is invalid XML, likely due to invalid characters. XML error: XML_ERR_NAME_REQUIRED at line 1, column 151