Remove top bit characters

Brett... · 02-06-2006, 03:55 PM

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?

--

Brett

Dave Peterson · 02-06-2006, 04:30 PM

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 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?
>
> --
>
> Brett

--

Dave Peterson

flummi · 02-06-2006, 04:35 PM

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 · 02-06-2006, 04:35 PM

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

--
Regards,
Tom Ogilvy

"Brett..." <[email protected]> wrote in message
news:[email protected]...
> 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?
>
> --
>
> Brett
>
>

Brett... · 02-07-2006, 02:30 PM

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 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?
>>
>> --
>>
>> Brett

--

Brett

Dave Peterson · 02-07-2006, 02:35 PM

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 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?
> >>
> >> --
> >>
> >> Brett
>
> --
>
> Brett

--

Dave Peterson

Brett... · 02-08-2006, 01:40 PM

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 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?
>>>>
>>>> --
>>>>
>>>> Brett
>>
>> --
>>
>> Brett

--

Brett

Dave Peterson · 02-08-2006, 02:10 PM

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 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?
> >>>>
> >>>> --
> >>>>
> >>>> Brett
> >>
> >> --
> >>
> >> Brett
>
> --
>
> Brett

--

Dave Peterson

Brett... · 02-09-2006, 01:45 PM

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 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?
>>>>>>
>>>>>> --
>>>>>>
>>>>>> Brett
>>>>
>>>> --
>>>>
>>>> Brett
>>
>> --
>>
>> Brett