Replacing Apostrophize at Beginning of Cell

[SeanF74]

I have a spreadsheet where columns D through N have SOME data with
apostrophies before the data (e.g., '777, 'DCH8, 'GlobalExpress). Some data
does, and some data doesn't have the apostrophe and they are all mixed
together. This becomes a pain when trying to compare cells (e.g., I want to
compare replace DCH8 with 'DCH8). When I go to find replace, if I put find '
and replace with nothing, it does not find anything. How can I get rid of
all of the ' in front of the data?

Thanks!

 

[villandro]

I would highlight the apostrophe and the copy it the go to the find and
replace and paste the apostrophe that u just copied, it happened to me once
that the apostophe that i was typing with my keybord wasn't the same as the
one that was on the actual cell that why it wasnt finding anything at first.

hope it helps

 

[Joe User]

This becomes a pain when trying to compare cells
(e.g., I want to compare replace DCH8 with 'DCH8)

If you have DCH8 in A1 and 'DCH8 in A2, =IF(A1=A2,TRUE) should return true,
as should =IF(A2="DCH8",TRUE).

Perhaps your mistake is including the apostrophe in the string when you make
a comparison. For example, the following is wrong: =IF(A2="'DCH8",TRUE).

The apostrophe is superfluous in that example; but it should not adversely
affect your ability to write comparison formulas.

On the other hand, in the example '777, the question is: do you want to
treat that as a text string or as a number?

If the latter, then it would indeed be better to remove the apostrophe.
I've forgotten how to do that with Find/Replace, if it can be done.
Alternative: use the Text To Column feature, which is under Data in Excel
2003.


----- original message -----

 

[Jaleel]

Hi,

Try the =clean(cell ref.) formula in a helper column.

Jaleel