If the only character that shows up in the formula bar is the apostrophe, then
you can clean these up with this technique:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

These apostrophe cells could be left behind because you had formulas that
evaluated to ="" and were converted to values.

As for the other stuff...

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"")
or
=substitute(a1,char(##)," ")

Replace ## 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(##), Chr(##)) '<--What showed up in CellView?

myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?

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

Eddie Ortiz wrote:

Hello Everyone,

I have question on how to remove chracters in my excel spreadsheet. I
exported my contract list from Outlook to an Excel spreadsheet and I need to
upload this data to a different database but I'm having problem because the
data i exported contains special characters. In the beginning of each cell
there is a character ' that shows up but only shows up in the formua bar
and the if cell is selected. also there is another character that show
randomly in each cell a thats looks like a box with a question mark inside
it.

I need to remove this characters before i can upload this data. I have tried
the Find and Replace feature with no success. I have also tried the CTRL-J
trick it eliminated the hard returns but not the special characters.

If there is anyone that can guide me, i sure would appreciate it. Thanks you
in advance.

--

Dave Peterson