Help! I need to get rid of hard returns!

[Christine D]

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]

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]

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

Christine

 

[Mark W.]

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?

 

[Christine D]

11 is what shows up.

??

Christine

 

[Mark W.]

On 2002-03-11 11:51, Christine D wrote:
11 is what shows up.

??

Christine

Okay, use...

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

 

[Christine D]

Yay!!! Thank you so much!

Christine