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.

## 4 comments:

Wow, thank your tips excel.

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

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…

Any advise -- my email address is agnnns@bullerinetworks.net

"21" and "21000" bug fixed: Changed [>1, "-"] to [>=1, "-"] and [>1000, "-"] to [>=1000, "-"]

Post a Comment