replacing carriage returns within text with spaces

[julesw]

Hi,

I am exporting FMP data into another program which cannot handle any carriage returns. One of the fields has tons of carriage returns in it: some of them are at the end of the text, some in the middle of it, eg.

University of Blah

Department of XYZ

I have tried using the substitute function to replace all carriage returns with spaces, and it works for the ones at the end of the text but not those in the middle.

Can anyone help?? I have already tried the FMP experts email list, but no joy there so far. The only person to respond couldn't understand why returns in the middle of text should be any different to those at the end.

[QuinTech]

Hi Jules, What version of FMP are you using? I just tried this on my FM6 and it worked.

Just to check, you are using the symbol in your Substitute function, right?

Jerry

[julesw]

version 7. yes i am using that symbol.

[rmw]

Having two fields

TextWithCR of type text

and

TextWithoutCR of type calculation(text) with this calculation: Substitute ( TextWithCR ; "

[julesw]

ah would you believe it: i had the calculation result set to number instead of text. ok, i feel stupid now.

THANKS!

[julesw]

no i take it back - i definitely have the correct formula, set to text, and it isn't working!!! heeeelp!

[Mike D.]

By any chance, it the calculation field set to store the results. Make sure the "Do not store calculations" block is checked.

Mike

[RalphL]

Why? The calculation should work either stored or unstored. The calculation will not remove the carriage return at the end of a record when exported. The text file will have to edited to get rid of those.

[Mike D.]

OK, I just ran a test and the calculation storage does not make any difference. However, julesw is having a problem with carriage returns in the middle of the text not at the end. I would understand a carriage return at the end showing up in the exported file because that would be the record delimiter.

Just a thought - are we sure that the "carriage returns" in the middle of the text are in fact carriage returns and not something else like tabs, line feeds, etc. That would explain why the substitute calculation doesn't always seem to work.

Mike

[Mike D.]

If other "characters" are the culprit, then change the substitute calculation to include additional criteria like this example using a tab inside the double quotes (copied and pasted in from a text field). You can add more criteria to the substitute function by using the square brackets separated by a semicolon.

Substitute ( text block;[ "

[antimatter]

How are you exporting? via a text file? There are many ways to remove control characters (tabs, return, linefeed etc) from plain text such as unix programs (sed, awk, perl) or word processors (WordPerfect handles non-printable characters very nicely) or programming editors.

You WILL need a carraige return at the end of each record, so don't simply remove everything at once. I suggest exporting a marker (make a global field with unique text like `EOL` (end of line) and put it at the end of the record. Now you can replace all control characters in your exported file with nothing, leaving you with a monolithic block of text. Then replace all occurences of EOL with a carraige return/newline character.

[julesw]

okay this is really strange. sometimes it seems to work, sometimes it definitely doesn't. but the character in question is definitely a carriage return, not a tab or multiple spaces.

can't imagine any reason for it work inconsistently??