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.

## 2 Responses to

Number to text converter: most useless spreadsheet ever