Removing errant apostrophes

[C Glenn]

I'm working with a spreadsheet produced by a scraper. For reasons that
I can only guess and am powerless to change, it places an apostrophe
before some of the numbers (IOW, one might imagine that it believes them
to be numeric strings).

I was hoping to find a way to remove them en masse, through search and
replace perhaps. But the search half of the process doesn't know how to
look for an apostrophe at the beginning of a cell. It seems that those
don't count.

Any ideas on how this could be automated?

 

[Paul Sheppard]

I'm working with a spreadsheet produced by a scraper. For reasons that
I can only guess and am powerless to change, it places an apostrophe
before some of the numbers (IOW, one might imagine that it believe
them
to be numeric strings).

I was hoping to find a way to remove them en masse, through search and
replace perhaps. But the search half of the process doesn't know ho
to
look for an apostrophe at the beginning of a cell. It seems tha
those
don't count.

Any ideas on how this could be automated?

Hi C Glenn

In an adjacent column try =TRIM(cellreference) eg =TRIM(A1), this wil
remove the '

Then copy and paste special value

 

[C Glenn]

That works!

I created a cell with your suggested formula pointing to one of the
errant cells, then copied it to a range of cells that would encompass
all of the cells that were not converted properly. (Fortunately, they
were contiguous; there were no cells having contents that would be
mangled by the TRIM process.) Now, the rest of my workbook can simply
point to those trimmed up cells. The data import process will have to
include a macro that automates this step.

Thanks.

Chris.

 

[Guest]

You have found a very pesky fact. Apostrophes in the middle of text are easy
to remove, but leading apostrophes are more difficult.

Enter this tiny macro:

Sub tickout()
Dim r As Range
For Each r In Selection
r.Value = r.Value
Next
End Sub

Select the cells you want leading ticks (apostrophes) removed and call the
macro.
It will remove ticks but is not effective against fleas

 

[C Glenn]

This is amazing! It works on ticks but not fleas!?!?

Hey, thanks. This is great. Really bizarre though --
r.Value = r.Value removes the leading apostrophe. Also, I don't get
that we would need to Dim r as Range. Seems like we should Dim c as
Cell. Isn't Cell a valid concept in this context? I'm still a little
new to Excel macros.