Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
saved Excel as .txt then reopened - now I have squares
I have an Excel document that includes a list of e-mail addresses as well as
other data. This was saved as a .txt file. When reopened, the double quotes that were inserted by Notepad are apparently not recognized by Excel and so show up as squares. Not sure if Notepad separates columns using the double quotes, but it seems that way and so I don't have much control over it. Any thoughts about fixing this OR a way to resave so that the quotes don't show up to begin with?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
saved Excel as .txt then reopened - now I have squares
Could be the squares are linefeeds......which show as little squares until
you set the cells to wrap text. If you want to get rid of them, select all cells and EditReplace What: CTRL + j With: whatever you like.....nothing or space or??? Replace all. Or if you want to split the data at the linefeeds use DataText to columnsDelimited byOther.........Alt + 0010 Gord Dibben MS Excel MVP On Thu, 30 Apr 2009 11:20:16 -0700, MDG wrote: I have an Excel document that includes a list of e-mail addresses as well as other data. This was saved as a .txt file. When reopened, the double quotes that were inserted by Notepad are apparently not recognized by Excel and so show up as squares. Not sure if Notepad separates columns using the double quotes, but it seems that way and so I don't have much control over it. Any thoughts about fixing this OR a way to resave so that the quotes don't show up to begin with?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
saved Excel as .txt then reopened - now I have squares
I've never seen anything like this.
I wonder if those double quotes were actually smart quotes (curly style that MSWord uses). If so, it might be easier to just fix those characters by making a couple of changes inside NotePad. MDG wrote: I have an Excel document that includes a list of e-mail addresses as well as other data. This was saved as a .txt file. When reopened, the double quotes that were inserted by Notepad are apparently not recognized by Excel and so show up as squares. Not sure if Notepad separates columns using the double quotes, but it seems that way and so I don't have much control over it. Any thoughts about fixing this OR a way to resave so that the quotes don't show up to begin with?? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
saved Excel as .txt then reopened - now I have squares
I saved this from a previous post. You may find it useful:
Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.aspx Depending on what that character is, you may be able to use alt-#### (from the number keypad) to enter the character into the Other box in the text to columns wizard dialog. In fact, you may be able to select the character (in the formula bar), and copy it. Then use ctrl-v to paste into that text to columns Other box. 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(##),"") 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(##)) '<--What showed up in CellView? myGoodChars = Array("") 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: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) MDG wrote: I have an Excel document that includes a list of e-mail addresses as well as other data. This was saved as a .txt file. When reopened, the double quotes that were inserted by Notepad are apparently not recognized by Excel and so show up as squares. Not sure if Notepad separates columns using the double quotes, but it seems that way and so I don't have much control over it. Any thoughts about fixing this OR a way to resave so that the quotes don't show up to begin with?? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get rid of squares substituting as spaces in excel? | Excel Worksheet Functions | |||
Excel cells change color after reopened | Excel Discussion (Misc queries) | |||
Hyperlinks in Excel database don't work when file is reopened. | Excel Discussion (Misc queries) | |||
csv format drops a text character when reopened in Excel | Excel Worksheet Functions | |||
removing squares and lines in squares that really should be paragr | Excel Discussion (Misc queries) |