Remove top bit characters

[Brett...]

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 here:
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?

 

[Dave Peterson]

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

 

[flummi]

If your text were in A1 and the first visible word were "draft" then
type in B1:

=RIGHT(A1;LEN(A1)-SEARCH("draft";A1;1)+1)

(You may need to replace the semicolon with a comma depending on your
Windows settings for list separator)

Hans

 

[Tom Ogilvy]

=code(mid(A1,1,1))

 

[Brett...]

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]

You could loop through each cell (yech!) or just do the equivalent of
edit|replace in code.

Did you try the suggested code in the other post?

 

[Brett...]

Yes, I couldn't get it to work, which is why I used the code below.
Why is looping through each cell a problem? If I have unwanted codes in a
number of cells and two columns how else am I going to deal with it?

cheers

Brett

You could loop through each cell (yech!) or just do the equivalent of
edit|replace in code.

Did you try the suggested code in the other post?

 

[Dave Peterson]

Looping through up to 65536 cells for each column seems like it would take a
long time.

I'd use the equivalent of selecting the two columns and then Edit|Replace.

You may want to post the code that failed. (My original code seems to work ok
for me.)

 

[Brett...]

Looping through up to 65536 cells for each column seems like it would
take a long time.

The macro completed the job in a few seconds.

I'd use the equivalent of selecting the two columns and then
Edit|Replace.

You may want to post the code that failed. (My original code seems
to work ok for me.)

It was the cost as posted. Don't worry about it though as the code I used
works ok

regards

Brett