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 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?

Hi C Glenn

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

Then copy and paste special values

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

Paul Sheppard wrote:
> C Glenn Wrote:
>
>>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?
>
>
> Hi C Glenn
>
> In an adjacent column try =TRIM(cellreference) eg =TRIM(A1), this will
> remove the '
>
> Then copy and paste special values
>
>

[Gary''s Student]

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
--
Gary''s Student


"C Glenn" wrote:

> 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?
>

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





Gary''s Student wrote:
> 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