Removing carriage returns from excel

[Luvsql]

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?

 

[Bob Umlas, Excel MVP]

Alt/F11 (to get to the VBE), then Ctrl/G (immediate window), then enter this
line:
cells.Replace chr(10),"",xlpart
--voila!

 

[Gord Dibben]

Edit>Replace

What: Alt + 0010(on the numpad)

With: nothing

Replace all.


Gord Dibben MS Excel MVP

 

[Luvsql]

Neither of these seem to work. I did do the edit > replace and it said it
did find items to replace, yet, the data still appears as

21 Progress AvenueUnit8 (eg) and if I double click on the cell, the row
height doubles

21 Progress Avenue
Unit 8

 

[Dave Peterson]

Select the cells to fix (all of them???) and
format|cells|alignment tab|uncheck wrap text.

 

[Luvsql]

Text wrap is not marked by default. I changed it to Chr(13) and it's removed
some of the formatting. Now, with cells that had more than 2 returns, it's a
mess. Even if I backspace over data and re-type it is deleting letters and
inserting spaces.

This seems to be such a HUGE undertaking just to remove formatting. Why
will edit > Copy < paste special > values not remove FORMATTING??

Even if I paste into notepad, copy, then paste back into a new cell it is
still putting data with returns.

 

[Luvsql]

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

 

[Dave Peterson]

Why would pasting values change any format? If it did, then I think we'd have a
problem.

I'm not sure what "I changed it to Chr(13)" means. What did you change and
where did you change it?

Saved from a previous post...

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

Since you do see a box, then you can either fix it via a helper cell or a macro:

=substitute(a1,char(13),"")
or
=substitute(a1,char(13)," ")

Replace 13 with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in CellView?

myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Luvsql]

I used this formula and it fixed it:

=CLEAN(SUBSTITUTE(A1,CHAR(10)," "))

 

[Gord Dibben]

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