Hi all, I need your help on a topic related to Ms Excel. is there any formula/functions available who could transfer the Numbers in Text. for example: If we have any figure of TOTAL Amount in any Bill is Rs 49750.00, then it would be converted automatically in Words Like Forty-Nine Thousands Seven Hundreds and Fifty Only.
Kindly Help me... it's urgent.
From India, Jamshedpur
Kindly Help me... it's urgent.
From India, Jamshedpur
Hi
You need to write a macro for that. Check this link for the exact steps- How To Convert Number Into Words In MS Excel ? (Example 100 = Hundred Dollars) | Microsoft Excel. Hope this helps.
From India, Secunderabad
11You need to write a macro for that. Check this link for the exact steps- How To Convert Number Into Words In MS Excel ? (Example 100 = Hundred Dollars) | Microsoft Excel. Hope this helps.
From India, Secunderabad
Which version of excel are you using? Typically you should be able to re-use the macro by way of copy and paste from one workbook to another.
From India, Secunderabad
From India, Secunderabad
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
From India, Ahmadabad
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
From India, Jamshedpur
Try this code instead of the above one, it is working fine - Foreign Post Office Mumbai: Excel Function to Convert Rupees in Figure to Rupees in Word
From India, Secunderabad
From India, Secunderabad
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) = "Fourty"
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
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) = "Fourty"
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
Thanks to All for your Suggestion... what to do if
(1) i want to use the same Macro in any system with same file..?
(2) I want to use the same Macro in any MS Excel File on same System for long time...?
From India, Jamshedpur
(1) i want to use the same Macro in any system with same file..?
(2) I want to use the same Macro in any MS Excel File on same System for long time...?
From India, Jamshedpur
Rajiv, Check out these two pages from the microsoft site - Copy your macros to a Personal Macro Workbook - Excel - Office.com and Copy a macro module to another workbook - Excel - Office.com.
From India, Secunderabad
From India, Secunderabad
Hi Hello all
Amt in words canl be calculated in excel itself just by incorporating this attached excel sheet
in your workbook. It will work for one Invoice value or amt, and if you want for more than one
amt then you have the copy & extend the link for more than one cell value.
I create this for one of my friend who generates simple quotation's and invoice using excel and
it works fine for him.
From India, Chennai
Amt in words canl be calculated in excel itself just by incorporating this attached excel sheet
in your workbook. It will work for one Invoice value or amt, and if you want for more than one
amt then you have the copy & extend the link for more than one cell value.
I create this for one of my friend who generates simple quotation's and invoice using excel and
it works fine for him.
From India, Chennai
Community Support and Knowledge-base on business, career and organisational prospects and issues - Register and Log In to CiteHR and post your query, download formats and be part of a fostered community of professionals.
MS-EXCEL - INTRODUCTION TO PIVOT TABLES.pdf
The wonders of Ms excel.zip