Imported data has character that cannot be replaced

[Ssolano]

Imported a list into Excel
The column with notes has a character that represents
a "return"
It displays as a box
We tried a find and replace
It does not remove the character
How can we remove the return graphic in a cell?

 

[Bernie Deitrick]

S,

First find out what the character is. If it is in the 10th position in cell
A1, this formula will tell you what it is

=CODE(MID(A1,10,1))

Once you have done that, simply replace the (10) in the code below with that
number, and run the macro. Not sure what you want to replace it with, but
assumed a space.

Sub MyReplace()
Cells.Replace What:="" & Chr(10) & "", _
Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

HTH,
Bernie
MS Excel MVP

 

[David McRitchie]

Hi > "Ssolano" <[email protected]> ,
Ok to replace with nothing if it is on the front or back end
of a cell but you probably don't want to do that if the character
is elsewhere. From what you indicate, and not seeing a
square bullet, I expect you you have a CHAR(160) which
in HTML is &nbsp; (nonbreaking space). See TRIMALL
macro in
Rearranging Data in Columns
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Think modular
http://images.ucomics.com/comics/tmntf/2004/tmntf040514.gif