Remove top bit characters
 

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

Remove top bit characters
 

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

--

Dave Peterson

Remove top bit characters
 

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

Remove top bit characters
 

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

--
Regards,
Tom Ogilvy



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

Remove top bit characters
 

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

Remove top bit characters
 

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..." wrote:

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

--

Dave Peterson

Remove top bit characters
 

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



Dave Peterson wrote:
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..." wrote:

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

--

Brett

Remove top bit characters
 

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..." wrote:

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

Dave Peterson wrote:
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..." wrote:

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

--

Brett

--

Dave Peterson

Remove top bit characters
 

Dave Peterson wrote:
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


"Brett..." wrote:

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

Dave Peterson wrote:
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..." wrote:

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

--

Brett