Format Formula

[enna49]

Hi

I have this formula and I want the answer to come back as TOTAL - $98,104.
I also want the text to wrap as it takes up too much space, that is why I
could not use Custom Format.

This is my formula ="TOTAL"&" - "&"$"&SUM(AA4:AA78)
This is my answer TOTAL - 98103.9666666667
This is what I would like to happen TOTAL
$98,104
I am using Excel 2007

Thanking you
Anne

 

[Sheeloo]

Use
="TOTAL"&" - "&"$"& Text(SUM(AA4:AA78),"0.00")

adjust 0.00 to the format you want.
you may want a space after $ or include that as part of format within TEXT
function.

 

[Max]

Format the formula cell to wrap text, then try something like this:
="TOTAL"&CHAR(10)&TEXT(SUM(AA4:AA78),"$#,##0")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Sheeloo]

Use
="TOTAL"&" - "&"$"& Text(SUM(AA4:AA78),"0.00")

adjust 0.00 to the format you want.
you may want a space after $ or include that as part of format within TEXT
function.

 

[Max]

Format the formula cell to wrap text, then try something like this:
="TOTAL"&CHAR(10)&TEXT(SUM(AA4:AA78),"$#,##0")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Dave Peterson]

You could try a custom format:

Rightclick on the cell.
Format Cells|Number Tab|Custom category
"Total"(ctrl-j)$#,##0

Don't type the (ctrl-j)
Instead hit and hold the control key while hitting the j key.

Then Rightclick on the cell.
Format Cells|Alignment tab
Check the "shrink to fit" box first
Then check Wrap text

You can autofit the columnwidth--then shrink it a little more (manually).

And you'll have to adjust the rowheight manually, too.

 

[Dave Peterson]

You could try a custom format:

Rightclick on the cell.
Format Cells|Number Tab|Custom category
"Total"(ctrl-j)$#,##0

Don't type the (ctrl-j)
Instead hit and hold the control key while hitting the j key.

Then Rightclick on the cell.
Format Cells|Alignment tab
Check the "shrink to fit" box first
Then check Wrap text

You can autofit the columnwidth--then shrink it a little more (manually).

And you'll have to adjust the rowheight manually, too.

 

[enna49]

Works perfectly thanks for the quick response. What is the CHAR(10) Thanks
Anne

 

[enna49]

Works perfectly thanks for the quick response. What is the CHAR(10) Thanks
Anne

 

[Max]

CHAR(10) is the new line char, equiv. to pressing Alt+ENTER when you break
for a new line within a cell. (the wrap text formatting is still required for
it to work)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Max]

CHAR(10) is the new line char, equiv. to pressing Alt+ENTER when you break
for a new line within a cell. (the wrap text formatting is still required for
it to work)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik