Information abt EXCEL formula

This query is : Resolved

Querist : Anonymous

Querist : Anonymous (Querist)

27 March 2010 CAN ANYBODY HELP!
I need a formula in EXCEL which converts the Value of Figures into Words Automatically.
Ex : If I enter 25,000/- in should convert into Words Twenty Five Thousand only.
Pls if anybody knows abt it Pls help me!!!!!!

CS Sunita Jamkhande

CS Sunita Jamkhande (Expert)
Follow

29 March 2010 Hi,

To convert number into words : Follow below steps
1. Press Alt+F11
2. Click insert on menu bar > Module Insert
3. Copy & paste in module the following code

Function words(fig, Optional point = "Point") As String
Dim digit(14) As Integer
alpha = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
figi = Trim(StrReverse(Str(Int(Abs(fig)))))
For i = 1 To Len(figi)
digit(i) = Mid(figi, i, 1)
Next
For i = 2 To Len(figi) Step 3
If digit(i) = 1 Then
digit(i) = digit(i - 1) + 10: digit(i - 1) = 0
Else: If digit(i) > 1 Then digit(i) = digit(i) + 18
End If
Next
For i = 1 To Len(figi)
If (i Mod 3) = 0 And digit(i) > 0 Then words = "hundred " & words
If (i Mod 3) = 1 And digit(i) + digit(i + 1) + digit(i + 2) > 0 Then _
words = Choose(i / 3, "thousand ", "million ", "billion ") & words
words = Trim(alpha(digit(i)) & " " & words)
Next
If fig Int(fig) Then
figc = StrReverse(figi)
If figc = 0 Then figc = ""
figd = Trim(WorksheetFunction.Substitute(Str(Abs(fig)), figc & ".", ""))
words = Trim(words & " " & point)
For i = 1 To Len(figd)
If Val(Mid(figd, i, 1)) > 0 Then
words = words & " " & alpha(Mid(figd, i, 1))
Else: words = words & " Zero"
End If
Next
End If

4.If fig function > user defined > A1 or (relevent cell)
Or after completing step 1 to 4 use formula = words(A1)
5.Then if in CELL A1 is 10 number the result is ten.

Regards

CS Sunita Jamkhande

Message likes : 1 times

Querist : Anonymous

Querist : Anonymous (Querist)

30 March 2010 thanks for the answer miss. But the formulae is too lenghty and i have tried it but i could not get the result. So, can u please attach the Excel sheet which contains the formulae it will be a great help to me.

Upgrade to CAclubindia Pro. Subscribe Now!

CS Sunita Jamkhande

CS Sunita Jamkhande (Expert)
Follow

31 March 2010 Please forward your email id.So that I can mail the required files.

Message likes : 1 times

Querist : Anonymous

Querist : Anonymous (Querist)

31 March 2010 Its rahulpandit150@gmail.com thanks for the reply

CS Sunita Jamkhande

CS Sunita Jamkhande (Expert)
Follow

05 April 2010 I already sent mail on the id mentioned by you on 1st April 2010.Please confirm your correct id.

Querist : Anonymous