Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Remove line breaks in field

Remove line breaks in field

Remove line breaks in field

I have a comment (memo) field in a report I am exporting to a tab delimited txt file. In this field there will frequently be line breaks / hard returns that show up on separate lines in the detail section of my report. I need to eliminate these so all lines in the comment field show up on one line. i.e., instead of :


I want:

text text text

Is there a formula to ignore or remove these line breaks/hard returns?

Thanks for your help

RE: Remove line breaks in field

You need to determine what is causing the line breaks, but you might try:

replace(replace({table.string}, chr(13)," "), chr(10)," ")

I think either character could cause a return.


RE: Remove line breaks in field

The returns are done in the application, a purchase order software.
Since this is a memo field i won't be able to do a formula. I'm using CR 8.5 , to an SQL 2000 database. I tried a conversion SQL expression I found in a search of the forums but couldn't get them to work.
I can export to Excel and it will keep everything in that field on one line. It just an extra step I'd rather not take.
Thanks anyway

RE: Remove line breaks in field

I am having the same problem. I am grabbing data from a peoplesoft db, Oracle 8i, CR10.
The fields shows as memo in crystal, the chr(13) replace doesn't work but I can't tell if it's because the return is some other special char or the formula won't work on a memo field.
Any advice would be greatly appreciated.

RE: Remove line breaks in field

Since I'm only using version 8, I'm not sure how V10 handles memo fields.  You may be able to adapt the following to give you a list of the chr() values of the members of the field.  You would then just have count the characters to find the "offending" chr value.

stringvar test:="your memo field here" ;
numbervar loopit:=len(test);
stringvar output;
numbervar looper;

for looper:=1 to loopit do(
output:=output & totext(asc(test [looper]),0) &","   )  ;


RE: Remove line breaks in field

Ok, I did some testing by using instr({myfield},'A') to find out if the formula would likely work on the memofield in cr10, and it does.
Then to figure out what special char was being used, I created a quick report that takes in a bunch of records from a table (any table will do). In the detail line I used the following formula - chr(recordnumber) to display what each char number would show. This showed that, for the Arial font, there are several 'blank' chars in addition to chr(13), including 266 and 269, which ended up being the value I was looking to eliminate.
Not fool proof, because I think the fonts vary in this respect, however a good basic font should give some clue as to what chars are "special". Thanks for the quick response, mbarron.


RE: Remove line breaks in field

Dear Wprice,

The issue with our sql expression is that in 8.5 SQL Server ODBC the return of the sql expression is limited to 254 total characters.

Does the data stored in this memo field, exceed that length?

If not, then this will work:


        Substring(Incident."Incident Description",1,8000)
    ,char(13),' ')
,char(10),' ')

I lined it up the way I did, so that you could see the values that goes with each function.  Replace Incident."Incident Description" with the name of your memo field, it will not show up in the list of available fields, you must enter it yourself.

If you need more than 254 characters, at CR 8.5 version, then your only option is to create a field in the view you are reporting on that does the same thing, but returns all the data in the field after stripping the carriage return and line field characters.


Rosemary Lieberman
rosemary@microflo.com, www.microflo.com
Microflo provides expert consulting on MagicTSD and Crystal Reports.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close