How do I remove "special characters" from cell data?

i exported data from another application. Cells now contain some unusual
line return characters (some appear as boxes, others as really dark |
characters). My version of Excel 2003 does not have the ability to find and
replace these characters. Any suggestions on how I can remove these? My
ultimate goal is to parse the data using TEXT TO COLUMNS.
Thanks.
Cal

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.

Cal wrote:
>
> i exported data from another application. Cells now contain some unusual
> line return characters (some appear as boxes, others as really dark |
> characters). My version of Excel 2003 does not have the ability to find and
> replace these characters. Any suggestions on how I can remove these? My
> ultimate goal is to parse the data using TEXT TO COLUMNS.
> Thanks.
> Cal

--

Dave Peterson

I have the same problem...
someone gave me a spreadsheet mailing list for me to make a mailing for them.
It's got "boxes" in the street address column.
At the top the address breaks into 2 lines but in the cell it's one line.
So I'm guessing that the box indicates an "enter".
If I change the cell format to "Wrap Text" the cell becomes 2 lines...so far
so good.
But, when I do mail merge in Word the address comes up as:
123 any street "BOX" PO box 123.
Help!
Thanks,
Frank

"Dave Peterson" wrote:

> 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.
>
> Cal wrote:
> >
> > i exported data from another application. Cells now contain some unusual
> > line return characters (some appear as boxes, others as really dark |
> > characters). My version of Excel 2003 does not have the ability to find and
> > replace these characters. Any suggestions on how I can remove these? My
> > ultimate goal is to parse the data using TEXT TO COLUMNS.
> > Thanks.
> > Cal
>
> --
>
> Dave Peterson
>

I think I'd use multiple columns to store the data.

If that's acceptible, you could select the column
data|text to columns
delimited
other: type ctrl-j (that's the alt-enter character)

Then use those multiple fields in your mail merge.

Frank wrote:
>
> I have the same problem...
> someone gave me a spreadsheet mailing list for me to make a mailing for them.
> It's got "boxes" in the street address column.
> At the top the address breaks into 2 lines but in the cell it's one line.
> So I'm guessing that the box indicates an "enter".
> If I change the cell format to "Wrap Text" the cell becomes 2 lines...so far
> so good.
> But, when I do mail merge in Word the address comes up as:
> 123 any street "BOX" PO box 123.
> Help!
> Thanks,
> Frank
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> > Cal wrote:
> > >
> > > i exported data from another application. Cells now contain some unusual
> > > line return characters (some appear as boxes, others as really dark |
> > > characters). My version of Excel 2003 does not have the ability to find and
> > > replace these characters. Any suggestions on how I can remove these? My
> > > ultimate goal is to parse the data using TEXT TO COLUMNS.
> > > Thanks.
> > > Cal
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson