Get rid of small squares in column

[EllenM]

Hello,
I have a column with small squares here and there within a column. I think
they're either tabs or carriage returns. They mess up my import into Access.
Anyone know how to get rid of them?

Thanks,
Ellen

 

[PCLIVE]

Maybe you can copy one of the squares and then perform a Find and Replace,
replacing the character with nothing.

HTH,
Paul

 

[EllenM]

Thanks, Paul. I tried that. It appears to be a new line character or return.

 

[Dave Peterson]

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"")
or
=substitute(a1,char(##)," ")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##), Chr(##)) '<--What showed up in CellView?

myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?

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

 

[EllenM]

Thanks, Dave. Your function code worked beautifully. I'll have to study the
macro part of your post. I'm glad I have a coworker who knows macros very
well.

Before I got your post, I tried saving as a web page, then open in a web
authoring software. Those breaks manifested as <br> tags which were easily
removable.

Ellen