Removing Special characters in Excel

[Eddie Ortiz]

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]

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

 

[Ron Rosenfeld]

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.

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

 

[Annie]

Hi Eddie,
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.
Cheers, Annie

 

[Eddie Ortiz]

I was able to find out the code by isolating and typing =CODE(CELL#) and i
was able to use your formula =substitute. Much appreaciated.

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

 

[Eddie Ortiz]

Annie.. I was so excited to see this formula when you posted it.. but sorry
to say it didnt work.. it still shows up.

Column A1 Header = Fname A2 = 'Alan helper Cell =clean(A2) still shows 'Alan

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 doesnt
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 everytime you
export anything from outlook to an excel spreadsheet.

-Eddie O.

 

[Annie]

Hi Eddie - Try =trim(cell_address). If tha works, same copy down/ copy/ paste
special values. I usually use both trim and clean with database imported data.
Cheers, Annie

 

[Eddie Ortiz]

Thanks Annie, I figured out a way to do it, for some reason when i copied the
workseet and paste speical = values on a seperate workbook seem to have fix
the problem.
Thanks a bunch to everyone.

 

[Dave Peterson]

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?