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

UDF : Currency To Word

UDF : Currency To Word



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

Download Excel File with the Above User Defined Function ( UDF )

UDF : Currency To Word

UDF : Currency To Word

Download Now

 
To Check out Excel Macro Tutorials, visit Excel Macro Tutorial

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!