Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get rid of small squares in column
Thanks, Paul. I tried that. It appears to be a new line character or return.
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
getting small cirlces and squares when i click on cells | Excel Discussion (Misc queries) | |||
ISERROR,SMALL,INDEX, MATCH, SMALL?? | Excel Discussion (Misc queries) | |||
chi squares | Excel Discussion (Misc queries) | |||
removing squares and lines in squares that really should be paragr | Excel Discussion (Misc queries) | |||
Small font on row & column numbers - wierd | Excel Discussion (Misc queries) |