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

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

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

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

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

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

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

=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