Strange Character

  • Thread starter Thread starter bw
  • Start date Start date
B

bw

I'm trying to write some code to delete a character in a text file. The character appears
as a square "O", and when it is copied to MS Word, it turns out to be "^m" for a manual
page break that I can find, edit and replace.

So the question is, what is that character in Excel, and how can I do and find and
replace on it?

If you need more information, please let me know.

Thanks,
Bernie
 
Chip Pearson has a very nice addin that can help identify those funny
characters.

http://www.cpearson.com/excel/CellView.htm

If you have to do this lots of times, I'd record a macro when you do an
Edit|Replace and modify it to do those funny characters:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13))

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

End Sub

Just keep adding those Hex codes you found using Chip's addin.

(I changed the funny characters to "". You could use " " if you wanted a
space.)

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

========
With some of those funny characters (alt-enters or alt-0010), you can actually
use edit|replace.

Edit|replace
hit and hold the alt key while typing 0010 (from the number keypad--not above
QWERTY).

It may look like nothing got entered in that little box, but try it.

And if this is an alt-enter, you may just want to change the text wrap.
Format|Cells|alignment tab.

It's a way of entering more than one line inside a cell.
 
Hi Chip, thanks for the help.

Well, I downloaded the addin (pretty cool), and I see that the funny character in position
1 of cell A1 is Dec 012 and Hex 0C.

But that's as far as I get. In your procedure cleanEmUp, I put 0C between the quote
marks after Replacement...that didn't work.

So I need some more help as to how to proceed. This same character will appear many
times in many different files, so I need a way to automate deleting them, or replacing with
a space.

Thanks again,
Bernie
 
Excuse me...I mean thanks DAVE (not Chip)...


Chip Pearson has a very nice addin that can help identify those funny
characters.

http://www.cpearson.com/excel/CellView.htm

If you have to do this lots of times, I'd record a macro when you do an
Edit|Replace and modify it to do those funny characters:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13))

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

End Sub

Just keep adding those Hex codes you found using Chip's addin.

(I changed the funny characters to "". You could use " " if you wanted a
space.)

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

========
With some of those funny characters (alt-enters or alt-0010), you can actually
use edit|replace.

Edit|replace
hit and hold the alt key while typing 0010 (from the number keypad--not above
QWERTY).

It may look like nothing got entered in that little box, but try it.

And if this is an alt-enter, you may just want to change the text wrap.
Format|Cells|alignment tab.

It's a way of entering more than one line inside a cell.
Click to expand...
 
I'm not offended--not sure about Chip, though!

Change this line to the hex codes you want to clean up:

myBadChars = Array(Chr(10), Chr(13))

with just one funny character:
myBadChars = Array(Chr(12))
 
Thanks Dave,

I sent you another message but it's not on the newsgroup for some reason.

It's working now, just like I wanted, but I still don't understand. I used the "myBadChars =
Array(Chr(12))" value but can't see how this does what it does. VB Help doesn't help me
either. Somehow you knew this was the character that was causing me the problem???

In any case, I appreciate your help. Thanks again.
Bernie
 
Here's the code:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13))

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

End Sub

the For/next loop goes through each element in the array named myBadChars.

Kind of a lather, rinse, repeat, but lathering until you run out of shampoo (or
bad characters).

In your example, the array consists of one element. You really didn't need to
loop at all:

Option Explicit
Sub cleanEmUp()
ActiveSheet.Cells.Replace What:=chr(12), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

Would have been sufficient. But, heck. If you ever run into more than one bad
character (maybe an alley?), you can run that original code and get rid of them
all.

(I think that's how the Guardian Angels started <bg>.)


Thanks Dave,

I sent you another message but it's not on the newsgroup for some reason.

It's working now, just like I wanted, but I still don't understand. I used the "myBadChars =
Array(Chr(12))" value but can't see how this does what it does. VB Help doesn't help me
either. Somehow you knew this was the character that was causing me the problem???

In any case, I appreciate your help. Thanks again.
Bernie
Click to expand...
 
Back
Top