Need to filter/substitute carriage returns in calc

[the shepster]

I have three fields in a table, letterText, letterTitle and cLetterTitle. letterText is the body of a written letter. letterTitle is a single line description of the letter. cLetterTitle is a calculation that is equal to letterTitle unless letterTitle is blank. If letterTitle is blank, cLetterTitle is the first 50 characters of letterText. The problem is with carriage returns which I want to filter out. If the first line is "Hi", then nothing else is displayed in cLetterTitle because they have been 'carriage returned' to the next line which is out of view.

Here is the solution I would like:

If ( IsEmpty ( Letter Name ) ; Substitute ( Left ( Letter Text; 50 ) ; [ "r" ; " " ] ) ; Letter Name )

The only problem is putting a carriage return into the Substitute() function ("r" above). I can't get anything to work. I have tried cutting and pasting various characters, including the backwards P paragraph mark. Nothing works.

If ( IsEmpty ( Letter Name ) ; Filter ( Left ( Letter Text; 50 ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 ,.'-!?") ; Letter Name )

The above calculation is more successful, but still has problems. Carriage returns are filtered, but that makes the two lines run together with no space between. Also, I can't get quotation marks into the filter. Escaping them with a backslash doesn't work.

Any suggestions?

[-Queue-]

If ( IsEmpty(Letter Name); Left( Substitute( Letter Text; "

[the shepster]

Beauty! That is the solution. I tried it before, but I escaped the character. That is, "

[Vaughan]

On a mac it is Option-G I think... I'm on a PC now so I cannot check.

It's Option-something, just test them all till you find the right one.

[Lee Smith]

Option 7 on a Mac

[Vaughan]

Thanks Lee.