The following dataset contains a list of products and their sales values. We will try to enter the numbers in the Sales column as words with Dirhams in the Sales in Word column.
Method 1 – Using a Formula to Spell Numbers in Dirhams in Excel
Steps:
- Select cell D4, where we want to apply the formula.
- Enter the following formula in this cell:
=SWITCH((LEFT(TEXT(ROUND(C4,0),"000000000"))+1),1," ",2,"One",3,"Two",4,"Three",5,"Four",6,"Five",7,"Six",8,"Seven",9,"Eight",10,"Nine")&IF(--LEFT(TEXT(ROUND(C4,0),"000000000"))=0,,IF(AND(--MID(TEXT(ROUND(C4,0),"000000000"),2,1)=0,--MID(TEXT(ROUND(C4,0),"000000000"),3,1)=0)," Hundred"," Hundred and "))
&SWITCH((MID(TEXT(ROUND(C4,0),"000000000"),2,1)+1),1,"",2,"",3,"Twenty ",4,"Thirty ",5,"Forty ",6,"Fifty ",7,"Sixty ",8,"Seventy",9,"Eighty ",10,"Ninety ")&IF(--MID(TEXT(ROUND(C4,0),"000000000"),2,1
<>1,SWITCH((MID(TEXT(ROUND(C4,0),"000000000"),3,1)+1),1,"",2,"One",3,"Two",4,"Three",5,"Four",6,"Five",7,"Six",8,"Seven",9,"Eight",10,"Nine"),SWITCH((MID(TEXT(ROUND(C4,0),"000000000"),3,1)+1),1,"Ten",2,"Eleven",3,"Twelve",4,"Thirteen",5,"Fourteen",6,"Fifteen",7,"Sixteen",8,"Seventeen",9,"Eighteen",10,"Nineteen"))&IF((--LEFT(TEXT(ROUND(C4,0),"000000000"))+MID(TEXT(ROUND(C4,0),"000000000"),2,1)+MID(TEXT(ROUND(C4,0),"000000000"),3,1))=0,,IF(AND((--MID(TEXT(ROUND(C4,0),"000000000"),4,1)+MID(TEXT(ROUND(C4,0),"000000000"),5,1)+MID(TEXT(ROUND(C4,0),"000000000"),6,1)+MID(TEXT(ROUND(C4,0),"000000000"),7,1))=0,(--MID(TEXT(ROUND(C4,0),"000000000"),8,1)+RIGHT(TEXT(ROUND(C4,0),"000000000")))>0)," Million and "," Million "))&SWITCH(MID(TEXT(ROUND(C4,0),"000000000"),4,1)+1,1,"",2,"One",3,"Two",4,"Three",5,"Four",6,"Five",7,"Six",8,"Seven",9,"Eight",10,"Nine")&IF(--MID(TEXT(ROUND(C4,0),"000000000"),4,1)=0,,IF(AND(--MID(TEXT(ROUND(C4,0),"000000000"),5,1)=0,--MID(TEXT(ROUND(C4,0),"000000000"),6,1)=0)," Hundred"," Hundred and"))&SWITCH(MID(TEXT(ROUND(C4,0),"000000000"),5,1)+1,1,"",2,"",3," Twenty",4," Thirty",5," Forty",6," Fifty",7," Sixty",8," Seventy",9," Eighty",10," Ninety")&IF(--MID(TEXT(ROUND(C4,0),"000000000"),5,1)<>1,SWITCH(MID(TEXT(ROUND(C4,0),"000000000"),6,1)+1,1,"",2," One",3," Two",4," Three",5," Four",6," Five",7," Six",8," Seven",9," Eight",10," Nine"),SWITCH(MID(TEXT(ROUND(C4,0),"000000000"),6,1)+1,1," Ten",2," Eleven",3," Twelve",4," Thirteen",5," Fourteen",6," Fifteen",7," Sixteen",8," Seventeen",9," Eighteen",10," Nineteen"))&IF((--MID(TEXT(ROUND(C4,0),"000000000"),4,1)+MID(TEXT(ROUND(C4,0),"000000000"),5,1)+MID(TEXT(ROUND(C4,0),"000000000"),6,1))=0,,IF(OR((--MID(TEXT(ROUND(C4,0),"000000000"),7,1)+MID(TEXT(ROUND(C4,0),"000000000"),8,1)+MID(TEXT(ROUND(C4,0),"000000000"),9,1))=0,--MID(TEXT(ROUND(C4,0),"000000000"),7,1)<>0)," Thousand "," Thousand and "))&SWITCH(MID(TEXT(ROUND(C4,0),"000000000"),7,1)+1,1,"",2,"One",3,"Two",4,"Three",5,"Four",6,"Five",7,"Six",8,"Seven",9,"Eight",10,"Nine")&IF(--MID(TEXT(ROUND(C4,0),"000000000"),7,1)=0,,IF(AND(--MID(TEXT(ROUND(C4,0),"000000000"),8,1)=0,--MID(TEXT(ROUND(C4,0),"000000000"),9,1)=0)," Hundred "," Hundred and "))&SWITCH(MID(TEXT(ROUND(C4,0),"000000000"),8,1)+1,1,"",2,"",3,"Twenty ",4,"Thirty ",5,"Forty ",6,"Fifty ",7,"Sixty ",8,"Seventy ",9,"Eighty ",10,"Ninety ")&IF(--MID(TEXT(ROUND(C4,0),"000000000"),8,1)<>1,SWITCH(MID(TEXT(ROUND(C4,0),"000000000"),9,1)+1,1,"",2,"One",3,"Two",4,"Three",5,"Four",6,"Five",7,"Six",8,"Seven",9,"Eight",10,"Nine"),SWITCH(MID(TEXT(ROUND(C4,0),"000000000"),9,1)+1,1,"Ten",2,"Eleven",3,"Twelve",4,"Thirteen",5,"Fourteen",6,"Fifteen",7,"Sixteen",8,"Seventeen",9,"Eighteen",10,"Nineteen"))&" Dirhams"
 In this large formula, you just need to change the cell reference from C4 to any other reference and the currency from Dirhams to any other currency as per your requirement.
Note: To easily change all of the cell references, you can use the Find and Replace option to replace C4 from any other reference.
We will explain the first part of the formula, which will help you understand the whole formula.
=SWITCH((LEFT(TEXT(ROUND(C4,0),"000000000"))+1),1," ",2,"One",3,"Two",4,"Three",5,"Four",6,"Five",7,"Six",8,"Seven",9,"Eight",10,"Nine")&IF(--LEFT(TEXT(ROUND(C4,0),"000000000"))=0,,IF(AND(--MID(TEXT(ROUND(C4,0),"000000000"),2,1)=0,--MID(TEXT(ROUND(C4,0),"000000000"),3,1)=0)," Hundred"," Hundred and "))
Formula Breakdown
- ROUND(C4,0) → becomes
- ROUND(29484.09,0) → will round up the fraction value.
- Output → 29484
- ROUND(29484.09,0) → will round up the fraction value.
- TEXT(ROUND(C4,0),”000000000″) → becomes
- TEXT(29484,”000000000″) → change the format of the number and convert it into text
- Output → 000029484
- TEXT(29484,”000000000″) → change the format of the number and convert it into text
- LEFT(TEXT(ROUND(C4,0),”000000000″)) → becomes
- LEFT(000029484) → extracts the first character from this number text value
- Output → 0
- LEFT(000029484) → extracts the first character from this number text value
- LEFT(TEXT(ROUND(C4,0),”000000000″))+1 → becomes
- “0”+1 → add 1 to the text value and convert it into a number
- Output → 1
- “0”+1 → add 1 to the text value and convert it into a number
- SWITCH((LEFT(TEXT(ROUND(C4,0),”000000000″))+1),1,” “,2,”One”,3,”Two”,4,”Three”,5,”Four”,6,”Five”,7,”Six”,8,”Seven”,9,”Eight”,10,”Nine”) → becomes
- SWITCH(1,1,””,2,”One”,3,”Two”,4,”Three”,5,”Four”,6,”Five”,7,”Six”,8,”Seven”,9,”Eight”,10,”Nine”) → SWITCH will return a result according to the assigned value (here, we assigned a Blank for 1)
- Output → BLANK
- SWITCH(1,1,””,2,”One”,3,”Two”,4,”Three”,5,”Four”,6,”Five”,7,”Six”,8,”Seven”,9,”Eight”,10,”Nine”) → SWITCH will return a result according to the assigned value (here, we assigned a Blank for 1)
- –LEFT(TEXT(ROUND(C4,0),”000000000″))=0 → becomes
- –“0″=0 → double negation will convert text to a number
- 0=0 → returns TRUE for fulfilling the condition
- Output → TRUE
- IF(–LEFT(TEXT(ROUND(C4,0),”000000000″))=0,,IF(AND(–MID(TEXT(ROUND(C4,0),”000000000″),2,1)=0,–MID(TEXT(ROUND(C4,0),”000000000″),3,1)=0),” Hundred”,” Hundred and “)) → becomes
- TRUE,,IF(AND(–MID(TEXT(ROUND(C4,0),”000000000″),2,1)=0,–MID(TEXT(ROUND(C4,0),”000000000″),3,1)=0),” Hundred”,” Hundred and “)) → returns a Blank for fulfilling condition
- Output → BLANK
- TRUE,,IF(AND(–MID(TEXT(ROUND(C4,0),”000000000″),2,1)=0,–MID(TEXT(ROUND(C4,0),”000000000″),3,1)=0),” Hundred”,” Hundred and “)) → returns a Blank for fulfilling condition
- Press ENTER and drag down the Fill Handle.
You will be able to spell the number in dirhams easily.
Method 2 – Using VBA to Spell Numbers in Dirhams in Excel
We will consider the whole numbers as Dirhams and fraction numbers as Fils.
Steps:
- Go to the Developer tab >> Visual Basic
The Visual Basic Editor window will open up.
- Go to the Insert tab >> Module.
A new module Module1 will be created.
- Enter the following code in your created module:
Function Dirham_word(ByVal amount)
   Dim whole_num, fraction_num, changed_val
   Dim pos_dec, increased_amount
   ReDim Position(9) As String
   Position(2) = " Thousand "
   Position(3) = " Million "
   Position(4) = " Billion "
   Position(5) = " Trillion "
   amount = Trim(Str(amount))
   pos_dec = InStr(amount, ".")
   If pos_dec > 0 Then
       fraction_num = extract_10(Left(Mid(amount, pos_dec + 1) & "00", 2))
       amount = Trim(Left(amount, pos_dec - 1))
   End If
   increased_amount = 1
   Do While amount <> ""
       changed_val = extract_100(Right(amount, 3))
       If changed_val <> "" Then whole_num = changed_val & Position(increased_amount) & whole_num
       If Len(amount) > 3 Then
           amount = Left(amount, Len(amount) - 3)
       Else
           amount = ""
       End If
       increased_amount = increased_amount + 1
   Loop
   Select Case whole_num
       Case ""
           whole_num = "No Dirham"
       Case "One"
           whole_num = "One Dirham"
        Case Else
           whole_num = whole_num & " Dirham"
   End Select
   Select Case fraction_num
       Case ""
           fraction_num = " and No Fils"
       Case "One"
           fraction_num = " and One Fil"
             Case Else
           fraction_num = " and " & fraction_num & " Fils"
   End Select
   Dirham_word = whole_num & fraction_num
