Spellindian Formula In Excel: A Comprehensive Guide

September 02, 2023 Posting Komentar

Excel formula to convert number to words in rupees Lakhs, Crores from www.teachoo.com

Excel is a powerful tool that helps you in data analysis, calculations, and visualization. It comes with various formulas, and one such useful formula is Spellindian. In this blog post, we will discuss in detail about the Spellindian formula in Excel and how it can help you in your work.

What is Spellindian Formula in Excel?

Spellindian is a custom formula in Excel that is used to convert numbers to Indian currency format. This formula is useful for those who deal with Indian currency in their work. The Spellindian formula is not a built-in function in Excel, and you need to create it manually using VBA code.

How to Create Spellindian Formula in Excel?

To create the Spellindian formula in Excel, you need to follow these steps:

Open a new Excel workbook.

Press Alt + F11 to open the Visual Basic Editor.

Click on Insert and select Module.

Copy and paste the VBA code for Spellindian formula.

Close the Visual Basic Editor and go back to your Excel workbook.

Type the number you want to convert to Indian currency format.

Type the formula " =Spellindian(A1)" in a cell where you want to display the converted value.

The VBA code for Spellindian formula is:

 Function Spellindian(ByVal MyNumber) Dim Dollars, Cents, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) =" Thousand " Place(3) =" Lakh " Place(4) =" Crore " MyNumber = Trim(Str(MyNumber)) DecimalPlace = InStr(MyNumber, ".") 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 ="Zero Rupees " Case "One" Dollars ="One Rupee " Case Else Dollars = Dollars & " Rupees " End Select Select Case Cents Case "" Cents =" " Case "One" Cents =" and One Paisa" Case Else Cents =" and " & Cents & " Paise" End Select Spellindian = Dollars & Cents End Function Function GetHundreds(ByVal MyNumber) Dim Result As String If Val(MyNumber) = 0 Then Exit Function MyNumber = Right("000" & MyNumber, 3) If Mid(MyNumber, 1, 1) <> "0" Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred " End If 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 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 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

How to Use Spellindian Formula in Excel?

To use the Spellindian formula in Excel, you need to follow these steps:

Type the number you want to convert to Indian currency format in a cell.

Type the formula " =Spellindian(A1)" in a cell where you want to display the converted value.

Press Enter.

For example, if you want to convert the number 12345 to Indian currency format, you need to type "12345" in a cell and type the formula " =Spellindian(A1)" in another cell. The converted value will be displayed in the second cell as "Twelve Thousand Three Hundred Forty-Five Rupees."

Advantages of Spellindian Formula in Excel

The Spellindian formula in Excel has the following advantages:

It helps you to convert numbers to Indian currency format easily.

It saves time and effort in manual conversion.

It reduces the chances of errors in conversion.

Conclusion

In conclusion, the Spellindian formula in Excel is a useful tool for those who deal with Indian currency in their work. It helps you to convert numbers to Indian currency format easily and saves time and effort in manual conversion. By following the steps mentioned in this blog post, you can create and use the Spellindian formula in Excel. So, start using the Spellindian formula in Excel and make your work easier and error-free.

Happy Excel-ing!