Shaz
Try EditReplace
What: Alt + 0010 on the numpad
With: space
Replace all.
As far as seeing what characters are in a cell, download Chip Pearson's CellView
add-in.
http://www.cpearson.com/excel/download.htm
If the character is 0013 then you will need a macro to replace with a space.
This covers them all.
Sub Remove_CR_LF()
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub
Gord Dibben MS Excel MVP
On 21 Feb 2007 13:52:17 -0800, "shaz" wrote:
I have text with embedded non-printable characters through out a large
worksheet of data.
For example,
Collected $98 incash, $5checks 4/25/02 Moved, left no addres
If I use CLEAN(), it works but leaves me without a space...I could use
substitute, but I don't know what ascii character this is?
Has someone written a little macro that goes through the text and
prints out each characters acsii code? I've tried doing it manually
with ASCII(text) but haven't been able to use that either. In
addition, these characters are embedded all over the spreadsheet and I
have no way of knowing if it is the same np character or multiple np
characters.