Deleting characters after 3rd occurence of a string

[Mark b.]

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?

 

[Ron Rosenfeld]

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

 

[Bob Phillips]

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

 

[Mark b.]

Thanks, Ron! Worked like a charm.

 

[Ron Rosenfeld]

Thanks, Ron! Worked like a charm.

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