Excel Spell Number Function

Excel Spell Number Function

Excel Functions:

                                  A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel. These functions and formulas are predefine and limited so what if your desire Function is not available in excel?

For our desire output or Function we use VBA Visual Basic for Application and write code for our Macro that return our required output. Visual Basic for Applications (VBA) is an implementation of Microsoft's event-driven programming language Visual Basic 6, which was discontinued in 2008, and its associated integrated development environment (IDE). Although Visual Basic is no longer supported or updated by Microsoft, the VBA programming language was upgraded in 2010 with the introduction of Visual Basic for Applications 7 in Microsoft Office applications.

Click here to Download SpellNumber Add-ins and watch a video tutorial how to add Excel Add-ins.

To convert number into words i create a function call SpellNumber, that convert any number into words. Code for that function is given below...

 Function SpellNumber(amt As Variant) As Variant  
 Dim FIGURE As Variant  
 Dim LENFIG As Integer  
 Dim i As Integer  
 Dim WORDs(19) As String  
 Dim tens(9) As String  
 WORDs(1) = "One"  
 WORDs(2) = "Two"  
 WORDs(3) = "Three"  
 WORDs(4) = "Four"   
 WORDs(5) = "Five"   
 WORDs(6) = "Six"   
 WORDs(7) = "Seven"   
 WORDs(8) = "Eight"   
 WORDs(9) = "Nine"   
 WORDs(10) = "Ten"   
 WORDs(11) = "Eleven"   
 WORDs(12) = "Twelve"   
 WORDs(13) = "Thirteen"   
 WORDs(14) = "Fourteen"   
 WORDs(15) = "Fifteen"   
 WORDs(16) = "Sixteen"   
 WORDs(17) = "Seventeen"   
 WORDs(18) = "Eighteen"   
 WORDs(19) = "Nineteen"   
 tens(2) = "Twenty"   
 tens(3) = "Thirty"   
 tens(4) = "Fourty"   
 tens(5) = "Fifty"   
 tens(6) = "Sixty"   
 tens(7) = "Seventy"   
 tens(8) = "Eighty"   
 tens(9) = "Ninety"   
 FIGURE = amt  
 FIGURE = Format(FIGURE, "FIXED")  
 FIGLEN = Len(FIGURE)  
 If FIGLEN < 12 Then  
 FIGURE = Space(12 - FIGLEN) & FIGURE  
 End If  
 For i = 1 To 3  
 If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then  
 SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))  
 ElseIf Val(Left(FIGURE, 2)) > 19 Then  
 SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))  
 SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))  
 End If  
 If i = 1 And Val(Left(FIGURE, 2)) > 0 Then  
 SpellNumber = SpellNumber & " Crore "  
 ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then  
 SpellNumber = SpellNumber & " Lakh "  
 ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then  
 SpellNumber = SpellNumber & " Thousand "  
 End If  
 FIGURE = Mid(FIGURE, 3)  
 Next i  
 If Val(Left(FIGURE, 1)) > 0 Then  
 SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 1))) + " Hundred "  
 End If  
 FIGURE = Mid(FIGURE, 2)  
 If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then  
 SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))  
 ElseIf Val(Left(FIGURE, 2)) > 19 Then  
 SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))  
 SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))  
 End If  
 FIGURE = Mid(FIGURE, 4)  
 If Val(FIGURE) > 0 Then  
 SpellNumber = SpellNumber & " Paise "  
 If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then  
 SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))  
 ElseIf Val(Left(FIGURE, 2)) > 19 Then  
 SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))  
 SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))  
 End If  
 End If  
 FIGURE = amt  
 FIGURE = Format(FIGURE, "FIXED")  
 If Val(FIGURE) > 0 Then  
 SpellNumber = SpellNumber & " Only "  
 End If  
 End Function