Square Character in Excel (apostrophe)

G

Guest

In coverting and copying data from the Internet into excel, we constantly have square characters appear in a number of places, but in particular in place of apostrophes. We tried copying and pastin ghe square into replace, but that does not work.

Is there a way to replace the squares, or do we simply need to change the character set somehow?
 
D

Dave Peterson

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 myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(255), Chr(123))
myGoodChars = Array("'", "a")

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

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

Guest

Dave...thanks for the tool to identify the characters, but is there a way to replace them automatically using find and replace. I can find them, but I do not want to go line by line to replace them.
 
D

Dave Peterson

It depends on the character you've found.

I can change the chr(10) (vblf) manually via:

Edit|Replace
what: Hit and hold the alt-key and type 0010 from the number keypad--not above
the QWERTY keys
replace with: (whatever you want)

But I can't do the same with chr(13) (vbCr).

But once you find those characters (and once you know what to replace them
with), you can just keep adding them to the list in the myBadChars and
myGoodChars array. Then run the macro.

Then save that macro for future use (don't recreate it each time).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top