Help! I need to get rid of hard returns!

**Christine D** · Mar 11th, 2002, 12:15 PM

I have downloaded data from Filemaker Pro to a text tab delimited file, and imported into XL. My problem is I have little squares where hard returns are in the file. I need to get rid of these and replace it with a space. I have tried find and replace, but it doesn't seem to work. In help I have discovered the clean function, but I don't understand pivot tables, so it is confusing. Please make directions as simple as possible for this beginner!
Thanks!
Christine

**Aladin Akyurek** · Mar 11th, 2002, 12:29 PM

On 2002-03-11 11:15, Christine D wrote:
I have downloaded data from Filemaker Pro to a text tab delimited file, and imported into XL. My problem is I have little squares where hard returns are in the file. I need to get rid of these and replace it with a space. I have tried find and replace, but it doesn't seem to work. In help I have discovered the clean function, but I don't understand pivot tables, so it is confusing. Please make directions as simple as possible for this beginner!
Thanks!
Christine

Christine,

=SUBSTITUTE(A1,CHAR(13)," ")

will replace carriage returns in A1 with spaces.

Aladin

**Christine D** · Mar 11th, 2002, 12:41 PM

Thanks, Aladdin. I have tried and I still have those little squares. Anything else I can try?

Christine

**Mark W.** · Mar 11th, 2002, 12:48 PM

Christine, let's see what those "little squares" actually are. In an empty cell enter, =CODE(RIGHT(A1)), where A1 is a reference to one of those pesky text strings. What's the value that's returned?