Get rid of small squares in column
 

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

Get rid of small squares in column
 

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

HTH,
Paul

--

"EllenM" wrote in message
...
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

Get rid of small squares in column
 

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

Get rid of small squares in column
 

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 wrote:

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

--

Dave Peterson

Get rid of small squares in column
 

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