how to remove hard returns, which show as little boxes

[eames.librarian]

Hello,
I exported a bunch of Filemaker Pro records into Excel. Wherever there was a
hard return in Filemaker, there is now a little square box in the text,
showing where that hard return was. Is there a batch way I can get rid of all
of these little squares (i.e., all the hard returns)?
Thanks!

 

[Andrea Jones]

Try this macro:

Sub Replacehard()
Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Andrea Jones
http://www.allaboutoffice.co.uk
http://www.stratatraining.co.uk
http://www.allaboutclait.com

 

[Doug Kanter]

Hello,
I exported a bunch of Filemaker Pro records into Excel. Wherever there was
a
hard return in Filemaker, there is now a little square box in the text,
showing where that hard return was. Is there a batch way I can get rid of
all
of these little squares (i.e., all the hard returns)?
Thanks!

I know I've done this in the past, but I don't recall how - probably one of
those times when I got desperate and tried almost everything. So, try this:
Go into edit mode in a cell containing the little box. Select only the box,
and hit CTRL-C (for Copy). Now, hit Esc to get out of edit mode for the cell
you're in. Then, CTRL-H (or click Edit, Replace), put the cursor in the Find
What box and hit CTRL-V to paste the character. Put nothing in the Replace
With box. Then, click Replace All.

If this doesn't work:

Can you export from Filemaker in a format like CSV or DBF, which may cause
less problems?

 

[Doug Kanter]

Slick!!!!!!!

Try this macro:

Sub Replacehard()
Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Andrea Jones
http://www.allaboutoffice.co.uk
http://www.stratatraining.co.uk
http://www.allaboutclait.com