Help! Can't get rid of hidden characters
 

I'm doing a mail merge from my Excel data...

When I had my letters and envelopes printed this week, I noticed that a
character had been repeated and inserted at the end of many of my
entries, a "ÿ" (a "y" with two dots over it.) I looked in my Excel
file and couldn't see any of these hidden characters. When I looked at
specific fields I knew had added the character, I saw a blank space
after the words and then a blinking cursor.

My Excel sheet has over 3,000 lines so going one by one to look for the
blank space and the cursor manually isn't going to be possible.

In some places there is only one or two ÿ's added to the ends and in
others there are maybe ten or eleven! I tried doing a Cntrl-F and
entered in the ÿ to search/replace, but it yielded no results.

HELP! The printing company handling my mail merge said that for my
next mailing they have a machine that could "detect odd characters" but
that it would take three hours and "may not work completely". For
which, of course, they wanted to charge me $200!

Any suggestions you have would be greatly appreciated. Thank you!


--
tragopanic
------------------------------------------------------------------------
tragopanic's Profile: http://www.excelforum.com/member.php...o&userid=25210
View this thread: http://www.excelforum.com/showthread...hreadid=386959

You can use Chip Pearson's Cell View addin to find out the character it is:
http://www.cpearson.com/excel/CellView.htm

If those box characters are char(10)'s (alt-enters), you can use
edit|Replace
what: ctrl-j
with: (spacebar??)
replace all

If that box character is something else, you may need a macro:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(13)) '<--What showed up in CellView?

myGoodChars = Array(" ")

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

tragopanic wrote:

I'm doing a mail merge from my Excel data...

When I had my letters and envelopes printed this week, I noticed that a
character had been repeated and inserted at the end of many of my
entries, a "ÿ" (a "y" with two dots over it.) I looked in my Excel
file and couldn't see any of these hidden characters. When I looked at
specific fields I knew had added the character, I saw a blank space
after the words and then a blinking cursor.

My Excel sheet has over 3,000 lines so going one by one to look for the
blank space and the cursor manually isn't going to be possible.

In some places there is only one or two ÿ's added to the ends and in
others there are maybe ten or eleven! I tried doing a Cntrl-F and
entered in the ÿ to search/replace, but it yielded no results.

HELP! The printing company handling my mail merge said that for my
next mailing they have a machine that could "detect odd characters" but
that it would take three hours and "may not work completely". For
which, of course, they wanted to charge me $200!

Any suggestions you have would be greatly appreciated. Thank you!

--
tragopanic
------------------------------------------------------------------------
tragopanic's Profile: http://www.excelforum.com/member.php...o&userid=25210
View this thread: http://www.excelforum.com/showthread...hreadid=386959

--

Dave Peterson