Trying to systematically delete all characters after the 3rd occurence of a
string and not having much luck with the Len, right and substitute commands.

In a region of cells, some of the data contains values separated by the '/'
character. when a cell contains the '/' character 3 (or more) times, I'd
like to remove that character and all characters to the right of it.

Thus:

apple/banana/cherry/date would get truncated down to apple/banana/cherry
animal/vegetable would remain animal/vefetable


thoughts?


--
Thanks,
Mark

On Mon, 8 Sep 2008 14:18:00 -0700, Mark b. <(E-Mail Removed)>
wrote:

>Trying to systematically delete all characters after the 3rd occurence of a
>string and not having much luck with the Len, right and substitute commands.
>
>In a region of cells, some of the data contains values separated by the '/'
>character. when a cell contains the '/' character 3 (or more) times, I'd
>like to remove that character and all characters to the right of it.
>
>Thus:
>
>apple/banana/cherry/date would get truncated down to apple/banana/cherry
>animal/vegetable would remain animal/vefetable
>
>
>thoughts?


=REPLACE(A1,FIND(CHAR(1),SUBSTITUTE(A1&CHAR(1),"/",CHAR(1),3)),255,"")

--ron

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))>2,LEFT(A1,FIND("/",A1,FIND("/",A1,FIND("/",A1)+1)+1)-1),A1)

--
__________________________________
HTH

Bob

"Mark b." <(E-Mail Removed)> wrote in message
news:898D0AB9-A132-4E16-BAF0-(E-Mail Removed)...
> Trying to systematically delete all characters after the 3rd occurence of
> a
> string and not having much luck with the Len, right and substitute
> commands.
>
> In a region of cells, some of the data contains values separated by the
> '/'
> character. when a cell contains the '/' character 3 (or more) times, I'd
> like to remove that character and all characters to the right of it.
>
> Thus:
>
> apple/banana/cherry/date would get truncated down to apple/banana/cherry
> animal/vegetable would remain animal/vefetable
>
>
> thoughts?
>
>
> --
> Thanks,
> Mark

Thanks, Ron! Worked like a charm.
--
Thanks,
Mark


"Ron Rosenfeld" wrote:

> On Mon, 8 Sep 2008 14:18:00 -0700, Mark b. <(E-Mail Removed)>
> wrote:
>
> >Trying to systematically delete all characters after the 3rd occurence of a
> >string and not having much luck with the Len, right and substitute commands.
> >
> >In a region of cells, some of the data contains values separated by the '/'
> >character. when a cell contains the '/' character 3 (or more) times, I'd
> >like to remove that character and all characters to the right of it.
> >
> >Thus:
> >
> >apple/banana/cherry/date would get truncated down to apple/banana/cherry
> >animal/vegetable would remain animal/vefetable
> >
> >
> >thoughts?
>
>
> =REPLACE(A1,FIND(CHAR(1),SUBSTITUTE(A1&CHAR(1),"/",CHAR(1),3)),255,"")
>
> --ron
>

On Tue, 9 Sep 2008 07:07:01 -0700, Mark b. <(E-Mail Removed)>
wrote:

>Thanks, Ron! Worked like a charm.
>--
>Thanks,
>Mark

You're welcome. Glad to help. Thanks for the feedback.
--ron