Thanks Dave
The utility told me that the unwanted characters were CHR(13) but I couldn't
get your macro below to work.
Another problem was that the cells to be searched were not in the first
sheet or in column 1.
I eventually got round the problem by cutting and pasting the relevant
columns one at a time into Column 1 of the first sheet of a workbook and
using the following macro:

Sub ExcludeReturn()
Dim lngRows As Long, strCell As String
For lngRows = 1 To 65500
strCell = Sheet1.Cells(lngRows, 1)
If Len(Sheet1.Cells(lngRows, 1)) 0 Then
If (InStr(1, Sheet1.Cells(lngRows, 1), vbCrLf, vbTextCompare) 0) Or _
(InStr(1, Sheet1.Cells(lngRows, 1), vbCr, vbTextCompare) 0) Or _
(InStr(1, Sheet1.Cells(lngRows, 1), vbLf, vbTextCompare) 0) Then
Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1), vbCrLf, "")
Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1), vbLf, "")
Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1), vbCr, "")
End If
End If
Next lngRows
MsgBox "done"
End Sub

Brett



Dave Peterson wrote:
Chip Pearson has a very nice addin that will help determine what each
character is:
http://www.cpearson.com/excel/CellView.htm

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in
CellView?

myGoodChars = Array(" "," ") '<--the new characters--I used a
space?

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


"Brett..." wrote:

I've an excel file that contains lots of top bit characters.
Ive found that I can replace linefeed chars using the replace tool
and entering ALT 0010 but I'm still left with lots of unknown top bit
characters - shown in Excel and Notepad as a rectangle in Times New
Roman. Ive tried macros that remove CR's and LF's but I'm still left
with the top bit chars!
A sample can be found he
http://fp.catshill.plus.com/excel.zip

On a separate but related topic, is there a way to find the ASCII
code from a top bit character?

--

Brett

--

Brett