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

I want to buy a new graphics card (dedicated-game) not more than Rs 3000 (Indian Currency). My computer's configuration is : Mother Board: P5N73 AM Processor: Intel Dual core CPU E5200 @2.50 GHZ Ram:...


http://www.newegg.com/Product/Product.aspx?Item=N82E16814162053

This product right here will fit into your motherboard's PCIe x 16 expansion slot.
I converted the currency and I believe it will cost you less than Rs 3000; however, I am unsure of where you could buy this locally.

May 03, 2011 | Video Game Consoles & Games

1 Answer

I recently acquired a new laptop with Windows 7 (previously I had windows XP) but my arcsoft Photoimpression software does not work properly - saying there is a problem. Having read your homepage it...


Use GOOGLE to search for "currency converter" and convert 100 dollars to pounds sterling to get a conversion rate.

Note that when you place an online order, and give your credit-card information, the amount will be converted by your credit-card company into your local currency. It should be within 2% of what that "currency converter" web-page has generated.

Nov 24, 2010 | Arcsoft Computers & Internet

1 Answer

Do you have a formula in Microsoft Excel that a value can convert to a word...(Ex. 15,000 then it will convert to in words fifteen Thousand..)thanks. need immediate reply.


Hi enelrah_mel0,

You can create a function called SpellNumber using the Visual Basic Editor in the Microsoft Excel to spell the number.

  1. Please Open Microsoft Excel. Then press Alt + F11 to open the Visual Basic Editor.
  2. Then, click "Insert" option above the Visual Basic Editor window and select "Module".
  3. Then, in the Book1 Module1 (Code) window, paste the below code there. (pasted in the next post separately for your convenience)
  4. Then, press Alt+Q to close the Visual Basic Editor window.
  5. After that, please type the formula " =SpellNumber( * )" in the Excel Sheet and press the Enter Key.
  6. Then, save the Excel Sheet.
eg: =SpellNumber(A1)

Where A1 is the first cell in the excel sheet.


Good Luck!

Please post back the result and let me know if you require further assistance.

Thanks for using Fixya.

Oct 29, 2010 | IBM ThinkPad T30 2366 Notebook

2 Answers

I NEED MS EXCEL CONVERT FORMULA FOR NUMBER TO CURRENCY


Here is a very popular bit of code from Microsoft that will convert any currency amount in a cell to English words. All code and text from below here is the work of Microsoft.

Summary
This article shows you how to create a sample, user-defined function named ConvertCurrencyToEnglish() to convert a numeric value to an English word representation. For example, the function will return the following words for the number 1234.56: One Thousand Two Hundred Thirty Four Dollars And Fifty Six Cents

The Function Wizard can also be used to enter a custom function in a worksheet. To use the Function Wizard, follow these steps:

1. Click the Function Wizard button, and select User Defined under Function Category.
2. Select ConvertCurrencyToEnglish, and enter your number or cell reference.
3. Click Finish

To Create the Sample Functions

1. Insert a module sheet into a workbook. To do this in Microsoft Excel 97 or Microsoft Excel 98, point to Macro on the Tools menu, and then click Visual Basic Editor. In the Visual Basic Editor, click Module on the Insert menu. In Microsoft Excel 5.0 or 7.0, point to Macro on the Insert menu and click Module.

2. Type the following code into the module sheet.
Function ConvertCurrencyToEnglish (ByVal MyNumber)

Dim Temp

Dim Dollars, Cents

Dim DecimalPlace, Count



ReDim Place(9) As String

Place(2) = " Thousand "

Place(3) = " Million "

Place(4) = " Billion "

Place(5) = " Trillion "



' Convert MyNumber to a string, trimming extra spaces.

MyNumber = Trim(Str(MyNumber))



' Find decimal place.

DecimalPlace = InStr(MyNumber, ".")



' If we find decimal place...

If DecimalPlace > 0 Then

' Convert cents

Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)

Cents = ConvertTens(Temp)



' Strip off cents from remainder to convert.

MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

End If



Count = 1

Do While MyNumber <> ""

' Convert last 3 digits of MyNumber to English dollars.

Temp = ConvertHundreds(Right(MyNumber, 3))

If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars

If Len(MyNumber) > 3 Then

' Remove last 3 converted digits from MyNumber.

