Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
removing char(13) with find/replace
I have found that char(10) is the equivalent of ctrl-j in the find/replace
box. Is there an equivalent way of finding char(13)? Thanks. -- Boris |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
removing char(13) with find/replace
I can't get anything to work manually.
So I use a macro: Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(13)) 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 ======= If you're working with lots of those funny characters, you may want to get Chip Pearson's addin: http://www.cpearson.com/excel/CellView.htm Then if you have lots of characters to fix, you can loop through them: 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 BorisS wrote: I have found that char(10) is the equivalent of ctrl-j in the find/replace box. Is there an equivalent way of finding char(13)? Thanks. -- Boris -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND 1 char in cell of any 3 char =True | Excel Discussion (Misc queries) | |||
How to substitute a comma with a newline char using Replace. | Excel Discussion (Misc queries) | |||
Using replace box to change a character into a CR (Alt-Enter char) | Excel Discussion (Misc queries) | |||
8500 cells with phone number(7 char.), wishing to add area code (10 char.) | Excel Discussion (Misc queries) | |||
Find if nth char = specific character.. | Excel Discussion (Misc queries) |