Search and replace for non printable characters

Hi

Using Excel 2002

I have a number of cells with abc¬xyz¬opq ... . I want to search and replace
the '¬' character for 'alt-enter' i.e. the enter key pressed whilst the 'alt'
key is pressed.

How do you do this.

I have tried recording a macro while doing this but all that is shown
changed is '¬' to chr(10). If i try apply this s&r then the cell is split to
a new line for each '¬'

Any ideas.

PS 'alt-enter' gives a soft line feed in the cell.

regards

Ric

Hi ricl999,

Will something like this work for you?

Sheets("Sheet1").UsedRange.Replace "¬", Chr(10)

That works for me when I try it on a sample worksheet.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


ricl999 wrote:
> Hi
>
> Using Excel 2002
>
> I have a number of cells with abc¬xyz¬opq ... . I want to search and
> replace the '¬' character for 'alt-enter' i.e. the enter key pressed
> whilst the 'alt' key is pressed.
>
> How do you do this.
>
> I have tried recording a macro while doing this but all that is shown
> changed is '¬' to chr(10). If i try apply this s&r then the cell is
> split to a new line for each '¬'
>
> Any ideas.
>
> PS 'alt-enter' gives a soft line feed in the cell.
>
> regards
>
> Ric

Ric,

Try this, to replace them with a space

Sub ReplaceChr10()
Selection.Replace What:=Chr(10), _
Replacement:=Chr(32), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

or this, to simply remove them:

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

HTH,
Bernie
MS Excel MVP

"ricl999" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> Using Excel 2002
>
> I have a number of cells with abc¬xyz¬opq ... . I want to search and
replace
> the '¬' character for 'alt-enter' i.e. the enter key pressed whilst the
'alt'
> key is pressed.
>
> How do you do this.
>
> I have tried recording a macro while doing this but all that is shown
> changed is '¬' to chr(10). If i try apply this s&r then the cell is split
to
> a new line for each '¬'
>
> Any ideas.
>
> PS 'alt-enter' gives a soft line feed in the cell.
>
> regards
>
> Ric

Ric,

I'm sorry, I completely misread what you wanted. Try this

Sub Replace()
Selection.Replace What:="¬", _
Replacement:=Chr(10), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.WrapText = True
End Sub

HTH,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Ric,
>
> Try this, to replace them with a space
>
> Sub ReplaceChr10()
> Selection.Replace What:=Chr(10), _
> Replacement:=Chr(32), LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False
> End Sub
>
> or this, to simply remove them:
>
> Sub ReplaceChr10Part2()
> Selection.Replace What:=Chr(10), _
> Replacement:="", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False
> End Sub
>
> HTH,
> Bernie
> MS Excel MVP
>
> "ricl999" <[email protected]> wrote in message
> news:[email protected]...
> > Hi
> >
> > Using Excel 2002
> >
> > I have a number of cells with abc¬xyz¬opq ... . I want to search and
> replace
> > the '¬' character for 'alt-enter' i.e. the enter key pressed whilst the
> 'alt'
> > key is pressed.
> >
> > How do you do this.
> >
> > I have tried recording a macro while doing this but all that is shown
> > changed is '¬' to chr(10). If i try apply this s&r then the cell is
split
> to
> > a new line for each '¬'
> >
> > Any ideas.
> >
> > PS 'alt-enter' gives a soft line feed in the cell.
> >
> > regards
> >
> > Ric
>
>

Hi Bernie

that worked perfectly. Thanks.

Regerds

Ric

"Bernie Deitrick" wrote:

> Ric,
>
> I'm sorry, I completely misread what you wanted. Try this
>
> Sub Replace()
> Selection.Replace What:="¬", _
> Replacement:=Chr(10), LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False
> Selection.WrapText = True
> End Sub
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
> > Ric,
> >
> > Try this, to replace them with a space
> >
> > Sub ReplaceChr10()
> > Selection.Replace What:=Chr(10), _
> > Replacement:=Chr(32), LookAt:=xlPart, _
> > SearchOrder:=xlByRows, MatchCase:=False
> > End Sub
> >
> > or this, to simply remove them:
> >
> > Sub ReplaceChr10Part2()
> > Selection.Replace What:=Chr(10), _
> > Replacement:="", LookAt:=xlPart, _
> > SearchOrder:=xlByRows, MatchCase:=False
> > End Sub
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "ricl999" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi
> > >
> > > Using Excel 2002
> > >
> > > I have a number of cells with abc¬xyz¬opq ... . I want to search and
> > replace
> > > the '¬' character for 'alt-enter' i.e. the enter key pressed whilst the
> > 'alt'
> > > key is pressed.
> > >
> > > How do you do this.
> > >
> > > I have tried recording a macro while doing this but all that is shown
> > > changed is '¬' to chr(10). If i try apply this s&r then the cell is
> split
> > to
> > > a new line for each '¬'
> > >
> > > Any ideas.
> > >
> > > PS 'alt-enter' gives a soft line feed in the cell.
> > >
> > > regards
> > >
> > > Ric
> >
> >
>
>
>