Square Character in Excel (apostrophe)

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?

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.

Robmo wrote:

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?

--

Dave Peterson

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.



"Dave Peterson" wrote:

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.

Robmo wrote:

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?

--

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).

Robmo wrote:

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.

"Dave Peterson" wrote:

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.

Robmo wrote:

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?

--

Dave Peterson



--

Dave Peterson