MyNumber = Left(MyNumber, Len(MyNumber) - 3)

Else

MyNumber = ""

End If

Count = Count + 1

Loop



' Clean up dollars.

Select Case Dollars

Case ""

Dollars = "No Dollars"

Case "One"

Dollars = "One Dollar"

Case Else

Dollars = Dollars & " Dollars"

End Select



' Clean up cents.

Select Case Cents

Case ""

Cents = " And No Cents"

Case "One"

Cents = " And One Cent"

Case Else

Cents = " And " & Cents & " Cents"

End Select



ConvertCurrencyToEnglish = Dollars & Cents

End Function







Private Function ConvertHundreds (ByVal MyNumber)

Dim Result As String



' Exit if there is nothing to convert.

If Val(MyNumber) = 0 Then Exit Function



' Append leading zeros to number.

MyNumber = Right("000" & MyNumber, 3)



' Do we have a hundreds place digit to convert?

If Left(MyNumber, 1) <> "0" Then

Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "

End If



' Do we have a tens place digit to convert?

If Mid(MyNumber, 2, 1) <> "0" Then

Result = Result & ConvertTens(Mid(MyNumber, 2))

Else

' If not, then convert the ones place digit.

Result = Result & ConvertDigit(Mid(MyNumber, 3))

End If



ConvertHundreds = Trim(Result)

End Function







Private Function ConvertTens (ByVal MyTens)

Dim Result As String



' Is value between 10 and 19?

If Val(Left(MyTens, 1)) = 1 Then

Select Case Val(MyTens)

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

' .. otherwise it's between 20 and 99.

Select Case Val(Left(MyTens, 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



' Convert ones place digit.

Result = Result & ConvertDigit(Right(MyTens, 1))

End If



ConvertTens = Result

End Function







Private Function ConvertDigit (ByVal MyDigit)

Select Case Val(MyDigit)

Case 1: ConvertDigit = "One"

Case 2: ConvertDigit = "Two"

Case 3: ConvertDigit = "Three"

Case 4: ConvertDigit = "Four"

Case 5: ConvertDigit = "Five"

Case 6: ConvertDigit = "Six"

Case 7: ConvertDigit = "Seven"

Case 8: ConvertDigit = "Eight"

Case 9: ConvertDigit = "Nine"

Case Else: ConvertDigit = ""

End Select

End Function

Apr 15, 2010 | Broderbund Learn MS Windows XP and Excel...

1 Answer

Need Formula


TEXT CASE
Convert to UPPER, Proper or lower
Auto Convert to UPPER, Proper or lower
Stop VBA Being Case Sensitive

SELECT CASE VBA
Select Case Statement. Great alternative to Else If Statements

CONVERSION
VBA: CBool(),CByt(),CCur(),CDate(),CDbl()Double,CDec(),CInt(),CLng(),CSng(),CStr(),CVar().
Example
dDate=cDate("25-Jan-07")
Convert Numbers to Words
Convert Numbers to Dollar Amount in Words
Spreadsheet Converter. Versions: HTML/JavaScript,Excel ASP.Net and Excel Java/JSP. $
SQL Database Migration $
Converters. Database Converters, Spreadsheet Converters, Text Converters, E-mail Conversion, Document Conversion, PDF Conversion and more! $
CSV Converter $

IMPORTING
Import SQL Into Excel-SQL Tester $
Import Excel Into Access $

CONDITIONAL FORMATTING
Conditional Formatting
More Than 3 Conditional Formats
Conditional Format Fonts for greater than 3

CUSTOM FORMATS
Custom Formats
Excel see a cells format as having four Sections. These are, from left to right: Positives;Negatives;Zeros;Text.

To hide zeros cell-by-cell use a Custom Number Format like 0.00;-0.00; where 0.00 is desired format for non zeros. Note the use of -0.00 for negatives.

To hide zeros on the Workbook level go to Tools>Options>View - Zero Values.

Dec 15, 2008 | Microsoft Windows XP Home Edition

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


Hello,

Do you mean convert to the current currency rate? like you have 1$ in the cell and you want it to show .70 £

or do you mean to change the symbol? from $ to £

let me know,

Chester

Jan 11, 2008 | Computers & Internet

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

1,024 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

micky dee

Level 3 Expert

2644 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18381 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...