Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab character is messing me up
I imported some data from one an excel file on one of our company web sites.
When I look at it everything looks fine. When my boss looks at it he sees characters shaped like little boxes where I see blank spaces. I copied the blank spaces and pasted them into Word and I can see the arrows you'd see for tabs in a Word document if you selected View Paragraph marks. How can I replace these with blank space in the Excel file? Why can my boss see them in excel but I can't? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab character is messing me up
The little boxes could be carriage returns.
To get rid of then do an EditReplace What: ALT + 0100(from the numpad, not the regular keys) With: space or nothing Replace all. If your boss set his cells to "wrap text" he would not see the little boxes. If you want to see them, uncheck "wrap text" Gord Dibben MS Excel MVP On Tue, 27 Jun 2006 14:42:52 -0500, "Some Dude" wrote: I imported some data from one an excel file on one of our company web sites. When I look at it everything looks fine. When my boss looks at it he sees characters shaped like little boxes where I see blank spaces. I copied the blank spaces and pasted them into Word and I can see the arrows you'd see for tabs in a Word document if you selected View Paragraph marks. How can I replace these with blank space in the Excel file? Why can my boss see them in excel but I can't? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab character is messing me up
It's not carriage return. Know were I might find a table of all the other
symbols? "Gord Dibben" <gorddibbATshawDOTca wrote in message ... The little boxes could be carriage returns. To get rid of then do an EditReplace What: ALT + 0100(from the numpad, not the regular keys) With: space or nothing Replace all. If your boss set his cells to "wrap text" he would not see the little boxes. If you want to see them, uncheck "wrap text" Gord Dibben MS Excel MVP On Tue, 27 Jun 2006 14:42:52 -0500, "Some Dude" wrote: I imported some data from one an excel file on one of our company web sites. When I look at it everything looks fine. When my boss looks at it he sees characters shaped like little boxes where I see blank spaces. I copied the blank spaces and pasted them into Word and I can see the arrows you'd see for tabs in a Word document if you selected View Paragraph marks. How can I replace these with blank space in the Excel file? Why can my boss see them in excel but I can't? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab character is messing me up
Saved from a previous post:
Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm Since you do see a box, then you can either fix it via a helper cell or a macro: =substitute(a1,char(13),"") or =substitute(a1,char(13)," ") Replace 13 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(10), Chr(13)) '<--What showed up in CellView? myGoodChars = Array(" "," ") '<--what's the new character? 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 ------- Sometimes those funny characters don't work in the edit|Find dialog. alt-0010 (or ctrl-j) (aka: alt-enters) work ok. char(13) has never worked for me. Some Dude wrote: It's not carriage return. Know were I might find a table of all the other symbols? "Gord Dibben" <gorddibbATshawDOTca wrote in message ... The little boxes could be carriage returns. To get rid of then do an EditReplace What: ALT + 0100(from the numpad, not the regular keys) With: space or nothing Replace all. If your boss set his cells to "wrap text" he would not see the little boxes. If you want to see them, uncheck "wrap text" Gord Dibben MS Excel MVP On Tue, 27 Jun 2006 14:42:52 -0500, "Some Dude" wrote: I imported some data from one an excel file on one of our company web sites. When I look at it everything looks fine. When my boss looks at it he sees characters shaped like little boxes where I see blank spaces. I copied the blank spaces and pasted them into Word and I can see the arrows you'd see for tabs in a Word document if you selected View Paragraph marks. How can I replace these with blank space in the Excel file? Why can my boss see them in excel but I can't? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab character is messing me up
Typo patrol...........should be 0010(not 0100) but if a Tab
character......................... Try this formula for Tab CHAR(9) =substitute(a1,char(9)," ") A handy add-in to have is Chip Pearson's CELLVIEW.XLA http://www.cpearson.com/excel/CellView.htm To get a listing of codes for characters enter in A1 of a new sheet =CHAR(ROW()) Drag/copy down 256 rows. Gord On Tue, 27 Jun 2006 16:13:02 -0500, "Some Dude" wrote: It's not carriage return. Know were I might find a table of all the other symbols? "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . The little boxes could be carriage returns. To get rid of then do an EditReplace What: ALT + 0100(from the numpad, not the regular keys) With: space or nothing Replace all. If your boss set his cells to "wrap text" he would not see the little boxes. If you want to see them, uncheck "wrap text" Gord Dibben MS Excel MVP On Tue, 27 Jun 2006 14:42:52 -0500, "Some Dude" wrote: I imported some data from one an excel file on one of our company web sites. When I look at it everything looks fine. When my boss looks at it he sees characters shaped like little boxes where I see blank spaces. I copied the blank spaces and pasted them into Word and I can see the arrows you'd see for tabs in a Word document if you selected View Paragraph marks. How can I replace these with blank space in the Excel file? Why can my boss see them in excel but I can't? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mystery ALT key character illudes all methods of extraction! HELP! | Excel Discussion (Misc queries) | |||
Odd! Excel changes my letter character. Why? | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Count occurrence of character within a cell | Excel Discussion (Misc queries) | |||
Removing ' character from cells | Excel Discussion (Misc queries) |