Replacing ascii characters

I have imported information from Access where one of the fields contains
manual line breaks. It appears Excel converts the manual line break into a
square box. I would like to do a find and replace on these square boxes but
am having a hard time figuring out what code to use. I am thinking the ascii
code is either 10 or 13 for the mlb. So for the find field what would you
type? I have tried CHAR(10) and CHAR(13) but when it searches it says it
can't find the data I am searching for. I know I was able to accomplish this
a year or so ago but have since forgotten how to do it. Anyone have a
suggestion?

In general, if you are searching for symbols within an Excel file, how do
you indicate the ascii code in the "find what" field?

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(9), Chr(160)) '<--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
(alt-enters) work ok. char(13) have never worked for me.

HappyCamper wrote:
>
> I have imported information from Access where one of the fields contains
> manual line breaks. It appears Excel converts the manual line break into a
> square box. I would like to do a find and replace on these square boxes but
> am having a hard time figuring out what code to use. I am thinking the ascii
> code is either 10 or 13 for the mlb. So for the find field what would you
> type? I have tried CHAR(10) and CHAR(13) but when it searches it says it
> can't find the data I am searching for. I know I was able to accomplish this
> a year or so ago but have since forgotten how to do it. Anyone have a
> suggestion?
>
> In general, if you are searching for symbols within an Excel file, how do
> you indicate the ascii code in the "find what" field?

--

Dave Peterson

HappyC,

Select the cells, then try this little macro, which will replace characters 10 and
13 with spaces.

HTH,
Bernie
MS Excel MVP


Sub ReplaceReturns()
Selection.Replace Chr(10), " ", xlPart
Selection.Replace Chr(13), " ", xlPart
End Sub





"HappyCamper" <[email protected]> wrote in message
news:[email protected]...
>I have imported information from Access where one of the fields contains
> manual line breaks. It appears Excel converts the manual line break into a
> square box. I would like to do a find and replace on these square boxes but
> am having a hard time figuring out what code to use. I am thinking the ascii
> code is either 10 or 13 for the mlb. So for the find field what would you
> type? I have tried CHAR(10) and CHAR(13) but when it searches it says it
> can't find the data I am searching for. I know I was able to accomplish this
> a year or so ago but have since forgotten how to do it. Anyone have a
> suggestion?
>
> In general, if you are searching for symbols within an Excel file, how do
> you indicate the ascii code in the "find what" field?