Run this macro and see what happens.

Sub Remove_CR_LF()
Selection.Replace What:=Chr(160), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13) & Chr(10), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(10), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

BTW.........did you try Chip's CellView add-in as directed by Dave P?


Gord

On Tue, 20 Mar 2007 07:21:08 -0700, Luvsql
wrote:

Using the Alt then 0010 removes the issues with text that goes into 2 lines

123 Street
Unit 5

becomes 123 StreetUnit 5

However, ones that have more than 2 lines, still have some sort of character
within them that I can't remove. With Crystal reports, I had to remove both
Chr10 and Chr 13. Is there an edit replace for both

"Gord Dibben" wrote:

EditReplace

What: Alt + 0010(on the numpad)

With: nothing

Replace all.


Gord Dibben MS Excel MVP


On Mon, 19 Mar 2007 19:22:03 -0700, Luvsql
wrote:

I have spent a good 8 hours on this over the last week and CANNOT figure this
out. I have exported all our contacts out of Outlook to excel and the
addresses and some other fields are being exported with the carriage return,
but you cannot see the symbol. If I export to tab delimited file, the ones
with the carriage return are being put into 2 to 3 separate lines and makes
importing impossible.

For example, when I click on a field it actually appears as

123 Street
Unit 4

So if it's exported to text, it messes up everything. I have tried every
formula I can possible try and downloaded an add-on to view code and it says
"cell has an apostrophe prefix" but cannot see that either.

I can't even import into access and try to export to text as it still
maintains this character somewhere.

Please can someone please tell me how to formute the cell as 123 Street Unit
4?