This is a part 01 of multi part series of my excel sheet as a digital chequebook & cheque generator, & using excel to convert numbers to words.
So, I am using Microsoft Excel from ages, for various things at work as well as personal reasons, to track money, health, things, tasks, printing stationary & million other things. Initially I used Excel only as to print blank forms, just for its visual part. Over timeI learned formulas & started using various colums, SUMs, IFS, VLOOKUP, SUMPRODUCT & other such things.
In Kenya around 2008 I got a task from my Manager that when he writes Cheques, he hass to print a A4 Sheet with details of payment, receiever, purpose, date & amount. He asked me to Google to find out how to convert Numbers (123) to Words (One Hunderd Twenty Three). Easy, I gooogled, found a VBA code snippet, added to his worksheet & viola, it worked. He was happy, although I noticed that now that sheet prompts for Macro-Permissions at every run/open.
In 2011, he emailed me, long after I left that job about a year ago, asking me that Cheque excel sheet again with same capability, as numbers to words, I attached it as email attachment & he struggled to run it, I had to use Team Viewer ot make it work. There onwards I started thinking about same thing in a non-macro sheet, named N2W (Numbers to Words :-) )
Cell A1 is 1,234,567,891,234. The sheet splits each digit into one row. Excel does not have loops in non-VBA environment, so I used rows as each itereation of loop. I used 13 rows, from One to Trillion, each row gets one digit, the first row looks for 13th digit from right, if that digit is not there, it stays empty. The second row gets 12th digit if available.
A seperate VLOOKUP Table array, two columns & 32 rows, has digits in Column 01, from 1 to 20, & then 30, 40, 50, 60, 70, 80, 90, 100, 1000, 1000000, Billion, Million & Trillion with their corrosponding words in column two; i.e. One for 1, Nine for 9, Seventeen for 17 & all such. These 32 words versus numbers make up all the digits from One to Trillion & everything in between.
Column A lists words Trillion, Hundred Billion, Ten Billion, Billion, Hundred Million, Ten Million, Million, hundred Thousand, Ten Thousand, Thousand, Hundred, Ten, One as Row Labels, each at next cell of column.
Sheet could have been made complicated with multiple formulas in each cell, but I was novice, so I used each column for one single step at a time. Now after using the sheet for 8+ years, I could think many ways of making it smaller & easier, but if ain’t broke, don’t change it.
B2 = Original Value 1,234,567,891,234. The expected output value we need is One Trillion Two Hundred Thirty Four Billion Five Hundred Sixty Seven Million Eight Hundred Ninety One Thousand Two Hundred Thirty Four & 0/100 Cents Only.
C2 = 10^12 for Trillion, resulting in 1,000,000,000,000.
D2 = MOD(B2, C2) resulting in stripping the 13th digit from right, which is same as 1st from right, so 1. D2 = 234,567,891,234
E2 = IF(D2=B2, “”, D2) Here it checks if after stripping there is no change, keep it empty. Reason is if it is not Trillion, then no need of going for Trillionth word.
F7 = IF(E6=””, “”, B6-E6), getting the first digit followed by zeros, same as rest of the digits. 1,000,000,000,000
-Work In Progress