Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to convert number into word?

1

Dear Seniors,

In my excel sheet, amounts given as Rs40,000, Rs 30,000, etc. Please suggest formula to quickly convert the amounts to word format like "Rupees Forty Thousand Only".

PLEASE DO NOT SUGGEST VBA OR MACRO. (spellnumber not available).

Regards,

Akash Sharma

Answer
Discuss

Discussion

You may not want a macro, but it's the easiest way to do things here. Follow the Answer from Variatus and ask back here when/if you need help implementing that solution.
don (rep: 1989) Oct 17, '18 at 2:24 am
Add to Discussion

Answers

1
Selected Answer

I wrote a macro for this purpose in 2012. Below is a list of the words needed for the conversion (copied from that code)

    Const Ones As String = "zero one two three four five six seven eight nine"
    Const Teens As String = "teen eleven twelve thir four fif six seven eigh nine"
    Const Tens As String = "null ten twenty thirty fourty fifty sixty seventy eighty ninety"
    Const Powers As String = "hundred thousand million billion"
    Const SpellCurr As String = "dollar,only,and,cent"

To save you the counting, there are 38 of them which must be arranged in logical order. Excel doesn't have a built-in function to do that and to construct one using Excel's worksheet functions seems like a daunting task, even if one presumes that you don't need billions and perhaps not even cents.

A reasonable solution would be the use of a UDF (User Defined Function) which is entered like an Excel worksheet function but works with VBA in the background. SpellNumber, which you mention, is such a UDF which you could download from the Internet.

My macro is more elaborate. It could be converted to be called like a UDF. The whole macro is available here, just in case your need is greater than your aversion to macros.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login