nonprintable characters

[shaz]

I have text with embedded non-printable characters through out a large
worksheet of data.

For example,
Collected $98 incash, $5checks4/25/02 Moved, left no addres

If I use CLEAN(), it works but leaves me without a space...I could use
substitute, but I don't know what ascii character this is?

Has someone written a little macro that goes through the text and
prints out each characters acsii code? I've tried doing it manually
with ASCII(text) but haven't been able to use that either. In
addition, these characters are embedded all over the spreadsheet and I
have no way of knowing if it is the same np character or multiple np
characters.

 

[Gord Dibben]

Shaz

Try Edit>Replace

What: Alt + 0010 on the numpad

With: space

Replace all.

As far as seeing what characters are in a cell, download Chip Pearson's CellView
add-in.

http://www.cpearson.com/excel/download.htm

If the character is 0013 then you will need a macro to replace with a space.

This covers them all.

Sub Remove_CR_LF()
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub


Gord Dibben MS Excel MVP

 

[shaz]

Shaz

Try Edit>Replace

What: Alt + 0010 on the numpad

With: space

Replace all.

As far as seeing what characters are in a cell, download Chip Pearson's CellView
add-in.

http://www.cpearson.com/excel/download.htm

If the character is 0013 then you will need a macro to replace with a space.

This covers them all.

Sub Remove_CR_LF()
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

Gord Dibben MS Excel MVP

While I waited for a reply, I modified some code that I found on the
internet...turns out it is similar to what was suggested, but when I
run it, I get an out of memory error. I'm used to programming in a
different language in which I don't have to deal with memory
issues...this isn't that different from the submitted sub or the sub
that I based it on. I don't understand why it gets a memory error.
Anyone know why?

Sub Replace_NPChar_Char32()
'Replaces non-printable Characters 0-31, 129, 141, 143,144,157,160
'with space CHAR(32) and follows with a trim of multiple, leading
'and trailing white space
Dim myRange As Range
Dim myCol As Range
Dim myList(129, 141, 143, 144, 157, 160) As Integer
Dim iCode As Variant


Set myRange = Intersect(ActiveSheet.UsedRange, Selection)
If myRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False


For iCounter = 0 To 31
myRange.Replace what:=Chr(iCounter), replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCounter

For Each iCode In myList
myRange.Replace what:=Chr(iCode), replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next


For Each myCol In myRange.Columns
If Application.CountA(myCol) > 0 Then
myCol.TextToColumns Destination:=myCol(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
End If
Next myCol
Application.ScreenUpdating = True
End Sub

 

[shaz]

While I waited for a reply, I modified some code that I found on the
internet...turns out it is similar to what was suggested, but when I
run it, I get an out of memory error. I'm used to programming in a
different language in which I don't have to deal with memory
issues...this isn't that different from the submitted sub or the sub
that I based it on. I don't understand why it gets a memory error.
Anyone know why?

Sub Replace_NPChar_Char32()
'Replaces non-printable Characters 0-31, 129, 141, 143,144,157,160
'with space CHAR(32) and follows with a trim of multiple, leading
'and trailing white space
Dim myRange As Range
Dim myCol As Range
Dim myList(129, 141, 143, 144, 157, 160) As Integer
Dim iCode As Variant

Set myRange = Intersect(ActiveSheet.UsedRange, Selection)
If myRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False

For iCounter = 0 To 31
myRange.Replace what:=Chr(iCounter), replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCounter

For Each iCode In myList
myRange.Replace what:=Chr(iCode), replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next

For Each myCol In myRange.Columns
If Application.CountA(myCol) > 0 Then
myCol.TextToColumns Destination:=myCol(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
End If
Next myCol
Application.ScreenUpdating = True
End Sub

I think I see my mistake..i think I just created a huge array instead
of a list of 6 items. ouch