End Function
Function extract_100(ByVal amount)
   Dim Output As String
   If Val(amount) = 0 Then Exit Function
   amount = Right("000" & amount, 3)
   If Mid(amount, 1, 1) <> "0" Then
       Output = extract_1(Mid(amount, 1, 1)) & " Hundred "
   End If
   If Mid(amount, 2, 1) <> "0" Then
       Output = Output & extract_10(Mid(amount, 2))
   Else
       Output = Output & extract_1(Mid(amount, 3))
   End If
   extract_100 = Output
End Function
Function extract_10(amount_10)
   Dim Result As String
   Output = ""
   If Val(Left(amount_10, 1)) = 1 Then
       Select Case Val(amount_10)
           Case 10: Output = "Ten"
           Case 11: Output = "Eleven"
           Case 12: Output = "Twelve"
           Case 13: Output = "Thirteen"
           Case 14: Output = "Fourteen"
           Case 15: Output = "Fifteen"
           Case 16: Output = "Sixteen"
           Case 17: Output = "Seventeen"
           Case 18: Output = "Eighteen"
           Case 19: Output = "Nineteen"
           Case Else
       End Select
   Else
       Select Case Val(Left(amount_10, 1))
           Case 2: Output = "Twenty "
           Case 3: Output = "Thirty "
           Case 4: Output = "Forty "
           Case 5: Output = "Fifty "
           Case 6: Output = "Sixty "
           Case 7: Output = "Seventy "
           Case 8: Output = "Eighty "
           Case 9: Output = "Ninety "
           Case Else
       End Select
       Output = Output & extract_1(Right(amount_10, 1))
   End If
   extract_10 = Output
