Concatenation / Format $ / Split Lines
Posted by Donna on August 07, 2001 12:44 PM
I would like to be able to take a column that I have concatenated with a description and two units with prices after and I would like to be able to print the description on one line and the units/prices on the next line. My other problem is that the prices will not format correctly (for instance $1.80 comes
out $1.8 with no zero).
I will try to show an example: but, right now I can get it all in one line but the zeros don't come out right
Abington $9.9/sqyd $1.1 /sqft
This is how I would like to format it for printing on lables:
Carpet Name (I would like this centered???)
$ 2.00 / sqft
$18.00 / sqyd
If possible, could you email me an answer? I don't know if I'll be able to get back here. Anyways, thanks for any help.
Posted by Donna on August 07, 2001 1:01 PM
OK - Figured out the split lines, now just need to
know how to get the zeros to come out?
Posted by David on August 07, 2001 1:21 PM
Donna,
I *think* it's in the way you formatted the text in the concatenation (assumiing that is the way you are doing it) use this: $#,###.#0 to force the format to use the second decimal place..
Hope this helps.
David
Posted by Donna on August 07, 2001 1:35 PM
David
=A3&""&C3&"
"&"$"&E3&" / sqyd " & " $"&F3&" / sqft"
This is what I have entered into the cell that is
to be concatenated. It will print out $19.19 but not $19.10. The $# thing didn't work, unless I am not doing it right, which very well could be because I'm not that familiar with Excel.
The above will print out like this:
Abington
$9.9 / sqyd
$1.1 / sqft
should be $9.90 / sqyd and $1.10 / sqft
Posted by Richard S on August 08, 2001 12:32 AM
Donna,
I think the problem lies in your concatenation. By using "&" to join the cells, Excel converts the value to text, and drops of the zero at the end. If you convert the value in the cell to text first, using two decimal places, it should work.
ie if you have $9.90 as a value in cell A1,
=TEXT(A1,"$0.00") will convert the value to text in the format you want, then the concatenation should work. Let me know if it does, as I'm not real sure myself.
I'd also like to know how you solved the first part of your problem with the split lines.
Richard
David =A3&""&C3&" "&"$"&E3&" / sqyd " & " $"&F3&" / sqft" This is what I have entered into the cell that is to be concatenated. It will print out $19.19 but not $19.10. The $# thing didn't work, unless I am not doing it right, which very well could be because I'm not that familiar with Excel. The above will print out like this: Abington $9.9 / sqyd $1.1 / sqft should be $9.90 / sqyd and $1.10 / sqft
