currency format syntax problem with formula?

[snsyg]

hi all,

i am trying to combine a dollar amount with the written amount and th
format of the resulting value in not correct.

from responses to one of my previous postings, i got a macro for
function to convert the numbers into words and that works great. th
function is named "SpellNumber"

i have a cell name "Contract_Amount" which receives a user input valu
from a user form. it is formatted to currency with two decima
places.

i have another cell in which i have the formula =Contract_Amount. thi
cell is also formatted to currency with two decimal places. thi
formula produces the format of the amount as i want it, for example
$23,456.00.

the problem i have is when i try to combine the number with the writte
amount. the formula i am using for that is: =Contract_Amount&
"&SpellNumber(Contract_Amount) the result of this formula, "2345
Twenty Three Thousand Four Hundred Fifty Six Dollars and No Cents, i
correct, with the exception of the number format...i loose the currenc
formatting, including the comma.

is there some syntax i can add to the formula to make the number forma
correctly?

many thanks for your help.

kare

 

[Frank Kabel]

Hi
try
=TEXT(Contract_Amount,"$0,00") &" "&SpellNumber(Contract_Amount) the
result of this formula, "23456

change the format to your needs

 

[Niek Otten]

Hi Karen,

=TEXT(Contract_Amount,"#,##0.00")&" "&SpellNumber(Contract_Amount)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[snsyg]

thanks to both of you!

both solutions worked great, with a minor adjustment to each.

Niek, to get your formula to produce the $ at the beginning i di
this:
="$"&TEXT(Contract_Amount,"#,##0.00")&" "&SpellNumber(Contract_Amount)

Frank, to get your formula to produce the decimal places, i did this:
=TEXT(Contract_Amount,"$0,00.00")&" "&SpellNumber(Contract_Amount)

i'm sure both of you are were aware of this syntax, but i thought i'
share these adjustments in case anybody else is using this post as
reference for doing some thing similar.

your help is very much appreciated!

kare