Formula that contains currency not returning the results as currency

I have a spreadsheet that I am combining about 10 columsn into one utilizing the concatenate formula. One of my cells is a currency cell, but when the formula is run it returns XX.XX instead of showing it as currency $xx.xx. What can I do to have it return the cell exactly how it shows in currency? I know that since excel uses $ in formula, that could be the problem. TIA!
Trish

Convert the column that contains currency. If it's USD, (assuming the currency is in Column A), you can use "=Dollar(A1)". Otherwise, you can format the cells using "=text(A1,"$##.##)"

I am not following. I have 13 columns all together, column 14 puts all of those columns together. column L is currency ie. $30.10, but in column 14 when is shows everything in one column the column that is L shows as 30.10 instead of $30.10. How do I get that number in column L to show as currency $30.10 in column 14. TIA

I did, but it didn't change the way it looked in column 14 where it summarizes everything, it still shows 30.10 instead of $30.10. Do I have to add something in column 14 to make is show the $?

This is the Key

Originally Posted by Triscia

I have a spreadsheet that I am combining about 10 columsn into one utilizing the concatenate formula

Concatenate returns a TEXT string.
Even if it's result looks like a number, Excel still considers it a Text String.
Therfor, formatting the cell as currency (or any other number format) will have no effect on it..because it's not a number.

You could probably just add +0 to the end of the concatenate function to convert it to a real number.
=CONCATENATE(...,...,...)+0

That didn't work at all, once I added the +0 to the end I got a return #Value! error.

What's the actual formula? and it's resulting value?

Here is the sample. I hope that I did it correctly.

You just need to concatenate a "$" into your formula before the reference to Column L. "=concatenate(J1,K1,"$",L1)"

OK, that's different..

The number is just one portion of the overall string created by Concatenate.
but that number is being drawn from B2 which has been formatted as currency.

You'll have to use the TEXT function on that portion of the concatenate function to apply the same format to that number.

Try
=CONCATENATE(A1&":",A2&CHAR(10),B1&": ",TEXT(B2,"[$USD] #,##0.00")," ",C2&CHAR(10),D1&": ",D2)

Awesome!!!!!! That worked, I tried it before, but I didn't put the comma after it. Do you know why it would only read it as $30.1 instead of the $30.10 that shows in the cell?

The last zero you see in Column L is just like the dollar sign. It's not really in the cell, it's just a result of formatting. See Jonmo1's previous post if you'd like to format column L to actually contain both decimal places.

You're welcome.