VBA Coding Method: Simple Number to Words using a Single Loop String Triplets (SLST)
I have reviewed various methods of converting a number to words (spelling numbers) in English.
I have tried to come-up with simple method using a Single Loop String Triplets (SLST) and thus avoid the use of excessive arithmetic number operations, switches, array manipulations, reversing or splitting strings/arrays, or function recursions.
The method is not limited to VBA and can be used in other programming languages as the structure and flow is simple to code. I have also done it in JavaScript.
The principle applied here is to follow the basic human reading logic of pronouncing and writing the number (in US English) from Left to Right using the standard US English (i.e. without an “and” after the hundred parts).
The function is made to work for whole unsigned numbers (Unsigned Integers). But may be called twice for whole and fractional parts after a number split at the decimal point, or after removing the “minus” sign.
Also currency and sub-currency words could be added easily if a whole/fractional split had been made.
It is not intended for the function to check for bad inputs, negative numbers, decimals, etc. as this could be left to a another higher function that will call this function, therefore the following are not accounted for simplicity:
- No checks for negative numbers.
- No checks for non-number (NaN) strings/data or bad inputs.
- No checks or conversion for exponential notations.
However, large numbers can be passed as a String if necessary.
The "Scle" Array may be increased by adding additional scales above “Decillion”.
It is also simple to add a Comma "," after each scale words (except last) as some would prefer that.
Here how it works with an example:
Example Number: 1223000789
One Billion Two Hundred Twenty-Three Million Seven Hundred Eighty-Nine.
1. Stringfy and convert to shortest triplets padded with zero using the following code:
Please Login or Register to view this content.
The above line is the core liner of the code as it converts and establishes the Triplets thus the principal method of the code.
The stringfied Number in Triplets is now: (2 zeros added to the LH):
001223000789
In the above example 4 Triples are created.
Graphically, the number Triplets is now:
In our example, no triplets exist for scales above Billions, so no Trillions or above number scales.
2. Get count of Triplets: in this case 4 Triplets (i.e. count 3 to 0), So that we can loop through the Triplets:
Please Login or Register to view this content.
3. Loop through the Triplets starting from the Most Significant Triplet (MST) (i.e. like you read the number) and while looping do the following for each Triplet:
(a) Convert each Triplet number to equivalent words (1 to 999) and then add the scale name after it (thousands, millions, billions, etc.).
(b) If a Triplet is empty (i.e. 000) then skip it.
(c) Join the new created Triplet words to the end of the previous one.
For info: Line 11 of the code ensures a hyphen is inserted for numbers between 21 to 99 in accordance with English numerals writing (i.e. Twenty-One, Fifty-Seven, etc.). You could delete that if it does not apply to you together with the associated variable declaration.
Graphical Example of the above:
Result:
One Billion Two Hundred Twenty-Three Million Seven Hundred Eighty-Nine.
As you can see the code is every simple, short, easy to understand and works perfectly without the need for complex coding.
You can test wit the following:
Please Login or Register to view this content.
or by a string call:
Please Login or Register to view this content.
I have found this to be the simplest method to understand and code.
Higher functions can be built around it for further processing.
I hope the above is helpful and if you have any suggestions for improvements, bug fixing, etc. I appreciate your feedback.
Thanks
Mohsen Alyafei
Please Login or Register to view this content.
Bookmarks