Removing ' character from cells
 

In some Excel files, both native and those created with data imported from a
tab delimited file, many or all of the cells are populated with the ' (as is)
character. This presents a problem when I would like to have cells formatted
in a specific manner (i.e. dates/double integer). I have tried to
search/replace for the character to no avail. Does anyone have a surefire
way of removing this "exact" character enmasse?

Thanks,
Don

TTC - Text To Columns!

Select the data, then simply open and close TTC.

Select the data (in a single row),
<Data <TextToColumns <Finish
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Don" wrote in message
...
In some Excel files, both native and those created with data imported from
a
tab delimited file, many or all of the cells are populated with the ' (as
is)
character. This presents a problem when I would like to have cells
formatted
in a specific manner (i.e. dates/double integer). I have tried to
search/replace for the character to no avail. Does anyone have a surefire
way of removing this "exact" character enmasse?

Thanks,
Don

Unfortunately, this has no effect on the column (or row, depending on which
is selected). To clarify:

Cell A1 is: 'Field1

By selecting Data / Text to Columns / Finish, nothing changes.

Nor does the content of the cell(s) change when selecting a different Text
Qualifier (changing from the default " to ').

Any other ideas? These characters are a result of exporting from Access to
Excel. Very annoying. As mentioned, I also have them in other Excel files,
and simply want to remove them all.

"Ragdyer" wrote:

TTC - Text To Columns!

Select the data, then simply open and close TTC.

Select the data (in a single row),
<Data <TextToColumns <Finish

Removing it should be easy ... *BUT* ... you first have to identify it.
Try:
=CODE(A1)
Where A1 is a cell containing "contaminated" data.

This will return the Char() number for the *first* character in the cell.
If it's *other* then the Char() number of the first alpha/numeric character
in the cell, you then have the I.D. of your culprit.

You can then use "Edit / Replace" to eliminate it, inserting the *four*
digit Char() number in the "FindWhat" box, and leaving the "Replace With"
box empty.
Don't forget that the Char() number must be entered using,
<Alt + numbers from the num keypad, *not* the numbers under the function
keys.

Also, if there's a chance that the "bad" character is not the first one in
the cell, you could go down the line, testing each character in the cell
using:
=CODE(MID(A1,1,1))
=CODE(MID(A1,2,1))
=CODE(MID(A1,3,1))
....etc...
Until you come to a Char() number that doesn't match the appropriate
alpha/numeric character.
Don't forget to go a space *past* the last visible character, since
invisible characters might be appended to the data.

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Don" wrote in message
...
Unfortunately, this has no effect on the column (or row, depending on which
is selected). To clarify:

Cell A1 is: 'Field1

By selecting Data / Text to Columns / Finish, nothing changes.

Nor does the content of the cell(s) change when selecting a different Text
Qualifier (changing from the default " to ').

Any other ideas? These characters are a result of exporting from Access to
Excel. Very annoying. As mentioned, I also have them in other Excel
files,
and simply want to remove them all.

"Ragdyer" wrote:

TTC - Text To Columns!

Select the data, then simply open and close TTC.

Select the data (in a single row),
<Data <TextToColumns <Finish

You can use a macro to remove the apostrophes, as described in the
following MSKB article:

XL2000: Visual Basic Macros That Add or Remove Hidden Apostrophes
http://support.microsoft.com/default.aspx?id=213440

Don wrote:
In some Excel files, both native and those created with data imported from a
tab delimited file, many or all of the cells are populated with the ' (as is)
character. This presents a problem when I would like to have cells formatted
in a specific manner (i.e. dates/double integer). I have tried to
search/replace for the character to no avail. Does anyone have a surefire
way of removing this "exact" character enmasse?

Thanks,
Don


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Don

If all Excel files affected, it is possible you have ToolsOptionsTransition
"Transition Navigation Keys" enabled.

Gord Dibben Excel MVP

On Mon, 20 Dec 2004 18:03:02 -0800, Don wrote:

Unfortunately, this has no effect on the column (or row, depending on which
is selected). To clarify:

Cell A1 is: 'Field1

By selecting Data / Text to Columns / Finish, nothing changes.

Nor does the content of the cell(s) change when selecting a different Text
Qualifier (changing from the default " to ').

Any other ideas? These characters are a result of exporting from Access to
Excel. Very annoying. As mentioned, I also have them in other Excel files,
and simply want to remove them all.

"Ragdyer" wrote:

TTC - Text To Columns!

Select the data, then simply open and close TTC.

Select the data (in a single row),
<Data <TextToColumns <Finish