Search and replace for non printable characters

[Guest]

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

 

[Jake Marx]

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]

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

 

[Guest]

Hi Bernie

that worked perfectly. Thanks.

Regerds

Ric

 

[limit in cell for Replacement]

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