Convert dollar amount to text

[Johncie]

Is there a formula/combination of formulas to convert a dollar amount t
text?

i.e. $1234.56 = One Thousand Two Hundred Thirty-Four Dollars an
Fifty-Six Cents

Thanks!

Johnci

 

[Frank Kabel]

Hi
there're some macros/procedures to convert numbers to text. e.g.
MS Knowledge Base:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;213360
http://support.microsoft.com/default.aspx?scid=KB;EN-US;140704&

Free Addin MOREFUNC-DLL which includes a UDF for this task (for various
languages):
http://longre.free.fr/english/

or have a look at
http://www.bygsoftware.com/examples/zipfiles/num2wrds.zip
It's in the "Accountants" section on page:
http://www.bygsoftware.com/examples/examples.htm
It contains two methods

 

[Ron Rosenfeld]

Free Addin MOREFUNC-DLL which includes a UDF for this task (for various
languages):
http://longre.free.fr/english/

Frank,

I just downloaded the 2003 version of morefunc.xll.

Although his other functions seem to work fine, when I try to use NBTEXT, Excel
freezes (Excel is not responding message shows up). Any thoughts?

Windows XP Pro; Excel 2002; all critical updates are installed.


--ron

 

[Frank Kabel]

Frank,

I just downloaded the 2003 version of morefunc.xll.

Although his other functions seem to work fine, when I try to use
NBTEXT, Excel freezes (Excel is not responding message shows up).
Any thoughts?

Hi Ron
works for me (Excel 2003, German) But I've heard that this function
sometimes causes problems. Maybe send Laurent an email

Frank

 

[Peo Sjoblom]

Ron,

Laurent Longre has acknowledged it's a bug , from what I understand he is
working on an
upgrade for the whole morefunc

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Ron Rosenfeld]

Ron,

Laurent Longre has acknowledged it's a bug , from what I understand he is
working on an
upgrade for the whole morefunc

OK -- thanks. I did send him an email. Hopefully he will get it fixed RSN.

I also tried it using XP Pro's "diagnostic startup" with the same results. So
I doubt the problem is due to anything else on my system.


--ron

 

[Ron Rosenfeld]

Hi Ron
works for me (Excel 2003, German) But I've heard that this function
sometimes causes problems. Maybe send Laurent an email

Interesting that it works for you. I did send him an email, but just today.

It also did not work for me using XP's "diagnostic startup" so I don't think
it's due to anything else on my system.

Hmmm


--ron

 

[dave]

If you'd rather not mess with macros, you can use this...

create a numbers table as such...
1 one ten
2 two twenty
3 three thirty
4 four fourty
5 five fifty
6 six sixty
7 seven seventy
8 eight eighty
9 nine ninety
and name the range "numbers"

then enter your number in b2 and use this formula
alittle long but simple)

=VLOOKUP(FLOOR($B$2,1000)/1000,numbers,2,0)
&" "&"thousand"&" "&VLOOKUP(FLOOR($B$2-FLOOR
($B$2,1000),100)/100,numbers,2,0)&" "&"hundred"&" "&VLOOKUP
(FLOOR($B$2-FLOOR($B$2,100),10)/10,numbers,3,0)&" "&VLOOKUP
(FLOOR($B$2-FLOOR($B$2,10),1),numbers,2,0)&" "&"dollars"&"
and "&VLOOKUP(TRUNC(($B$2-FLOOR($B$2,1)),1)*10,numbers,3,0)
&" "&VLOOKUP(ROUND((($B$2-FLOOR($B$2,1))-TRUNC(($B$2-FLOOR
($B$2,1)),1)),2)*100,numbers,2,1)&" "&"cents"


you might need to change the weighting in the cell
reference, but it works.

Btw, the reason I used a range lookup of 1(true) in the
cents case is this avoids hitting an excel bug
which "dangles" numbers like ten decimal places out so the
vlookup doesn't match exactly.

hth,
Dave