Number to text converter: most useless spreadsheet ever

I can’t remember why, but I set myself a personal challenge some time back to create a spreadsheet that could convert numbers to text.

Here it is. Type in a number between 1 and 999,999 in the red cell and it will write it out in words in the blue. English style, not American, so the ands are included.

When you break it down, there are only 29 words used in the numbers below a million: the digits (one through nine), the tens (ten, twenty, … , ninety), the extended teens (eleven, twelve, … , nineteen), together with and, hundred and thousand.

I wrote the text for each of the sets of numbers next to their numeric equivalents for the purposes of VLOOKUPs. I then I parsed the number entered by the user, also figuring out its length. And then, for each of the six digits, I used logic to determine what to display for that digit.

And here is the logic for that fourth digit from the right, the most complex of the bunch.

=IF(Working!A15>=4,IF(OR(Working!D15=0,Working!C15=1)," thousand "&IF(AND(Working!E15=0,Working!F15=0,Working!G15=0),"",IF(Working!E15=0," and","")),VLOOKUP(Working!D15,Working!$A$2:$B$10,2,0)&" thousand"&IF(AND(Working!E15=0,Working!F15=0,Working!G15=0),"",IF(Working!E15=0," and",""))),"")

Below is the logic in English.

• If the number's length is greater than or equal to four, then this digit comes into play. If it doesn't, then return a blank
• If the fourth digit from the right is a zero, then you just need the word thousand, because there's nothing else to count. 110,000, for example is spoken one hundred and ten thousand. The ten would have been picked up by the logic for the fifth digit from the right, so we just need to say thousand before we move to the last three digits
• Now if the fifth digit from the right is a one, then logic therein figures out which extended teen it is, and this is derived within the logic representing that digit. So as with the bullet above, in this instance the only thing to say is the word thousand
• If the three subsequent digits are all zero, then there's no need to say and after the thousand. Otherwise, there is (in British English, at least)
• Now we look up the digit itself to bring back its text equivalent (2 becomes two, for example)
• If the third digit from the right is a zero and the number isn't an exact thousand, then you'll need an and. 1,023, for example, is one thousand and twenty-three

All in all, it took me about an hour. It was a bit of fun. And it seems to work. Give it a go.

• Tweet