Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find special characters
DOes anyone know how to use Find or Replace where the search criteria needs
to include special characters that are not vixible. For example, text imported from another source has Paragraph or Line Feed characters embedded in it. These characters are not visible in the text. How can I use find/replace to replace them with something like a space. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
find special characters
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. Joe Ventre wrote: DOes anyone know how to use Find or Replace where the search criteria needs to include special characters that are not vixible. For example, text imported from another source has Paragraph or Line Feed characters embedded in it. These characters are not visible in the text. How can I use find/replace to replace them with something like a space. Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 'Special' Characters in Expressions | Excel Worksheet Functions | |||
Converting special characters | Excel Discussion (Misc queries) | |||
REMOVE SPECIAL CHARACTERS FROM TEXT CELLS | Excel Worksheet Functions | |||
Special Characters in Headers and Footers | Excel Discussion (Misc queries) | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) |