Solved: Problem With A Text Box On A Chart

[Cyberdude]

I created a text box on a chart. I want to force a line feed in the text in 3 places so that it looks something like this:

Elapsed time since Feb 13, 06:
…....454 days
…...14.9 months
….....1.2 years

My formula that creates this text is:
=”Elapsed time since Feb 13, 06: "&CHAR(13)&
TEXT(TODAY() - DATEVALUE("2/13/2006"),"0"" days""")&CHAR(13)&
TEXT((TODAY() - DATEVALUE("2/13/2006"))/(365 / 12),"0.0 ""months""")&CHAR(13)&
TEXT((TODAY() - DATEVALUE("2/13/2006")) / 365, "0.0 ""years""") My problem is that the CHAR(13) function is being treated as a text character rather than a line feed, so that everything is displayed as though I had written a single line instead of 4 lines. How does a gent such as myself introduce a line feed into the text line in a text box?

I have a secondary problem. Assumming that I figure out how to do the line feed, how do I put a series of leading blanks on each "line fed" line so that the text becomes more centered? The text box mechanism tends to throw away all leading blanks on each line.

Edited 14-May-07 by geekgirlau. Reason: insert line breaks

[geekgirlau]

="Elapsed time since Feb 13, 06:"&CHAR(10)&REPT(CHAR(32),7)&
TEXT(TODAY() - DATEVALUE("2/13/2006"),"0")&" days"&CHAR(10)&REPT(CHAR(32),7)&
TEXT((TODAY() - DATEVALUE("2/13/2006"))/(365 / 12),"0.0")&" months"&
CHAR(10)&REPT(CHAR(32),7)&TEXT((TODAY() -
DATEVALUE("2/13/2006")) / 365, "0.0")&" years"

You'll notice that the words "days", "months" and "years" have to be outside of the TEXT function, and I've used character 10 (line feed) instead of character 13 (carriage return).

[Andy Pope]

If you are using a textbox can you no tjust set the alignment to Centered and do away with the padding spaces?

[Cyberdude]

Hey, geekgirlau, sorry for the reply delay. You obviously spent some time working this out, and I DO appreciate it.

With a little tweaking, I was able to accomplish what I wanted to display using your recommended code.

Thanks a lot. I can use this in quite a number of places now. :friends:

Sid

[Cyberdude]

Hey, geekgirlau, one more question regarding your code. You used REPT(CHAR(32), 7) in three places. What is the significance of the "7". I know it's the number of times you want to repeat a blank, but why 7?? Is that your guess of what it took to perform the spacing I wanted, or does it have some other significance??

Jes wondering,
Sid

[geekgirlau]

The number 7 has a deep symbolic meaning, and gives a clue to the true significance of life and our place in the universe ... :clever:

Nah, just plucked it out of thin air - seemed to give a nice amount of space on the left.