Formula for Converting Dollar Amount to Words

The article on Microsoft.com "How to convert a numeric value into English words in Excel" provides some VBA code for converting a dollar value into words, like you would see on checks or receipts.

I try to avoid using VBA in my spreadsheets whenever possible, and sometimes that leads to some very very long formulas. This one took some doing, but it IS possible to use a formula to convert dollar amounts less than 1 million into words without VBA. I wasn't able to extend this to numbers larger than a million because the formula exceeded the maximum length.

I use the formula in my new Receipt Template spreadsheet, where I highly doubt anyone would need to write a receipt for a value over a million.

FIRST, you need to define two named ranges:

THEN, you can use one of the following formulas, where cell B8 contains the numeric value. Important: The value in B8 must already be rounded to the nearest cent (0.01). A value like 999.995 will mess up the formula.

Style 1: ... and ../100

Example: B8=123,456.78
Result: "One Hundred Twenty-Three Thousand Four Hundred Fifty-Six and 78/100"

Style 2: ... Dollars and ... Cents

Example: B8=123,456.78
Result: "One Hundred Twenty-Three Thousand Four Hundred Fifty-Six Dollars and Seventy-Eight Cents"

You could create a formula in Excel that would handle much larger numbers if you split the formula into multiple cells. I suspect that the formula could be shortened further with some more effort, but I just don't care enough to do that.

xclmedia said...

Outsource Excel said...

Creative formula. I love that you figured out how to do it without using VBA. I'll definitely be using this idea.

Anonymous said...

There appears to be a glitch in the formula, wherein certain numbers that end in 21, 31, 41, 51, 61, 71, 81, or 91; appears in the formula result as twenty, thirty, forty, fifty, sixty, seventy, eighty and ninety (missing the “one”).

By example: 571.00

Appears as: Five Hundred Seventy and NO/100 Dollars

My Excel version is 2007 on a PC…