how to I remove these odd squares from spreadsheet?

[Joe]

I created a .csv file from an old Palm Desktop application and ended
up with a bunch of these odd squares in many of the cells. I tried to
cut one and paste it into 'find/replace' but it doesn't show up. Aside
from deleting every one of them manually is there a way I can get rid
of them automatically?

Here's a link to a screenshot I took of a couple of cells

http://img176.imageshack.us/my.php?image=howtoeliminatesquareseu6.jpg

 

[Joel]

OPen the CSV file in Notepad. Use the replace. Copy one of the squares and
paste it into the from box in the replace window. Leave the two box blnak.
Save the file.

 

[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

 

[Joe]

OPen the CSV file in Notepad. Use the replace. Copy one of the squares and
paste it into the from box in the replace window. Leave the two box blnak.
Save the file.

The boxes don't show up in notepad, nor do they show up in Word. They
come up as paragraph marks. This is basically coming from the notes
section of my Palm Desktop program. I assume Excel can't recognize
them as carriage returns so they show up as squares.

 

[Joe]

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.

really not sure what you are talking about what is the "hex number"?
When I press Ctrl-j in the find option in notepad nothing happens.

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.

when I try this I get "the formula you typed contains a error

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

I think this one is way over my head, where to I place this macro?
Thanks for the help so far.

 

[Dave Peterson]

My suggestion to use ctrl-j depended on what those characters were--and it was
to be used in Excel, not notepad.

You didn't share the formula you tried, so I don't have a guess.

Try reading David McRitchie's notes first. It sounds like you skipped right
over that suggestion.

 

[Joel]

the character will display differently in Notepad than they do in Excel.
what you want to do is remove these characters. I don't think it is
important to find out what the characters are unless this problem occurs
often and it becomes difficult to remove.

I still think it will be easier to remove in Notepad. Simply hightlight the
one character in Notepad and copy. Then open the replace box and paste the
character in the from box.. Click on th to box and then press replace all.
Save the file when you are done.

 

[Ron Rosenfeld]

I created a .csv file from an old Palm Desktop application and ended
up with a bunch of these odd squares in many of the cells. I tried to
cut one and paste it into 'find/replace' but it doesn't show up. Aside
from deleting every one of them manually is there a way I can get rid
of them automatically?

Here's a link to a screenshot I took of a couple of cells

http://img176.imageshack.us/my.php?image=howtoeliminatesquareseu6.jpg

I assume you still want to have the carriage returns, and I am also assuming
that the characters you wish to keep are just the normal ASCII characters.

If so, try this:

After making a backup, select your range of cells, and run the macro below.

To enter the macro, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To Run the macro, <alt-F8> opens the macro dialog box. Select the REMnonASCII
macro and <RUN>.

======================
Option Explicit
Sub REMnonASCII()
Dim c As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "[^ -~]+"
For Each c In Selection
c.Value = re.Replace(c.Value, vbLf)
Next c
End Sub
==========================
--ron