Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to replace an unrecognised character
I am importing data to Excel from a flat file - and the rows being imported
contain carraige returns. I can not remove these in the MS excel import process and these then appear in the EXcel celss as what I believe is an unrecognised character symbol - a ? inside a square. I need to separate the contents of these celss based on this symbol but I have not found a solution to do this. of the Text function I can use Clean to remove the symbol but I then lose the ability to separate the contents at the correect position. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to replace an unrecognised character
in the data tab have you tried "Text to Columns"? if not, try that with tab
delimited as the selection. "Conore" wrote: I am importing data to Excel from a flat file - and the rows being imported contain carraige returns. I can not remove these in the MS excel import process and these then appear in the EXcel celss as what I believe is an unrecognised character symbol - a ? inside a square. I need to separate the contents of these celss based on this symbol but I have not found a solution to do this. of the Text function I can use Clean to remove the symbol but I then lose the ability to separate the contents at the correect position. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to replace an unrecognised character
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.aspx Depending on what that character is, you may be able to use alt-#### (from the number keypad) to enter the character into the Other box in the text to columns wizard dialog. In fact, you may be able to select the character (in the formula bar), and copy it. Then use ctrl-v to paste into that text to columns Other box. 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(##),"") 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(##)) '<--What showed up in CellView? myGoodChars = Array("") 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.) Conore wrote: I am importing data to Excel from a flat file - and the rows being imported contain carraige returns. I can not remove these in the MS excel import process and these then appear in the EXcel celss as what I believe is an unrecognised character symbol - a ? inside a square. I need to separate the contents of these celss based on this symbol but I have not found a solution to do this. of the Text function I can use Clean to remove the symbol but I then lose the ability to separate the contents at the correect position. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Character search and replace | Excel Worksheet Functions | |||
Replace nonprinting character | Excel Worksheet Functions | |||
replace new line character | Excel Discussion (Misc queries) | |||
How do I replace a BEL Character of 7 with a space | Excel Discussion (Misc queries) | |||
How do I replace a character with another? | Excel Discussion (Misc queries) |