User Defined Function in Excel to Convert Currency to Words
Many a times we require to change the Numbers in Words. For Example: 6734862=Sixty Seven Lakhs Thirty Four Thousand Eight Hundred Sixty Two. In Excel, there is no Formula as such to Convert Numbers in Words. I have written one UDF : User Defined Function. After using this Function, you will be able to change any numbers in Words. Now there can be two way of converting Numbers in to Words in terms of Millions, Billions etc or Other way it could be in Lakhs and Crores.
How to Convert Numbers in Words or Currency to Words
Step 1. Open your Excel Workbook
Step 2. Press Alt + F11
Step 3. Now Add a Regular Module in Excel
Step 4. Now Copy and Paste the Below Code in the Module
Step 5. Once you have copied and Pasted the Below Code in a Module, you can use the Below Formula in your Workbook in any WorkSheet as shown in Below Picture:
Currenct2Word(B5)
B5 : Is the Cell Address Where the Number is there
Function Currency2Word(ByVal MyNumber) Dim WithoutCrore, Crore, DecimalPlace Dim DecimalNumber MyNumber = Trim(Str(MyNumber)) 'get the Decimal Position DecimalPlace = InStr(MyNumber, ".") 'Get the Decimal Part of the whole Number If DecimalPlace <> 0 Then DecimalNumber = Right(MyNumber, Len(MyNumber)  DecimalPlace) Else DecimalNumber = "" End If 'Get the Decimal Free Number If DecimalPlace > 0 Then MyNumber = Trim(Left(MyNumber, DecimalPlace  1)) End If 'Divide the Decimal Free Number in 2 part  WithoutCrore and Crore 'WithoutCrore Will have only that part part which has Less than Crore 'Crore Will have only Crore Part If DecimalNumber <> "" Then WithoutCrore = Right(MyNumber, 7) & "." & DecimalNumber Else WithoutCrore = Right(MyNumber, 7) End If Crore = Left(MyNumber, Len(MyNumber)  Len(Right(MyNumber, 7))) If Crore <> "" Then 'If the amount is greater than Crore Currency2Word = CurrToWord(Crore) & " Crore " & CurrToWord(WithoutCrore) Else 'If the amount is lesser than Crore Currency2Word = CurrToWord(WithoutCrore) End If End Function Function CurrToWord(ByVal MyNumber) Dim WholeNumber, Deci, Var Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Lakhs " 'String representation of amount. MyNumber = Trim(Str(MyNumber)) 'Get the Decimal Place if any DecimalPlace = InStr(MyNumber, ".") ' Convert Deci and set MyNumber to Rupee amount. If DecimalPlace > 0 Then Deci = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace  1)) End If If Len(Trim(Str(MyNumber))) Mod 2 = 0 Then MyNumber = "0" & Trim(Str(MyNumber)) Else MyNumber = Trim(Str(MyNumber)) End If Count = 1 If Len(MyNumber) = 1 Then MyNumber = "00" & MyNumber End If Do While MyNumber <> "" If Count = 1 Or Count > 7 Then Var = GetHundreds(Right(MyNumber, 3)) Else Var = GetTens(Right(MyNumber, 2)) End If If Var <> "" Then WholeNumber = Var & Place(Count) & WholeNumber If Len(MyNumber) >= 1 And Count < 2 Then MyNumber = Left(MyNumber, Len(MyNumber)  3) ElseIf Len(MyNumber) >= 1 And Count > 1 Then MyNumber = Left(MyNumber, Len(MyNumber)  2) Else MyNumber = "" End If Count = Count + 1 Loop If (Deci = "") Then CurrToWord = WholeNumber & Deci Else CurrToWord = WholeNumber & " and " & Deci & " Paise Only" End If End Function ' Converts a number from 100999 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 = GetSingleDigit(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 & GetSingleDigit(Mid(MyNumber, 3)) End If GetHundreds = Result End Function ' This will Convert a number from 10 to 99 into text. '********************************************************** Function GetTens(TensText) Dim Result As String Result = "" ' Null out the Varorary function value. If Val(Left(TensText, 1)) = 1 Then ' If value between 1019... 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 2099... 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 & GetSingleDigit _ (Right(TensText, 1)) ' Retrieve ones place. End If GetTens = Result End Function ' Converts a number from 1 to 9 into text. '**************************************************** Function GetSingleDigit(SingleDigit) Select Case Val(SingleDigit) Case 1: GetSingleDigit = "One" Case 2: GetSingleDigit = "Two" Case 3: GetSingleDigit = "Three" Case 4: GetSingleDigit = "Four" Case 5: GetSingleDigit = "Five" Case 6: GetSingleDigit = "Six" Case 7: GetSingleDigit = "Seven" Case 8: GetSingleDigit = "Eight" Case 9: GetSingleDigit = "Nine" Case Else: GetSingleDigit = "" End Select End Function
Download Excel File with the Above User Defined Function ( UDF )
To Check out Excel Macro Tutorials, visit Excel Macro Tutorial 
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P

SUBSCRIBEAND GET A FREE!EBOOK FOR EXCEL VBA BEGINNERSDON'T MISS ANY NEW ARTICLE ! 
Get your FREE! EBook & Have Every New Article Delivered Straight To Your EmailBox 
[…] UDF – Is File Open? UDF to Convert Currency to Words UDF to Count Words in Cell UDF to Extract Numbers from a cell UDF to Extract Special Characters […]