Question about Microsoft Excel for PC

1 Answer

Formula for converting currency fig to words

Please provide me with a formula to convert the currency figuers to words Eg: Rs. 3,240.90 (should convert to "RUPEES THREE THOUSAND TWO HUNDRED FOURTY AND PAISE NINTY)

Posted by on

  • hi_powertran Feb 16, 2009

    Thanks for trying but i am not sure were to paste this as ia m not a profetional request you to tell me step by step procedure to use the formula you have given

  • mraghumagaji May 03, 2009

    formula for converting currency fig to words

×

1 Answer

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    MVP:

    An expert that gotĀ 5 achievements.

    Governor:

    An expert whose answer gotĀ voted for 20 times.

    Hot-Shot:

    An expert who has answered 20 questions.

  • Expert
  • 95 Answers

You need to make some changes to the VB script.

here are the steps to guide you

Open Excel - press Alt F11, Click on Insert, Select Module - Copy paste the below mentioned script>>>



Function SpellNumber(ByVal MyNumber, Optional incRupees As Boolean = True)
Dim Crores, Lakhs, Rupees, Paise, Temp
Dim DecimalPlace As Long, Count As Long
Dim myLakhs, myCrores
ReDim Place(9) As String
Place(2) = " Thousand ": Place(3) = " Million "
Place(4) = " Billion ": Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert Paise and set MyNumber to Rupees amount.
If DecimalPlace > 0 Then
Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
myCrores = MyNumber \ 10000000
myLakhs = (MyNumber - myCrores * 10000000) \ 100000
MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000
Count = 1
Do While myCrores <> ""
Temp = GetHundreds(Right(myCrores, 3))
If Temp <> "" Then Crores = Temp & Place(Count) & Crores
If Len(myCrores) > 3 Then
myCrores = Left(myCrores, Len(myCrores) - 3)
Else
myCrores = ""
End If
Count = Count + 1
Loop
Count = 1
Do While myLakhs <> ""
Temp = GetHundreds(Right(myLakhs, 3))
If Temp <> "" Then Lakhs = Temp & Place(Count) & Lakhs
If Len(myLakhs) > 3 Then
myLakhs = Left(myLakhs, Len(myLakhs) - 3)
Else
myLakhs = ""
End If
Count = Count + 1
Loop
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Crores
Case "": Crores = ""
Case "One": Crores = " One Crore "
Case Else: Crores = Crores & " Crores "
End Select
Select Case Lakhs
Case "": Lakhs = ""
Case "One": Lakhs = " One Lakh "
Case Else: Lakhs = Lakhs & " Lakhs "
End Select
Select Case Rupees
Case "": Rupees = "Zero "
Case "One": Rupees = "One "
Case Else:
Rupees = Rupees
End Select
Select Case Paise
Case "": Paise = " and Paise Zero Only "
Case "One": Paise = " and Paise One Only "
Case Else: Paise = " and Paise " & Paise & " Only "
End Select
SpellNumber = IIf(incRupees, "Rupees", "") & Crores & Lakhs & Rupees & Paise

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 = GetDigit(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 & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary 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 & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function
' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

>>>Save it as a file on the computer - Select the cell where you want to insert the function - go to Insert - Function - User Defined - Spellnumber - Select the reference value against my number and type true in incrupees.


It would give the value in Word format.

The limiation is it can be only use in teh same workbook.

Next time when you want to use the formula, either you have to redo the process or use this workbook and copy paste data in another one.

Let me know if you face any difficulty.

Posted on Feb 13, 2009

  • tans_nut Feb 16, 2009

    Here are the steps:

    1. Open Excel - press Alt F11, It will open Microsoft Visual Basic.

    2. Click on the Insert tab from the top menu and select module. It will open another blank window..

    3. Copy the bold script from below and paste it in that empty box on the VB screen. Save the file as normal on your desktop.

    4. After the file is saved select the cell where you want to insert the function. Click on Insert from the menu, select Function. Select User Defined from teh dropdown and then select spellnumber from teh function list.

    5. Select the reference value against my number and type true in incrupees in the formula window.



    Let me know if face any difficulty. If you are in India, you can call me on 9850981051.



    Function SpellNumber(ByVal MyNumber, Optional incRupees As Boolean = True)
    Dim Crores, Lakhs, Rupees, Paise, Temp
    Dim DecimalPlace As Long, Count As Long
    Dim myLakhs, myCrores
    ReDim Place(9) As String
    Place(2) = " Thousand ": Place(3) = " Million "
    Place(4) = " Billion ": Place(5) = " Trillion "
    ' String representation of amount.
    MyNumber = Trim(Str(MyNumber))
    ' Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, ".")
    ' Convert Paise and set MyNumber to Rupees amount.
    If DecimalPlace > 0 Then
    Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
    MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    myCrores = MyNumber \ 10000000
    myLakhs = (MyNumber - myCrores * 10000000) \ 100000
    MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000
    Count = 1
    Do While myCrores <> ""
    Temp = GetHundreds(Right(myCrores, 3))
    If Temp <> "" Then Crores = Temp & Place(Count) & Crores
    If Len(myCrores) > 3 Then
    myCrores = Left(myCrores, Len(myCrores) - 3)
    Else
    myCrores = ""
    End If
    Count = Count + 1
    Loop
    Count = 1
    Do While myLakhs <> ""
    Temp = GetHundreds(Right(myLakhs, 3))
    If Temp <> "" Then Lakhs = Temp & Place(Count) & Lakhs
    If Len(myLakhs) > 3 Then
    myLakhs = Left(myLakhs, Len(myLakhs) - 3)
    Else
    myLakhs = ""
    End If
    Count = Count + 1
    Loop
    Count = 1
    Do While MyNumber <> ""
    Temp = GetHundreds(Right(MyNumber, 3))
    If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
    If Len(MyNumber) > 3 Then
    MyNumber = Left(MyNumber, Len(MyNumber) - 3)
    Else
    MyNumber = ""
    End If
    Count = Count + 1
    Loop
    Select Case Crores
    Case "": Crores = ""
    Case "One": Crores = " One Crore "
    Case Else: Crores = Crores & " Crores "
    End Select
    Select Case Lakhs
    Case "": Lakhs = ""
    Case "One": Lakhs = " One Lakh "
    Case Else: Lakhs = Lakhs & " Lakhs "
    End Select
    Select Case Rupees
    Case "": Rupees = "Zero "
    Case "One": Rupees = "One "
    Case Else:
    Rupees = Rupees
    End Select
    Select Case Paise
    Case "": Paise = " and Paise Zero Only "
    Case "One": Paise = " and Paise One Only "
    Case Else: Paise = " and Paise " & Paise & " Only "
    End Select
    SpellNumber = IIf(incRupees, "Rupees", "") & Crores & Lakhs & Rupees & Paise

    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 = GetDigit(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 & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
    End Function
    ' Converts a number from 10 to 99 into text.
    Function GetTens(TensText)
    Dim Result As String
    Result = "" ' Null out the temporary 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 & GetDigit _
    (Right(TensText, 1)) ' Retrieve ones place.
    End If
    GetTens = Result
    End Function
    ' Converts a number from 1 to 9 into text.
    Function GetDigit(Digit)
    Select Case Val(Digit)
    Case 1: GetDigit = "One"
    Case 2: GetDigit = "Two"
    Case 3: GetDigit = "Three"
    Case 4: GetDigit = "Four"
    Case 5: GetDigit = "Five"
    Case 6: GetDigit = "Six"
    Case 7: GetDigit = "Seven"
    Case 8: GetDigit = "Eight"
    Case 9: GetDigit = "Nine"
    Case Else: GetDigit = ""
    End Select
    End Functio
    n

×

1 Suggested Answer

6ya6ya
  • 2 Answers

SOURCE: I have freestanding Series 8 dishwasher. Lately during the filling cycle water hammer is occurring. How can this be resolved

Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
goodluck!

Posted on Jan 02, 2017

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Hello, i have 2 questions: 1- i need to know if there is away to convert "Arabic protected PDF book into DOC " 2- i need to know is there is away to supervise my team while the are dealing with...


PDF is the only document format that typically allows you to view the file, instead of editing with many means like data encryption techniques, passwords and local restrictions. Due to these actors, users prefer to convert PDF to Doc format so that they can access and make changes to the data. Once converted to Word format, files become open for modification, editing, printing, copying and extracting information. Using Doremisoft PDF to DOC converter is the most effective method to convert PDF to Doc with utmost ease and accuracy.This is an easy-to-use and efficient PDF to Word utility that converts PDF to Word DOC file with great precision. Good Preservation PDF Converter preserves all the elements of original PDF files, such as text, hyperlinks, images, layouts, tables, columns, and graphics. The best aspect about this utility is that as a user you can also convert password-protected PDF file. During the process of PDF to word conversion, the software prompts the user to enter the password. As a result, it is very important that the password of the PDF file must be known for PDF to Word conversion.Key Features of PDF to docx converter:
1.Support all Windows platforms, Windows Server 2008, Windows 7, Windows Vista, Windows XP, Windows 2003 and Windows 2000 2.Provides complete support to the conversion from encrypted PDF files and password protected documents 3.Preserves the Text, Pictures, and Layout even after the successful conversion of file from PDF to Word DOC format 4.Supports Unicode Character Conversion 5.Perform the PDF to DOC conversion at a very high speed 6.Allows user to convert entire document or only a specific page range 7.Converted files can be automatically opened immediately after conversion complete 8.Provides round the clock technical support 9. Preserve data structure, formatting and properties of content 10. This PDF to Word Converter is compatible with Microsoft Office 2010, 2007, and 2003.Pricing and Availability:Doremisoft PDF to Word converter is priced at $39.95 for single user license. You can downloaded online from Doremisoft pdf to word converter website

Sep 22, 2011 | Microsoft Office Professional 2007 Full...

1 Answer

How to format cell to Indian Rupee


Hi,

You need to right click on the cell and click on:

Format Cells, Number Tab, Currency, Select Rs.

That will convert the cell to display INR.

If you require any further assistance, please comment and let me know, and I will be happy to assist you to further resolve your issue.

I will appreciate your feedback and positive rating for this solution.
Thank you.

Mar 13, 2011 | Microsoft Excel for PC

1 Answer

What is the formula to convert the time of 4:15 to 4.25 hours? I'm trying to add up the number of hours I do child care.


If you calculate 0.1hr as 6mins, you can get what you ask for.. This is just 1/10th of an hr.. to put it more easy, the time which you get, convert it into mins.. (1hr=60mins) and then divide by 60.. For eg:

3hrs = 180mins -> 180/60 = 3hrs
3hrs 20mins = 180+20mins -> 200/60 = 3.33hrs
4hrs 30mins = 240+30mins -> 270/60 = 45.hrs..

Simple math, isn't it??

Mar 20, 2009 | Microsoft Excel for PC

1 Answer

I want to know the excel formula to convert the number in to text example 1 to one. i have surfed many sites but they provide only codings and no formula pls help


hey there! thanks for using Fixya.com!

As far as I know, you cannot convert a numeric entry into any Alpha. But, yes there are some codings for you to convert it or use as such.

Thanks!
don't forget to mark!

Feb 18, 2009 | Computers & Internet

1 Answer

Count how many times a value appears in a column, based on anothe


Go to the cell you want this total in.
Type this formula:
=SUM(IF(Sheet2!C1:C10="EME",IF(Sheet2!N1:N10=1,1,0)))
make sure you end the formula with CTRL - SHIFT - ENTER which makes it an array formula. If you forget, go back to the cell with this formula and press F2 (to edit the cell) and press CTRL - SHIFT - ENTER to convert it to an array formula (Excel will show a little {...} around the formula).

Dec 21, 2008 | Microsoft Excel for PC

6 Answers

Indian Rupee format in Excel spreadsheet


Rupees with Paise
[>9999999]"Rs. "##\,##\,##\,##0.00;[>99999]"Rs. "##\,##\,##0.00;"Rs.
"##,##0.00

Rupees without Paise
[>9999999]"Rs. "##\,##\,##\,##0;[>99999]"Rs."##\,##\,##0;"Rs. "##,##0

Rupees without Rs. notation
[>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00

Sep 14, 2008 | Microsoft Excel for PC

4 Answers

CONVERT FIGURE INTO WORD


i want to convert the figure / number in word

eg. 1,000 = ONE THOUSAND

Aug 22, 2008 | Computers & Internet

1 Answer

Excel formula


the formulae for the surface area of round disc is
pi * r * r
where pi=3.14 and r =radious of disc= diametre/2 

for this 1st u have 2 calculate the radious of the disc that is
180/2=90mm
then convert it to  m by dividing 1000 ,so radious=90/1000=0.09 m
finally calculate the area= 3.14 * 0.09 * 0.09=0.025434 sq metres
for 38 disc multiply 38 with the above area that is
0.025434 * 38=0.966492 sq metres

in excel 2007  u can do it more easily by going 2 formulas tab.
Under formla tab click on engineering formula and  then convert
here u can convert from mm to m
after converting use the above formula i just told u.

Feb 27, 2008 | Computers & Internet

1 Answer

Getting the excel formula


Suppose the value for $ is stored in cell A3. Your formula would look like this: =(A3+A3*0.25)*1.5

The equals sign at the beginning of the formula is necessary. And if you want the result to be formatted as currency, you can do so by right-clicking the cell or column, format cell, number tab, choose currency.

Nov 15, 2007 | Computers & Internet

Not finding what you are looking for?
Microsoft Excel for PC Logo

1,019 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

micky dee

Level 3 Expert

2638 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18334 Answers

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...