Delete Carriage Returns

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.

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

On Wed, 30 Nov 2005 08:58:28 -0800, "Andre" <[email protected]>
wrote:

>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.

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.


"Gord Dibben" wrote:

> 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
>
> On Wed, 30 Nov 2005 08:58:28 -0800, "Andre" <[email protected]>
> wrote:
>
> >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.
>
>

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

Andre wrote:
>
> 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.
>
> "Gord Dibben" wrote:
>
> > 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
> >
> > On Wed, 30 Nov 2005 08:58:28 -0800, "Andre" <[email protected]>
> > wrote:
> >
> > >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.
> >
> >

--

Dave Peterson

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

On Thu, 1 Dec 2005 13:50:04 -0800, "Andre" <[email protected]>
wrote:

>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.
>
>
>"Gord Dibben" wrote:
>
>> 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
>>
>> On Wed, 30 Nov 2005 08:58:28 -0800, "Andre" <[email protected]>
>> wrote:
>>
>> >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.
>>
>>