Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete a "return" character (a "box") in cell of imported d
When I download contact address information from Outlook to an XL file, many
times there appears a square 'box' character in the cell where either (a) a comma would have been separating street from say, suite # in the same line or (b) where a return would have been (street address <return city/state/zip) in the address card (and sometimes more than one "box" for multiple returns). I have tried to search and replace to change or delete the character, but I am unable to grab it, and thus have to manually adjust each cell. A daunting task with 4000+ entries.... The "box" character only appears in the formula field bar -- when viewing the cell in the sheet, it appears as two vertical lines (like a double lower case L). What is this character called how do I get rid of it globally in the sheet ??? Thx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete a "return" character (a "box") in cell of imported d
Chip Pearson has a very nice addin that will help determine what that
character(s) is: http://www.cpearson.com/excel/CellView.aspx 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: 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.) JaxHBT wrote: When I download contact address information from Outlook to an XL file, many times there appears a square 'box' character in the cell where either (a) a comma would have been separating street from say, suite # in the same line or (b) where a return would have been (street address <return city/state/zip) in the address card (and sometimes more than one "box" for multiple returns). I have tried to search and replace to change or delete the character, but I am unable to grab it, and thus have to manually adjust each cell. A daunting task with 4000+ entries.... The "box" character only appears in the formula field bar -- when viewing the cell in the sheet, it appears as two vertical lines (like a double lower case L). What is this character called how do I get rid of it globally in the sheet ??? Thx -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Help removing "space" character from imported data | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |