Kwert-soft.ru

IT Софт для ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Amount in words

Convert numbers into words

Excel doesn’t have a default function that displays numbers as English words in a worksheet, but you can add this capability by pasting the following SpellNumber function code into a VBA (Visual Basic for Applications) module. This function lets you convert dollar and cent amounts to words with a formula, so 22.50 would read as Twenty-Two Dollars and Fifty Cents. This can be very useful if you’re using Excel as a template to print checks.

If you want to convert numeric values to text format without displaying them as words, use the TEXT function instead.

Note: Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the VBA programming language, and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality, or construct procedures to meet your specific requirements.

Create the SpellNumber function to convert numbers to words

Use the keyboard shortcut, Alt + F11 to open the Visual Basic Editor (VBE).

Note: You can also access the Visual Basic Editor by showing the Developer tab in your ribbon.

Click the Insert tab, and click Module.

Copy the following lines of code.

Note: Known as a User Defined Function (UDF), this code automates the task of converting numbers to text throughout your worksheet.

Paste the lines of code into the Module1 (Code) box.

Press Alt + Q to return to Excel. The SpellNumber function is now ready to use.

Note: This function works only for the current workbook. To use this function in another workbook, you must repeat the steps to copy and paste the code in that workbook.

Use the SpellNumber function in indiv >Type the formula =SpellNumber( A1) into the cell where you want to display a written number, where A1 is the cell containing the number you want to convert. You can also manually type the value like =SpellNumber(22.50).

Press Enter to confirm the formula.

Save your SpellNumber function workbook

Excel cannot save a workbook with macro functions in the standard macro-free workbook format (.xlsx). If you click File > Save. A VB project dialog box opens. Click No.

You can save your file as an Excel Macro-Enabled Workbook (.xlsm) to keep your file in its current format.

Click File > Save As.

Click the Save as type drop-down menu, and select Excel Macro-Enabled Workbook.

Displaying Money Amounts in Words

Last modified: September 2, 2019

Читать еще:  Самоучитель работы в word

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

In the 9 years of running Baeldung, I’ve never, ever done a «sale».
But. we’ve also not been through anything like this pandemic either.
And, if making my courses more affordable for a while is going to help a company stay in business, or a developer land a new job, make rent or be able to provide for their family — then it’s well worth doing.
Effective immediately, all Baeldung courses are 33% off their normal prices!
You’ll find all three courses in the menu, above, or here.

1. Overview

In this tutorial, we’ll see how we can convert a monetary amount into words-representation in Java.

We’ll also see how a custom implementation could look like, via an external library – Tradukisto.

2. Implementation

Let’s first start with our own implementation. The first step is to declare two String arrays with the following elements:

When we receive an input, we’ll need to handle the invalid values (zero and negative values). Once a valid input is received, we can extract the number of dollars and cents into variables:

If the number given is less than 20, then we’ll get the appropriate ones’ element from the array based on the index:

We’ll use a similar approach for numbers less than 100, but now we have to use tens array as well:

We do this similarly for numbers that are less than one thousand.

Next, we use recursive calls to deal with numbers that are less than one million, as shown below:

The same approach is used for numbers that are less than one billion, and so on.

Here is the main method that can be called to do this conversion:

Let’s test our code to make sure it works:

Let’s also test some edge cases, and make sure we have covered them as well:

3. Using a Library

Now that we’ve implemented our own algorithm, let’s do this conversion by using an existing library.

Tradukisto is a library for Java 8+, which can help us convert numbers to their word representations. First, we need to import it into our project (the latest version of this library can be found here):

We can now use MoneyConverters‘s asWords() method to do this conversion:

Let’s test this method with a simple test case:

We could also use the ICU4J library to do this, but it’s a large one and comes with many other features that are out of the scope of this article.

However, have a look at it if Unicode and globalization support is needed.

4. Conclusion

In this quick article, we saw two approaches on how to do the conversion of a sum of money into words.

Читать еще:  Word сбой активации продукта как исправить

The code for all the examples explained here, and much more can be found over on GitHub.

Amount in words

Welcome to Oracle Communities

Register and Participate in Oracle’s online communities. Learn from thousand of experts, get answers to your questions and share knowledge with peers.