End Function
Function extract_1(amount_1)
   Select Case Val(amount_1)
       Case 1: extract_1 = "One"
       Case 2: extract_1 = "Two"
       Case 3: extract_1 = "Three"
       Case 4: extract_1 = "Four"
       Case 5: extract_1 = "Five"
       Case 6: extract_1 = "Six"
       Case 7: extract_1 = "Seven"
       Case 8: extract_1 = "Eight"
       Case 9: extract_1 = "Nine"
       Case Else: extract_1 = ""
   End Select
End Function
Formula Breakdown:
- Here, a total of 4 functions are created among them the main function is Dirham_word and the other sub-functions are extract_100, extract_10, and extract_1. extract_100, extract_10, and extract_1 will be used to convert texts in the ranges 100-999, 10-99, and 1-9.
- We have declared whole_num, fraction_num, changed_val, pos_dec, and increased_amount as Variant.
- Throughout the code, we have applied the CASE statement for considering different cases.
After saving the code, return to the worksheet.
- Apply the created function in cell D4.
=Dirham_word(C4)
- Press ENTER and drag down the Fill Handle.
You will have the following result.
Read More: How to Spell Number in Taka in Excel
Practice Section
For practicing, we have added a Practice portion on each sheet on the right portion.
Download the Workbook
Related Articles
- [Solved] Spell Number Not Working in Excel
- How to Use Spell Number in ExcelÂ
- How to Convert Peso Number to Words in Excel
- How to Convert Number to Words in ExcelÂ
- How to Convert Number to Words in Excel Without VBA
- How to Convert Number to Words in Excel in Rupees
- How to Spell Number Without Currency in Excel
<< Go Back to Spell Number in Excel | Convert Number to Text | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!