User Defined Function in Excel to Convert Currency to Words

.

Many 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 into 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 ways of converting Numbers into Words in terms of Millions, Billions, etc Another way 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

Currency to Words

Numbers to convert in words



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 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 = 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 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 & 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


To Check out Excel Macro Tutorials, visit Excel Macro Tutorial

Download Excel File - Currency in Words

You can download this excel workbook which can convert your numbers into INR, USD, or any. other currency numbers in Words. Enjoy

Number in Words

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

0 Comments

Trackbacks/Pingbacks

  1. Learn Excel Macro How to create User Defined Function in Excel - [...] UDF – Is File Open? UDF to Convert Currency to Words UDF to Count Words in Cell UDF to…
  2. Welcome to LearnExcelMacro.com FREE Download - Automated Invoice Template in Excel - […] Total Amount in Invoice will also get converted in text by using Excel VBA function to convert currency in…
  3. How to create User Defined Functions in Excel - Welcome to LearnExcelMacro.com - […]     2. UDF to Convert Currency to Words […]
  4. Calculate MOD of Large Numbers in Excel - Let's excel in Excel - […] : AGE CALCULATIONEXCEL UDF: TO EXTRACT ALL NUMBERS, SPECIAL CHARACTERS AND ALPHABETS FROM A STRINGUSER DEFINED FUNCTION IN EXCEL…
  5. UDF to Convert Numbers to Letters - Spell Currency in Words - […] and Alphabets from a String Create UDF in Excel in 4 Steps – Your Own Excel Formula User Defined…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest