software

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
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:
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)) 'lt;--What showed up in CellView?

myGoodChars = Array(quot; quot;,quot; quot;) 'lt;--the new characters--I used a space?

If UBound(myGoodChars) lt;gt; UBound(myBadChars) Then
MsgBox quot;Design error!quot;
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:
www.mvps.org/dmcritchie/excel/getstarted.htmquot;Brett...quot; wrote:
gt;
gt; I've an excel file that contains lots of top bit characters.
gt; Ive found that I can replace linefeed chars using the replace tool and
gt; entering ALT 0010 but I'm still left with lots of unknown top bit
gt; characters - shown in Excel and Notepad as a rectangle in Times New Roman.
gt; Ive tried macros that remove CR's and LF's but I'm still left with the top
gt; bit chars!
gt; A sample can be found he
gt; fp.catshill.plus.com/excel.zip
gt;
gt; On a separate but related topic, is there a way to find the ASCII code from
gt; a top bit character?
gt;
gt; --
gt;
gt; Brett

--

Dave Peterson

If your text were in A1 and the first visible word were quot;draftquot; then
type in B1:

=RIGHT(A1;LEN(A1)-SEARCH(quot;draftquot;;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
quot;Brett...quot; gt; wrote in message
...
gt; I've an excel file that contains lots of top bit characters.
gt; Ive found that I can replace linefeed chars using the replace tool and
gt; entering ALT 0010 but I'm still left with lots of unknown top bit
gt; characters - shown in Excel and Notepad as a rectangle in Times New Roman.
gt; Ive tried macros that remove CR's and LF's but I'm still left with the top
gt; bit chars!
gt; A sample can be found he
gt; fp.catshill.plus.com/excel.zip
gt;
gt; On a separate but related topic, is there a way to find the ASCII code
from
gt; a top bit character?
gt;
gt; --
gt;
gt; Brett
gt;
gt;
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)) gt; 0 Then
If (InStr(1, Sheet1.Cells(lngRows, 1), vbCrLf, vbTextCompare) gt; 0) Or _
(InStr(1, Sheet1.Cells(lngRows, 1), vbCr, vbTextCompare) gt; 0) Or _
(InStr(1, Sheet1.Cells(lngRows, 1), vbLf, vbTextCompare) gt; 0) Then
Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1), vbCrLf, quot;quot;)
Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1), vbLf, quot;quot;)
Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1), vbCr, quot;quot;)
End If
End If
Next lngRows
MsgBox quot;donequot;
End Sub

Brett
Dave Peterson wrote:
gt; Chip Pearson has a very nice addin that will help determine what each
gt; character is:
gt; www.cpearson.com/excel/CellView.htm
gt;
gt; Option Explicit
gt; Sub cleanEmUp()
gt;
gt; Dim myBadChars As Variant
gt; Dim myGoodChars As Variant
gt; Dim iCtr As Long
gt;
gt; myBadChars = Array(Chr(10), Chr(13)) 'lt;--What showed up in
gt; CellView?
gt;
gt; myGoodChars = Array(quot; quot;,quot; quot;) 'lt;--the new characters--I used a
gt; space?
gt;
gt; If UBound(myGoodChars) lt;gt; UBound(myBadChars) Then
gt; MsgBox quot;Design error!quot;
gt; Exit Sub
gt; End If
gt;
gt; For iCtr = LBound(myBadChars) To UBound(myBadChars)
gt; ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
gt; Replacement:=myGoodChars(iCtr), _
gt; LookAt:=xlPart, SearchOrder:=xlByRows, _
gt; MatchCase:=False
gt; Next iCtr
gt;
gt; End Sub
gt;
gt; If you're new to macros, you may want to read David McRitchie's intro
gt; at: www.mvps.org/dmcritchie/excel/getstarted.htm
gt;
gt;
gt; quot;Brett...quot; wrote:
gt;gt;
gt;gt; I've an excel file that contains lots of top bit characters.
gt;gt; Ive found that I can replace linefeed chars using the replace tool
gt;gt; and entering ALT 0010 but I'm still left with lots of unknown top bit
gt;gt; characters - shown in Excel and Notepad as a rectangle in Times New
gt;gt; Roman. Ive tried macros that remove CR's and LF's but I'm still left
gt;gt; with the top bit chars!
gt;gt; A sample can be found he
gt;gt; fp.catshill.plus.com/excel.zip
gt;gt;
gt;gt; On a separate but related topic, is there a way to find the ASCII
gt;gt; code from a top bit character?
gt;gt;
gt;gt; --
gt;gt;
gt;gt; 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?
quot;Brett...quot; wrote:
gt;
gt; Thanks Dave
gt; The utility told me that the unwanted characters were CHR(13) but I couldn't
gt; get your macro below to work.
gt; Another problem was that the cells to be searched were not in the first
gt; sheet or in column 1.
gt; I eventually got round the problem by cutting and pasting the relevant
gt; columns one at a time into Column 1 of the first sheet of a workbook and
gt; using the following macro:
gt;
gt; Sub ExcludeReturn()
gt; Dim lngRows As Long, strCell As String
gt; For lngRows = 1 To 65500
gt; strCell = Sheet1.Cells(lngRows, 1)
gt; If Len(Sheet1.Cells(lngRows, 1)) gt; 0 Then
gt; If (InStr(1, Sheet1.Cells(lngRows, 1), vbCrLf, vbTextCompare) gt; 0) Or _
gt; (InStr(1, Sheet1.Cells(lngRows, 1), vbCr, vbTextCompare) gt; 0) Or _
gt; (InStr(1, Sheet1.Cells(lngRows, 1), vbLf, vbTextCompare) gt; 0) Then
gt; Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1), vbCrLf, quot;quot;)
gt; Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1), vbLf, quot;quot;)
gt; Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1), vbCr, quot;quot;)
gt; End If
gt; End If
gt; Next lngRows
gt; MsgBox quot;donequot;
gt; End Sub
gt;
gt; Brett
gt;
gt; Dave Peterson wrote:
gt; gt; Chip Pearson has a very nice addin that will help determine what each
gt; gt; character is:
gt; gt; www.cpearson.com/excel/CellView.htm
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub cleanEmUp()
gt; gt;
gt; gt; Dim myBadChars As Variant
gt; gt; Dim myGoodChars As Variant
gt; gt; Dim iCtr As Long
gt; gt;
gt; gt; myBadChars = Array(Chr(10), Chr(13)) 'lt;--What showed up in
gt; gt; CellView?
gt; gt;
gt; gt; myGoodChars = Array(quot; quot;,quot; quot;) 'lt;--the new characters--I used a
gt; gt; space?
gt; gt;
gt; gt; If UBound(myGoodChars) lt;gt; UBound(myBadChars) Then
gt; gt; MsgBox quot;Design error!quot;
gt; gt; Exit Sub
gt; gt; End If
gt; gt;
gt; gt; For iCtr = LBound(myBadChars) To UBound(myBadChars)
gt; gt; ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
gt; gt; Replacement:=myGoodChars(iCtr), _
gt; gt; LookAt:=xlPart, SearchOrder:=xlByRows, _
gt; gt; MatchCase:=False
gt; gt; Next iCtr
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; If you're new to macros, you may want to read David McRitchie's intro
gt; gt; at: www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt;
gt; gt;
gt; gt; quot;Brett...quot; wrote:
gt; gt;gt;
gt; gt;gt; I've an excel file that contains lots of top bit characters.
gt; gt;gt; Ive found that I can replace linefeed chars using the replace tool
gt; gt;gt; and entering ALT 0010 but I'm still left with lots of unknown top bit
gt; gt;gt; characters - shown in Excel and Notepad as a rectangle in Times New
gt; gt;gt; Roman. Ive tried macros that remove CR's and LF's but I'm still left
gt; gt;gt; with the top bit chars!
gt; gt;gt; A sample can be found he
gt; gt;gt; fp.catshill.plus.com/excel.zip
gt; gt;gt;
gt; gt;gt; On a separate but related topic, is there a way to find the ASCII
gt; gt;gt; code from a top bit character?
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt;
gt; gt;gt; Brett
gt;
gt; --
gt;
gt; 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:
gt; You could loop through each cell (yech!) or just do the equivalent of
gt; edit|replace in code.
gt;
gt; Did you try the suggested code in the other post?
gt;
gt;
gt;
gt; quot;Brett...quot; wrote:
gt;gt;
gt;gt; Thanks Dave
gt;gt; The utility told me that the unwanted characters were CHR(13) but I
gt;gt; couldn't get your macro below to work.
gt;gt; Another problem was that the cells to be searched were not in the
gt;gt; first sheet or in column 1.
gt;gt; I eventually got round the problem by cutting and pasting the
gt;gt; relevant columns one at a time into Column 1 of the first sheet of a
gt;gt; workbook and using the following macro:
gt;gt;
gt;gt; Sub ExcludeReturn()
gt;gt; Dim lngRows As Long, strCell As String
gt;gt; For lngRows = 1 To 65500
gt;gt; strCell = Sheet1.Cells(lngRows, 1)
gt;gt; If Len(Sheet1.Cells(lngRows, 1)) gt; 0 Then
gt;gt; If (InStr(1, Sheet1.Cells(lngRows, 1), vbCrLf, vbTextCompare) gt; 0)
gt;gt; Or _ (InStr(1, Sheet1.Cells(lngRows, 1), vbCr, vbTextCompare) gt; 0)
gt;gt; Or _ (InStr(1, Sheet1.Cells(lngRows, 1), vbLf, vbTextCompare) gt; 0)
gt;gt; Then Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1),
gt;gt; vbCrLf, quot;quot;) Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows,
gt;gt; 1), vbLf, quot;quot;) Sheet1.Cells(lngRows, 1) =
gt;gt; Replace(Sheet1.Cells(lngRows, 1), vbCr, quot;quot;)
gt;gt; End If
gt;gt; End If
gt;gt; Next lngRows
gt;gt; MsgBox quot;donequot;
gt;gt; End Sub
gt;gt;
gt;gt; Brett
gt;gt;
gt;gt; Dave Peterson wrote:
gt;gt;gt; Chip Pearson has a very nice addin that will help determine what
gt;gt;gt; each character is:
gt;gt;gt; www.cpearson.com/excel/CellView.htm
gt;gt;gt;
gt;gt;gt; Option Explicit
gt;gt;gt; Sub cleanEmUp()
gt;gt;gt;
gt;gt;gt; Dim myBadChars As Variant
gt;gt;gt; Dim myGoodChars As Variant
gt;gt;gt; Dim iCtr As Long
gt;gt;gt;
gt;gt;gt; myBadChars = Array(Chr(10), Chr(13)) 'lt;--What showed up in
gt;gt;gt; CellView?
gt;gt;gt;
gt;gt;gt; myGoodChars = Array(quot; quot;,quot; quot;) 'lt;--the new characters--I used a
gt;gt;gt; space?
gt;gt;gt;
gt;gt;gt; If UBound(myGoodChars) lt;gt; UBound(myBadChars) Then
gt;gt;gt; MsgBox quot;Design error!quot;
gt;gt;gt; Exit Sub
gt;gt;gt; End If
gt;gt;gt;
gt;gt;gt; For iCtr = LBound(myBadChars) To UBound(myBadChars)
gt;gt;gt; ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
gt;gt;gt; Replacement:=myGoodChars(iCtr), _
gt;gt;gt; LookAt:=xlPart, SearchOrder:=xlByRows, _
gt;gt;gt; MatchCase:=False
gt;gt;gt; Next iCtr
gt;gt;gt;
gt;gt;gt; End Sub
gt;gt;gt;
gt;gt;gt; If you're new to macros, you may want to read David McRitchie's
gt;gt;gt; intro at: www.mvps.org/dmcritchie/excel/getstarted.htm
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; quot;Brett...quot; wrote:
gt;gt;gt;gt;
gt;gt;gt;gt; I've an excel file that contains lots of top bit characters.
gt;gt;gt;gt; Ive found that I can replace linefeed chars using the replace tool
gt;gt;gt;gt; and entering ALT 0010 but I'm still left with lots of unknown top
gt;gt;gt;gt; bit characters - shown in Excel and Notepad as a rectangle in
gt;gt;gt;gt; Times New Roman. Ive tried macros that remove CR's and LF's but
gt;gt;gt;gt; I'm still left with the top bit chars!
gt;gt;gt;gt; A sample can be found he
gt;gt;gt;gt; fp.catshill.plus.com/excel.zip
gt;gt;gt;gt;
gt;gt;gt;gt; On a separate but related topic, is there a way to find the ASCII
gt;gt;gt;gt; code from a top bit character?
gt;gt;gt;gt;
gt;gt;gt;gt; --
gt;gt;gt;gt;
gt;gt;gt;gt; Brett
gt;gt;
gt;gt; --
gt;gt;
gt;gt; 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.)

quot;Brett...quot; wrote:
gt;
gt; Yes, I couldn't get it to work, which is why I used the code below.
gt; Why is looping through each cell a problem? If I have unwanted codes in a
gt; number of cells and two columns how else am I going to deal with it?
gt;
gt; cheers
gt;
gt; Brett
gt;
gt; Dave Peterson wrote:
gt; gt; You could loop through each cell (yech!) or just do the equivalent of
gt; gt; edit|replace in code.
gt; gt;
gt; gt; Did you try the suggested code in the other post?
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Brett...quot; wrote:
gt; gt;gt;
gt; gt;gt; Thanks Dave
gt; gt;gt; The utility told me that the unwanted characters were CHR(13) but I
gt; gt;gt; couldn't get your macro below to work.
gt; gt;gt; Another problem was that the cells to be searched were not in the
gt; gt;gt; first sheet or in column 1.
gt; gt;gt; I eventually got round the problem by cutting and pasting the
gt; gt;gt; relevant columns one at a time into Column 1 of the first sheet of a
gt; gt;gt; workbook and using the following macro:
gt; gt;gt;
gt; gt;gt; Sub ExcludeReturn()
gt; gt;gt; Dim lngRows As Long, strCell As String
gt; gt;gt; For lngRows = 1 To 65500
gt; gt;gt; strCell = Sheet1.Cells(lngRows, 1)
gt; gt;gt; If Len(Sheet1.Cells(lngRows, 1)) gt; 0 Then
gt; gt;gt; If (InStr(1, Sheet1.Cells(lngRows, 1), vbCrLf, vbTextCompare) gt; 0)
gt; gt;gt; Or _ (InStr(1, Sheet1.Cells(lngRows, 1), vbCr, vbTextCompare) gt; 0)
gt; gt;gt; Or _ (InStr(1, Sheet1.Cells(lngRows, 1), vbLf, vbTextCompare) gt; 0)
gt; gt;gt; Then Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1),
gt; gt;gt; vbCrLf, quot;quot;) Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows,
gt; gt;gt; 1), vbLf, quot;quot;) Sheet1.Cells(lngRows, 1) =
gt; gt;gt; Replace(Sheet1.Cells(lngRows, 1), vbCr, quot;quot;)
gt; gt;gt; End If
gt; gt;gt; End If
gt; gt;gt; Next lngRows
gt; gt;gt; MsgBox quot;donequot;
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt; Brett
gt; gt;gt;
gt; gt;gt; Dave Peterson wrote:
gt; gt;gt;gt; Chip Pearson has a very nice addin that will help determine what
gt; gt;gt;gt; each character is:
gt; gt;gt;gt; www.cpearson.com/excel/CellView.htm
gt; gt;gt;gt;
gt; gt;gt;gt; Option Explicit
gt; gt;gt;gt; Sub cleanEmUp()
gt; gt;gt;gt;
gt; gt;gt;gt; Dim myBadChars As Variant
gt; gt;gt;gt; Dim myGoodChars As Variant
gt; gt;gt;gt; Dim iCtr As Long
gt; gt;gt;gt;
gt; gt;gt;gt; myBadChars = Array(Chr(10), Chr(13)) 'lt;--What showed up in
gt; gt;gt;gt; CellView?
gt; gt;gt;gt;
gt; gt;gt;gt; myGoodChars = Array(quot; quot;,quot; quot;) 'lt;--the new characters--I used a
gt; gt;gt;gt; space?
gt; gt;gt;gt;
gt; gt;gt;gt; If UBound(myGoodChars) lt;gt; UBound(myBadChars) Then
gt; gt;gt;gt; MsgBox quot;Design error!quot;
gt; gt;gt;gt; Exit Sub
gt; gt;gt;gt; End If
gt; gt;gt;gt;
gt; gt;gt;gt; For iCtr = LBound(myBadChars) To UBound(myBadChars)
gt; gt;gt;gt; ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
gt; gt;gt;gt; Replacement:=myGoodChars(iCtr), _
gt; gt;gt;gt; LookAt:=xlPart, SearchOrder:=xlByRows, _
gt; gt;gt;gt; MatchCase:=False
gt; gt;gt;gt; Next iCtr
gt; gt;gt;gt;
gt; gt;gt;gt; End Sub
gt; gt;gt;gt;
gt; gt;gt;gt; If you're new to macros, you may want to read David McRitchie's
gt; gt;gt;gt; intro at: www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt; quot;Brett...quot; wrote:
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt; I've an excel file that contains lots of top bit characters.
gt; gt;gt;gt;gt; Ive found that I can replace linefeed chars using the replace tool
gt; gt;gt;gt;gt; and entering ALT 0010 but I'm still left with lots of unknown top
gt; gt;gt;gt;gt; bit characters - shown in Excel and Notepad as a rectangle in
gt; gt;gt;gt;gt; Times New Roman. Ive tried macros that remove CR's and LF's but
gt; gt;gt;gt;gt; I'm still left with the top bit chars!
gt; gt;gt;gt;gt; A sample can be found he
gt; gt;gt;gt;gt; fp.catshill.plus.com/excel.zip
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt; On a separate but related topic, is there a way to find the ASCII
gt; gt;gt;gt;gt; code from a top bit character?
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt; --
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt; Brett
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt;
gt; gt;gt; Brett
gt;
gt; --
gt;
gt; Brett

--

Dave Peterson

Dave Peterson wrote:
gt; Looping through up to 65536 cells for each column seems like it would
gt; take a long time.

The macro completed the job in a few seconds.

gt;
gt; I'd use the equivalent of selecting the two columns and then
gt; Edit|Replace.
gt;
gt; You may want to post the code that failed. (My original code seems
gt; to work ok for me.)
gt;

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

regards

Brettgt; quot;Brett...quot; wrote:
gt;gt;
gt;gt; Yes, I couldn't get it to work, which is why I used the code below.
gt;gt; Why is looping through each cell a problem? If I have unwanted codes
gt;gt; in a number of cells and two columns how else am I going to deal
gt;gt; with it?
gt;gt;
gt;gt; cheers
gt;gt;
gt;gt; Brett
gt;gt;
gt;gt; Dave Peterson wrote:
gt;gt;gt; You could loop through each cell (yech!) or just do the equivalent
gt;gt;gt; of edit|replace in code.
gt;gt;gt;
gt;gt;gt; Did you try the suggested code in the other post?
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; quot;Brett...quot; wrote:
gt;gt;gt;gt;
gt;gt;gt;gt; Thanks Dave
gt;gt;gt;gt; The utility told me that the unwanted characters were CHR(13) but I
gt;gt;gt;gt; couldn't get your macro below to work.
gt;gt;gt;gt; Another problem was that the cells to be searched were not in the
gt;gt;gt;gt; first sheet or in column 1.
gt;gt;gt;gt; I eventually got round the problem by cutting and pasting the
gt;gt;gt;gt; relevant columns one at a time into Column 1 of the first sheet of
gt;gt;gt;gt; a workbook and using the following macro:
gt;gt;gt;gt;
gt;gt;gt;gt; Sub ExcludeReturn()
gt;gt;gt;gt; Dim lngRows As Long, strCell As String
gt;gt;gt;gt; For lngRows = 1 To 65500
gt;gt;gt;gt; strCell = Sheet1.Cells(lngRows, 1)
gt;gt;gt;gt; If Len(Sheet1.Cells(lngRows, 1)) gt; 0 Then
gt;gt;gt;gt; If (InStr(1, Sheet1.Cells(lngRows, 1), vbCrLf, vbTextCompare) gt; 0)
gt;gt;gt;gt; Or _ (InStr(1, Sheet1.Cells(lngRows, 1), vbCr, vbTextCompare) gt; 0)
gt;gt;gt;gt; Or _ (InStr(1, Sheet1.Cells(lngRows, 1), vbLf, vbTextCompare) gt; 0)
gt;gt;gt;gt; Then Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1),
gt;gt;gt;gt; vbCrLf, quot;quot;) Sheet1.Cells(lngRows, 1) =
gt;gt;gt;gt; Replace(Sheet1.Cells(lngRows, 1), vbLf, quot;quot;) Sheet1.Cells(lngRows,
gt;gt;gt;gt; 1) = Replace(Sheet1.Cells(lngRows, 1), vbCr, quot;quot;)
gt;gt;gt;gt; End If
gt;gt;gt;gt; End If
gt;gt;gt;gt; Next lngRows
gt;gt;gt;gt; MsgBox quot;donequot;
gt;gt;gt;gt; End Sub
gt;gt;gt;gt;
gt;gt;gt;gt; Brett
gt;gt;gt;gt;
gt;gt;gt;gt; Dave Peterson wrote:
gt;gt;gt;gt;gt; Chip Pearson has a very nice addin that will help determine what
gt;gt;gt;gt;gt; each character is:
gt;gt;gt;gt;gt; www.cpearson.com/excel/CellView.htm
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Option Explicit
gt;gt;gt;gt;gt; Sub cleanEmUp()
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Dim myBadChars As Variant
gt;gt;gt;gt;gt; Dim myGoodChars As Variant
gt;gt;gt;gt;gt; Dim iCtr As Long
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; myBadChars = Array(Chr(10), Chr(13)) 'lt;--What showed up in
gt;gt;gt;gt;gt; CellView?
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; myGoodChars = Array(quot; quot;,quot; quot;) 'lt;--the new characters--I
gt;gt;gt;gt;gt; used a space?
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; If UBound(myGoodChars) lt;gt; UBound(myBadChars) Then
gt;gt;gt;gt;gt; MsgBox quot;Design error!quot;
gt;gt;gt;gt;gt; Exit Sub
gt;gt;gt;gt;gt; End If
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; For iCtr = LBound(myBadChars) To UBound(myBadChars)
gt;gt;gt;gt;gt; ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
gt;gt;gt;gt;gt; Replacement:=myGoodChars(iCtr), _
gt;gt;gt;gt;gt; LookAt:=xlPart, SearchOrder:=xlByRows, _
gt;gt;gt;gt;gt; MatchCase:=False
gt;gt;gt;gt;gt; Next iCtr
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; If you're new to macros, you may want to read David McRitchie's
gt;gt;gt;gt;gt; intro at: www.mvps.org/dmcritchie/excel/getstarted.htm
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; quot;Brett...quot; wrote:
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt; I've an excel file that contains lots of top bit characters.
gt;gt;gt;gt;gt;gt; Ive found that I can replace linefeed chars using the replace
gt;gt;gt;gt;gt;gt; tool and entering ALT 0010 but I'm still left with lots of
gt;gt;gt;gt;gt;gt; unknown top bit characters - shown in Excel and Notepad as a
gt;gt;gt;gt;gt;gt; rectangle in Times New Roman. Ive tried macros that remove CR's
gt;gt;gt;gt;gt;gt; and LF's but I'm still left with the top bit chars!
gt;gt;gt;gt;gt;gt; A sample can be found he
gt;gt;gt;gt;gt;gt; fp.catshill.plus.com/excel.zip
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt; On a separate but related topic, is there a way to find the ASCII
gt;gt;gt;gt;gt;gt; code from a top bit character?
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt; --
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt; Brett
gt;gt;gt;gt;
gt;gt;gt;gt; --
gt;gt;gt;gt;
gt;gt;gt;gt; Brett
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Brett