15. Re: Convert amount in words to amount in numbers

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
16. Re: Convert amount in words to amount in numbers

Very cool. For the original poster, though, MODEL is not available in Oracle 8 either.

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
17. Re: Convert amount in words to amount in numbers

I had a few minutes to kill, so I reversed the tom kyte code. It should work with anything up to 40 digits, but I didn’t really test it that much:
Message was edited by:
shoblock

add the next 8 sets after ‘duodecillion’. now works up to 66 digit numbers.

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
18. Re: Convert amount in words to amount in numbers

That work fine on the big number given earlier : You just may want to add a test on 0 at the beginning.

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
19. Re: Convert amount in words to amount in numbers

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
20. Re: Convert amount in words to amount in numbers

Here are examples. Thanks

Two Thousand Eight Hundred Thirty-Eight Dollars and Thirty-Seven Cents ==> 2,838.37
Six Thousand Three Hundred Twelve Dollars and Seventy-Four Cents ==> 6,312.74
One Hundred Dollars and Zero Cents ==> 100.00
Three Hundred Thirty Dollars and Forty-Two Cents ==> 330.40
Forty-Nine Dollars and Thirteen Cents ==> 49.13

Tell me if the above would work . I should have gave examples from before and I should have said that it was money.

Sorry. Eagerly awaiting reply.

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
21. Re: Convert amount in words to amount in numbers

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
22. Re: Convert amount in words to amount in numbers

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
23. Re: Convert amount in words to amount in numbers

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
24. Re: Convert amount in words to amount in numbers

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
25. Re: Convert amount in words to amount in numbers

That shouldn’t be too hard now you have the magic function.

1. Just add the following line just after the begin to the shoblock’s function : 2. Create a new function and call this last one : Note that for 330.4 you’re wrong, this should be 330.42 as the function returned.

Читать еще:  Основы работы в ms word

3. Thank to shoblock 😉

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
26. Re: Convert amount in words to amount in numbers

OK, here is a query that should do it, but it will get slower and slower the larger the number you need to be able to display. Also, for the practical application, you would not want to use all_objects, but rather a real table with lots of rows since all_objects is slow to access. Still, if you want anything up to a million in under a second in Oracle 8, here’s my attempt:
Greg Pike
http://www.singlequery.com

Message was edited by:
gfpike

I changed this query to support ZERO dollars as well as ZERO cents and also added number formatting.

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
27. Re: Convert amount in words to amount in numbers

Could this not be done with a decode somehow? I was thinking along the lines of outputting the number 1 when the string ‘one’ is encountered, 2 when ‘two’ or ‘twe’ (for twenty), 3 if ‘thr’ or ‘thi’ (thirty) etc?

Would this be easier than trying to deal with words like ‘trillion’, ‘million’, ‘thousand’? – assume that the text-number is split with spaces, search through until you hit a specified string, output digit, carry on?

Would that be possible or am I completely on the wrong track here? Might be just one of those days…

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
28. Re: Convert amount in words to amount in numbers

  • Мне нравится Показать отметки «Мне нравится» (0) (0)
  • Действия
29. Re: Convert amount in words to amount in numbers

Could this not be done with a decode somehow? I was
thinking along the lines of outputting the number 1
when the string ‘one’ is encountered, 2 when ‘two’ or
‘twe’ (for twenty)
, 3 if ‘thr’ or ‘thi’ (thirty) etc?

I think it’s quite a wrong path.

e.g. twenty one = 21
twenty = 20
but if you always evaluate twenty as ‘2’ you’ll get wrong result.
And I think it’s just one of many.

Not in response to your post.
I was also trying resolving this problem by myself and that’s what I got (for 10g, didn’t test it a lot):
well, despite some drawbacks (they’re mentioned in the code), there some benefits:
— you can give as an input strings with ‘AND’, e.g. ‘one hundred and twenty’;
— you can use alphanumeric strings, e.g. ‘5 thousand 3 hundred and fifty’
— it counts scores;
— it counts lakhs and crores.

Message was edited by:
Volder
Got rid of one drawback:

Ссылка на основную публикацию
Adblock
detector