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

Search and replace for non printable characters
 

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

Search and replace for non printable characters
 

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" wrote in message
...
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

Search and replace for non printable characters
 

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
...
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" wrote in message
...
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

Search and replace for non printable characters
 

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
...
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" wrote in message
...
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

Search and replace for non printable characters
 

Hi Bernie,

Is there a chr limit in the cell for this Replace method?
I have a cell with 867 chr, Replace did not work, but it worded when I
deleted down to 854. Any reason?

Thanks.
Howard

"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
...
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" wrote in message
...
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