+ Reply to Thread
Results 1 to 2 of 2

VBA Coding Method: Simple Number to Words using a Single Loop String Triplets (SLST)

  1. #1
    Registered User
    Join Date
    06-20-2019
    Location
    Qatar
    MS-Off Ver
    2019
    Posts
    33

    VBA Coding Method: Simple Number to Words using a Single Loop String Triplets (SLST)

    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:

    Name:  a1.PNG
Views: 168
Size:  13.0 KB


    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:


    Name:  a2.PNG
Views: 192
Size:  38.7 KB

    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.


  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: VBA Coding Method: Simple Number to Words using a Single Loop String Triplets (SLST)

    Thanks,

    Moved to Tips & Tricks section.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Very Simple Spell (Number to Words) 001 to 999 in VBA
    By MohsenAlyafei in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-06-2019, 11:50 AM
  2. Replies: 2
    Last Post: 01-05-2018, 12:31 AM
  3. Formula to separate string of text into single words
    By Nola111 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-29-2017, 06:53 PM
  4. Extract all words in text string, put them into a single column
    By StartRunMSPaint in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-25-2016, 06:35 PM
  5. [SOLVED] Spell Number VBA coding for Arabic words
    By arshad.99.ali in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-20-2016, 04:44 AM
  6. [SOLVED] Need to separate continuous capital words of a string within a single cell
    By anchuri_chaitanya in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2013, 02:56 AM
  7. [SOLVED] Coordinate triplets with point number
    By gith in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-12-2012, 10:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1