Apostrophy Detection

I need a function that returns TRUE if its argument (a string) has a leading
apostrophy, otherwise FALSE.

So if A1 contains a then APOS(A1) should return FALSE.
if A1 contains 'a then APOS(A1) should return TRUE.

The difficulty I am having is that the LEN function ignores the apostrophy
and the LEFT function does the same.

Thanks in advance
--
Gary's Student

GS,

Use the PrefixCharacter property.

Jim Cone
San Francisco, USA

"Gary's Student" <[email protected]> wrote in message
news:[email protected]...
I need a function that returns TRUE if its argument (a string) has a leading
apostrophy, otherwise FALSE.
So if A1 contains a then APOS(A1) should return FALSE.
if A1 contains 'a then APOS(A1) should return TRUE.
The difficulty I am having is that the LEN function ignores the apostrophy
and the LEFT function does the same.
Thanks in advance
--
Gary's Student

Thanks Jim

Function tickiller2(r As Range) As Boolean
tickiller2 = True
If r.PrefixCharacter = "" Then
tickiller2 = False
End If
End Function

will help me find and remove useless ticks.
--
Gary's Student


"Jim Cone" wrote:

> GS,
>
> Use the PrefixCharacter property.
>
> Jim Cone
> San Francisco, USA
>
> "Gary's Student" <[email protected]> wrote in message
> news:[email protected]...
> I need a function that returns TRUE if its argument (a string) has a leading
> apostrophy, otherwise FALSE.
> So if A1 contains a then APOS(A1) should return FALSE.
> if A1 contains 'a then APOS(A1) should return TRUE.
> The difficulty I am having is that the LEN function ignores the apostrophy
> and the LEFT function does the same.
> Thanks in advance
> --
> Gary's Student
>

You shouldn't have to worry about them. You already established you can't
detect them with anything but a special property designed for that.

--
Regards,
Tom Ogilvy

"Gary's Student" <[email protected]> wrote in message
news:[email protected]...
> Thanks Jim
>
> Function tickiller2(r As Range) As Boolean
> tickiller2 = True
> If r.PrefixCharacter = "" Then
> tickiller2 = False
> End If
> End Function
>
> will help me find and remove useless ticks.
> --
> Gary's Student
>
>
> "Jim Cone" wrote:
>
> > GS,
> >
> > Use the PrefixCharacter property.
> >
> > Jim Cone
> > San Francisco, USA
> >
> > "Gary's Student" <[email protected]> wrote in
message
> > news:[email protected]...
> > I need a function that returns TRUE if its argument (a string) has a
leading
> > apostrophy, otherwise FALSE.
> > So if A1 contains a then APOS(A1) should return FALSE.
> > if A1 contains 'a then APOS(A1) should return TRUE.
> > The difficulty I am having is that the LEN function ignores the
apostrophy
> > and the LEFT function does the same.
> > Thanks in advance
> > --
> > Gary's Student
> >

Tom:
I need to worry about them because, even though they are hard to detect,
they are also very fattening. Like candy.

We recently received several workbooks with tick (apostrophy) infestations.
Not only were strings ticked, but many thousands of "empty" cells as well.
The workbooks are 10's of Mbytes big and slow to function. I need to remedy
this situation.
--
Gary's Student


"Tom Ogilvy" wrote:

> You shouldn't have to worry about them. You already established you can't
> detect them with anything but a special property designed for that.
>
> --
> Regards,
> Tom Ogilvy
>
> "Gary's Student" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Jim
> >
> > Function tickiller2(r As Range) As Boolean
> > tickiller2 = True
> > If r.PrefixCharacter = "" Then
> > tickiller2 = False
> > End If
> > End Function
> >
> > will help me find and remove useless ticks.
> > --
> > Gary's Student
> >
> >
> > "Jim Cone" wrote:
> >
> > > GS,
> > >
> > > Use the PrefixCharacter property.
> > >
> > > Jim Cone
> > > San Francisco, USA
> > >
> > > "Gary's Student" <[email protected]> wrote in
> message
> > > news:[email protected]...
> > > I need a function that returns TRUE if its argument (a string) has a
> leading
> > > apostrophy, otherwise FALSE.
> > > So if A1 contains a then APOS(A1) should return FALSE.
> > > if A1 contains 'a then APOS(A1) should return TRUE.
> > > The difficulty I am having is that the LEN function ignores the
> apostrophy
> > > and the LEFT function does the same.
> > > Thanks in advance
> > > --
> > > Gary's Student
> > >
>
>
>

set rng = Activesheet.UsedRange.SpecialCells(xlConstants,xlTextValues)
for each cell in rng
if len(trim(cell.value)) = 0 then
cell.Clear
end if
Next

--
Regards,
Tom Ogilvy


"Gary's Student" <[email protected]> wrote in message
news:[email protected]...
> Tom:
> I need to worry about them because, even though they are hard to detect,
> they are also very fattening. Like candy.
>
> We recently received several workbooks with tick (apostrophy)
infestations.
> Not only were strings ticked, but many thousands of "empty" cells as well.
> The workbooks are 10's of Mbytes big and slow to function. I need to
remedy
> this situation.
> --
> Gary's Student
>
>
> "Tom Ogilvy" wrote:
>
> > You shouldn't have to worry about them. You already established you
can't
> > detect them with anything but a special property designed for that.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Gary's Student" <[email protected]> wrote in
message
> > news:[email protected]...
> > > Thanks Jim
> > >
> > > Function tickiller2(r As Range) As Boolean
> > > tickiller2 = True
> > > If r.PrefixCharacter = "" Then
> > > tickiller2 = False
> > > End If
> > > End Function
> > >
> > > will help me find and remove useless ticks.
> > > --
> > > Gary's Student
> > >
> > >
> > > "Jim Cone" wrote:
> > >
> > > > GS,
> > > >
> > > > Use the PrefixCharacter property.
> > > >
> > > > Jim Cone
> > > > San Francisco, USA
> > > >
> > > > "Gary's Student" <[email protected]> wrote in
> > message
> > > > news:[email protected]...
> > > > I need a function that returns TRUE if its argument (a string) has a
> > leading
> > > > apostrophy, otherwise FALSE.
> > > > So if A1 contains a then APOS(A1) should return FALSE.
> > > > if A1 contains 'a then APOS(A1) should return TRUE.
> > > > The difficulty I am having is that the LEN function ignores the
> > apostrophy
> > > > and the LEFT function does the same.
> > > > Thanks in advance
> > > > --
> > > > Gary's Student
> > > >
> >
> >
> >