Hi all, I need your help on a topic related to MS Excel. Is there any formula/function available that could transfer numbers into text? For example, if we have a figure of the TOTAL Amount in any bill as Rs 49,750.00, then it should be automatically converted into words like Forty-Nine Thousand Seven Hundred and Fifty Only. Kindly help me... it's urgent.
From India, Jamshedpur
Acknowledge(0)
Amend(0)
From India, Jamshedpur
Acknowledge(0)Amend(0)
You need to write a macro for that. Check this link for the exact steps - http://lostinexcel.blogspot.in/2012/...l#.UtjPXvv3Sn4. Hope this helps.
From India, Secunderabad
Acknowledge(3)
Amend(0)
11From India, Secunderabad
Acknowledge(3)Amend(0)
Hi Rajiv, you can download "asap utilities". It will also help you for other function. after installed you can put the formula. {=asapspellnumber(D9)} Regards, Hardik
From India, Ahmadabad
Acknowledge(0)Amend(0)
From India, Ahmadabad
Acknowledge(0)Amend(0)
Hi SRK, I am using Office 2007. but in that function there is one problem i got. it is directly converted in Million, Trillion after 10 Thousands. plz help regards Rajiv Sri
From India, Jamshedpur
Acknowledge(0)Amend(0)
From India, Jamshedpur
Acknowledge(0)Amend(0)
Function to Convert Numbers to Words in Excel
Function Wtf(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) = "Forty"
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
If Val(Left(FIGURE, 9)) > 1 Then
Wtf = "Rupees "
ElseIf Val(Left(FIGURE, 9)) = 1 Then
Wtf = "Rupee "
End If
For i = 1 To 3
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
Wtf = Wtf & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
Wtf = Wtf & tens(Val(Left(FIGURE, 1)))
Wtf = Wtf & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
If i = 1 And Val(Left(FIGURE, 2)) > 0 Then
Wtf = Wtf & " Crore "
ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then
Wtf = Wtf & " Lakh "
ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then
Wtf = Wtf & " Thousand "
End If
FIGURE = Mid(FIGURE, 3)
Next i
If Val(Left(FIGURE, 1)) > 0 Then
Wtf = Wtf & 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
Wtf = Wtf & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
Wtf = Wtf & tens(Val(Left(FIGURE, 1)))
Wtf = Wtf & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
FIGURE = Mid(FIGURE, 4)
If Val(FIGURE) > 0 Then
Wtf = Wtf & " Paise "
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
Wtf = Wtf & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
Wtf = Wtf & tens(Val(Left(FIGURE, 1)))
Wtf = Wtf & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
End If
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
If Val(FIGURE) > 0 Then
Wtf = Wtf & " Only "
End If
End Function
From India, Bangalore
Acknowledge(2)
Amend(0)
Function Wtf(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) = "Forty"
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
If Val(Left(FIGURE, 9)) > 1 Then
Wtf = "Rupees "
ElseIf Val(Left(FIGURE, 9)) = 1 Then
Wtf = "Rupee "
End If
For i = 1 To 3
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
Wtf = Wtf & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
Wtf = Wtf & tens(Val(Left(FIGURE, 1)))
Wtf = Wtf & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
If i = 1 And Val(Left(FIGURE, 2)) > 0 Then
Wtf = Wtf & " Crore "
ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then
Wtf = Wtf & " Lakh "
ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then
Wtf = Wtf & " Thousand "
End If
FIGURE = Mid(FIGURE, 3)
Next i
If Val(Left(FIGURE, 1)) > 0 Then
Wtf = Wtf & 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
Wtf = Wtf & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
Wtf = Wtf & tens(Val(Left(FIGURE, 1)))
Wtf = Wtf & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
FIGURE = Mid(FIGURE, 4)
If Val(FIGURE) > 0 Then
Wtf = Wtf & " Paise "
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
Wtf = Wtf & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
Wtf = Wtf & tens(Val(Left(FIGURE, 1)))
Wtf = Wtf & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
End If
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
If Val(FIGURE) > 0 Then
Wtf = Wtf & " Only "
End If
End Function
From India, Bangalore
Acknowledge(2)Amend(0)
The amount in words can be calculated in Excel itself just by incorporating the attached Excel sheet into your workbook. It will work for one invoice value or amount, and if you want it for more than one amount, then you can copy and extend the formula for more than one cell value.
I created this for one of my friends who generates simple quotations and invoices using Excel, and it works fine for him.
Regards
From India, Chennai
Acknowledge(0)Amend(0)
I created this for one of my friends who generates simple quotations and invoices using Excel, and it works fine for him.
Regards
From India, Chennai
Acknowledge(0)Amend(0)
Dear All, Please find enclosed macro file for converting Numeric figures into Indian rupee. courtesy:CMA.ANKUR
From India, Delhi
Acknowledge(0)Amend(0)
From India, Delhi
Acknowledge(0)Amend(0)CiteHR is an AI-augmented HR knowledge and collaboration platform, enabling HR professionals to solve real-world challenges, validate decisions, and stay ahead through collective intelligence and machine-enhanced guidance. Join Our Platform.
Excel Functions Dictionary.xls
Process flow for Updation of Aadhar Number.pdf