Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to I remove these odd squares from spreadsheet?
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?i...squareseu6.jpg |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to I remove these odd squares from spreadsheet?
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. "Joe" wrote: 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?i...squareseu6.jpg |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to I remove these odd squares from spreadsheet?
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 wrote: 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?i...squareseu6.jpg -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to I remove these odd squares from spreadsheet?
On May 4, 2:57 am, Joel wrote:
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. "Joe" wrote: 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?i...squareseu6.jpg 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to I remove these odd squares from spreadsheet?
On May 4, 7:10 am, Dave Peterson wrote:
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to I remove these odd squares from spreadsheet?
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. Joe wrote: On May 4, 7:10 am, Dave Peterson wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to I remove these odd squares from spreadsheet?
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. "Joe" wrote: On May 4, 2:57 am, Joel wrote: 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. "Joe" wrote: 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?i...squareseu6.jpg 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to I remove these odd squares from spreadsheet?
On Sat, 3 May 2008 22:49:56 -0700 (PDT), Joe wrote:
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?i...squareseu6.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I remove a header in a Spreadsheet | Excel Discussion (Misc queries) | |||
How do I remove a constant from an excel spreadsheet? | Excel Discussion (Misc queries) | |||
can an excel spreadsheet be set to remove all hyperlinks | Excel Worksheet Functions | |||
Remove macros from spreadsheet | Excel Discussion (Misc queries) | |||
removing squares and lines in squares that really should be paragr | Excel Discussion (Misc queries) |