| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting characters after 3rd occurence of a string
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting characters after 3rd occurence of a string
On Mon, 8 Sep 2008 14:18:00 -0700, Mark b.
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting characters after 3rd occurence of a string
Thanks, Ron! Worked like a charm.
-- Thanks, Mark "Ron Rosenfeld" wrote: On Mon, 8 Sep 2008 14:18:00 -0700, Mark b. 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting characters after 3rd occurence of a string
On Tue, 9 Sep 2008 07:07:01 -0700, Mark b.
wrote: Thanks, Ron! Worked like a charm. -- Thanks, Mark You're welcome. Glad to help. Thanks for the feedback. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert Leading Characters If String Is Only 7 Characters | Excel Discussion (Misc queries) | |||
| SUMIF where CRITERIA is looking for a string occurence in the value,not the whole value | Excel Worksheet Functions | |||
| Deleting Characters | Excel Discussion (Misc queries) | |||
| How to count occurence of multiple characters in a cell | Excel Worksheet Functions | |||
| How to find the first occurence of any number in a string | Excel Programming | |||
Linear Mode
