Odd character

  • Thread starter Thread starter David French
  • Start date Start date
D

David French

I have a file that has been exported from Outlook to an Excel file.
In some of the address fields the Address1 and Address2 lines are separated
by some character that does not seem to be recognized by Excel.
In the cell it looks like a 'pipe' character that is bold...sometimes 2 of
them.
In the Formula Bar it appears to be a small box. It also pushes this
Address2 to a second line in the formula bar.
I need to import this to another program but that program chokes on this
character.

Can anyone help me to clean this out of the data?

Dave French
 
Looks like you might have paragraph marks there.
Can you copy one and paste in edit>replace and replace it with nothing? The
only other way I could think of would be to use Word and replace them
replace ^p with nothing

Regards,

Peo Sjoblom
 
Get a copy of Chip Pearson's CellView addin:
http://www.cpearson.com/excel/CellView.htm

And it'll tell you what those characters are.

Then you could run a macro that cleans up those characters:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(yy), Chr(zz))

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub

Replace yy with the codes you got from Chip's addin. If you only have one
character to clean up, then this:

myBadChars = Array(Chr(yy), Chr(zz))
should look more like:

myBadChars = Array(Chr(yy))

and you might want: Replacement:="" to be: Replacement:=" " (space character).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
To all that supplied an answer:

Thank you all for your input to my dilema.
I did d/l and install the Cell View add-in. I can see where it may be
helpful but in this case it showed up as a 'Special Character'.
This really wasn't much help.

What I ended up doing is taking that column of cells and pasting it into
Word. From there I did a Show All Characters and it ended up being
recognized as a 2 'soft returns' or a Manual Line Breaks.
All I needed to do then was do a find and replace on 2 consecutive line
breaks and replace with a Tab.
Pasting that back into Excel as TEXT automatically created the second column
with the information separated as needed.

Gotta LOVE working with data!!!
I also love these newsgroups. They are a never ending source of new
information!!

Gratefully yours,
Dave French



VENKAT said:
see this url
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
for this you also nee cell view addin and see this url
http://www.cpearson.com/excel/CellView.htm

this is for trimming the cell from odd nonprinable characters wwhich is
normally present in webpages.
=======================
Click to expand...
 
If you used Chip's CellView addin, you could see what the offending characters
are--not just that they were special.

In Chip's form, there's a line that shows the decimal/hexidecimal value for each
character.

(right above the Special row)

David said:
To all that supplied an answer:

Thank you all for your input to my dilema.
I did d/l and install the Cell View add-in. I can see where it may be
helpful but in this case it showed up as a 'Special Character'.
This really wasn't much help.

What I ended up doing is taking that column of cells and pasting it into
Word. From there I did a Show All Characters and it ended up being
recognized as a 2 'soft returns' or a Manual Line Breaks.
All I needed to do then was do a find and replace on 2 consecutive line
breaks and replace with a Tab.
Pasting that back into Excel as TEXT automatically created the second column
with the information separated as needed.

Gotta LOVE working with data!!!
I also love these newsgroups. They are a never ending source of new
information!!

Gratefully yours,
Dave French
Click to expand...
 
frm cellview if you find chr(160) is the offending item you can remoe it
by using this code

With ActiveSheet.UsedRange
..Replace what:=Chr(160), replacement:=Chr(32)
End With

chr(32) is space.

in one case sometime back I even tried

replacemen:=chr(0) instead of chr(32)
=================================



To all that supplied an answer:

Thank you all for your input to my dilema.
I did d/l and install the Cell View add-in. I can see where it may be
helpful but in this case it showed up as a 'Special Character'.
This really wasn't much help.

What I ended up doing is taking that column of cells and pasting it into
Word. From there I did a Show All Characters and it ended up being
recognized as a 2 'soft returns' or a Manual Line Breaks.
All I needed to do then was do a find and replace on 2 consecutive line
breaks and replace with a Tab.
Pasting that back into Excel as TEXT automatically created the second
column
with the information separated as needed.

Gotta LOVE working with data!!!
I also love these newsgroups. They are a never ending source of new
information!!

Gratefully yours,
Dave French
Click to expand...
 
the code in my previous message is from mcritchies trimall code to which
the url has been given in one of the earlier messages


To all that supplied an answer:

Thank you all for your input to my dilema.
I did d/l and install the Cell View add-in. I can see where it may be
helpful but in this case it showed up as a 'Special Character'.
This really wasn't much help.

What I ended up doing is taking that column of cells and pasting it into
Word. From there I did a Show All Characters and it ended up being
recognized as a 2 'soft returns' or a Manual Line Breaks.
All I needed to do then was do a find and replace on 2 consecutive line
breaks and replace with a Tab.
Pasting that back into Excel as TEXT automatically created the second
column
with the information separated as needed.

Gotta LOVE working with data!!!
I also love these newsgroups. They are a never ending source of new
information!!

Gratefully yours,
Dave French
Click to expand...
 
Back
Top