Removing Special characters in Excel
 

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.

Answer: Removing Special characters in Excel
 

Hi there! I can definitely help you with removing special characters in Excel.

First, let's address the character that appears at the beginning of each cell. This is most likely an apostrophe, which is Excel's way of indicating that the data in the cell is text. To remove this character, you can use the following steps:

1. Select the column that contains the data with the apostrophe.
2. Click on the "Data" tab in the ribbon.
3. Click on "Text to Columns" in the "Data Tools" section.
4. In the "Convert Text to Columns Wizard" window, select "Delimited" and click "Next."
5. Uncheck all of the delimiter options and click "Next."
6. In the "Column data format" section, select "General" and click "Finish."

This should remove the apostrophe from the beginning of each cell.

Now, let's address the random box with a question mark inside it. This is most likely a special character that is not recognized by the database you are trying to upload the data to. To remove this character, you can use the following steps:

1. Select the column that contains the data with the special character.
2. Click on the "Home" tab in the ribbon.
3. Click on the "Find & Select" dropdown and select "Replace."
4. In the "Find what" field, enter the special character. You can do this by copying the character from one of the cells and pasting it into the field.
5. Leave the "Replace with" field blank.
6. Click "Replace All."

This should remove all instances of the special character from the selected column.

Removing Special characters in Excel
 

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

Removing Special characters in Excel
 

On Tue, 12 Feb 2008 07:49:02 -0800, 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.

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

Removing Special characters in Excel
 

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

Removing Special characters in Excel
 

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.

"Dave Peterson" wrote:

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

Removing Special characters in Excel
 

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" wrote:

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

Removing Special characters in Excel
 

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" wrote:

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" wrote:

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

Removing Special characters in Excel
 

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.

"Annie" wrote:

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" wrote:

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" wrote:

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

Removing Special characters in Excel
 

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?

Eddie Ortiz wrote:

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.

"Dave Peterson" wrote:

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


--

Dave Peterson