Try this to replace the ALT ENTER with a semicolon

Sub FindAltEnter()
Dim rng As Excel.Range, rng1 As Excel.Range
Set rng = Cells.Find(What:=Chr(10), _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
sAddr = rng.Address
Do
If rng1 Is Nothing Then
Set rng1 = rng
Else
Set rng1 = Union(rng1, rng)
End If
Set rng = Cells.FindNext(rng)
Loop Until rng.Address = sAddr
End If
If Not rng1 Is Nothing Then
rng1.Select
rng1.Interior.ColorIndex = 6
Else
MsgBox "None found"
End If
End Sub

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Craig860" wrote:

Hi,
Im trying to use the text to col function in excel and have hit a snag.
My source data looks like this:
"DEPT OF SPECIAL SERVICES –¡ 760 COOPER ST.–¡ AGAWAM MA 01001"
I think these little boxes are CR returns because when I paste it as is in
word it appears line under line.
When I do the text to columns feature and I choose OTHER as the seperator I
can't insert this little box. Even when I do a find replace it doesn't see it.
I want it so each cell a, b, c has the name, street address, city and state
etc.
I even tried doing a find replace to change the box to something else.
Any ideas?