How to Remove special characters from Microsoft Excel

[Celestial]

I have question on how to remove characters 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 formula bar and the if cell is selected. also there is another character that show randomly in each cell a that's 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.

[Bontu]

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. Cpearson has a very nice addin that will help determine what that character(s). 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

[Erakna]

The way you describe it, you should be able to ignore the " ' " that you only see in the formula bar. That is not really part of the cell contents. It is a label prefix that indicates the cell contains left-aligned text. For the other special character, assuming there is just one or two, you can copy/paste that character into the Find what: line of the Find and Replace dialog box; replace it with nothing. If things are more complicated, we could easily write a macro that would filter out all the bad stuff.

[Shurod]

I have good luck with =clean(cell_address) Create a new column. Use formula above, referencing cell with odd characters. Copy formula down. Select column with clean formula - copy/paste special/values. Use this column as good data column if "clean" works.

[Gania]

I was able to find out the code by isolating and typing =CODE(CELL#) and i was able to use your formula =substitute. Much appreciated. I am still having issue with the ' in the beginning of each cell. Problem is when i convert this to a csv file it also shows up. Is there another way to get rid of this character.

[Alfons]

I was so excited to see this formula when you posted it.. but sorry to say it didn't work.. it still shows up. Column A1 Header = Fname A2 = 'Alan helper Cell =clean(A2) still shows, the problem is the character shows when i export this spreadsheet into a csv file but when it exports the character as box which i cant do a FIND/Replace for it and i need to upload it to another database it doesn't accept this character. Thanks for the help thus far. Ill continue searching the the board maybe someone has come across same problem. apparently this is normal every time you export anything from outlook to an excel spreadsheet. If that works, same copy down/ copy/ paste special values. I usually use both trim and clean with database imported data.

[JustineBB]

I figured out a way to do it, for some reason when i copied the worksheet and paste special = values on a separate workbook seem to have fix the problem. You found a solution, but if it happens again. Test the cell to see what else is in it: =len(a1). If you see the apostrophe, but =len() returns 0, then try that edit|replace once more. The only time I've seen it fail is when someone actually uses the apostrophe to force a text value. Did you do that?