Delete Carriage Returns

[Andre]

I'm trying to import data into SQL server from Excel, but some of the fields
have carriage returns in them. I have been going thru each record and
manually deleting the carriage returns. I've been trying to do find/replace
but can not seem be able to input the carriage return in find/replace. Can
anyone give me an idea what would be easier then manually going thru each
record and delete any carriage return.

 

[Gord Dibben]

Andre

Carriage returns are usually Char(10) or Char(13)

Try Edit>Replace

What: hold down ALT key and enter 0010 from the numpad

With: leave blank or type a space

Replace all.


Gord Dibben Excel MVP

 

[Andre]

Ok Great it got rid of the actual return but it is still leaving a rectangled
box where the return took place is there any way to get rid of that
rectangled box.

 

[Dave Peterson]

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(9), Chr(160)) '<--What showed up in CellView?

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

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

 

[Gord Dibben]

Andre

The box is the carriage return character or whatever character it is.

You have not gotten rid of it yet.

Download Chip Pearson's CellView add-in so's you can see which character is in
the cell.

http://www.cpearson.com/excel/CellView.htm


Gord