Deleting characters after 3rd occurence of a string
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))2,LEFT(A1,FIND("/",A1,FIND("/",A1,FIND("/",A1)+1)+1)-1),A1)
--
__________________________________
HTH
Bob
"Mark b." wrote in message
...
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
|