Re: Find the n-th occurance of a sustring within a string

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi Earl,

Well spotted. I like your idea. To be on the safe side, one could use a single character that would be most unlikely to appear in any 'normal' text string (eg an ASCII 135: ‡), without the need to concoct an 'unlikely' string - though I do accept that doing so renders false matches much less likely.

Plus, in order to deal with errors, one could return '0', via:
=IF(ISERROR(FIND(A2&"‡"&A3,SUBSTITUTE(A1,A2,A2&"‡"&A3,A3))),0,FIND(A2&"‡"&A3,SUBSTITUTE(A1,A2,A2&"‡"&A3,A3)))

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Earl Kiosterud" <someone@xxxxxxxxxxx> wrote in message news:%23MSO87HhHHA.4704@xxxxxxxxxxxxxxxxxxxxxxx
That is slick. It can be fooled, however, in the unlikely event any instance of the string you're looking for happens to have the instance number following it. This might happen in numbers combining alpha characters and digits (like part numbers). For example:

A1: CC3C
A2: C
A3: 3

It finds the second C, and returns 2.

You can use this modification:
=FIND(A2&"|"&A3,SUBSTITUTE(A1,A2,A2&"|"&A3,A3))

You use a character known never to appear in the data, such as the pipe character in this example. The added character can be an unlikely string, like !#$%.

=FIND(A2&"!#$%"&A3,SUBSTITUTE(A1,A2,A2&"!#$%"&A3,A3))

This unlikely string occurs only when Sarge is swearing (and beating the stuffing out of Beetle).
--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if they bottom-post to a reply that's
already top-posted, the thread gets messy. When in Rome...

-----------------------------------------------------------------------
"macropod" <invalid@xxxxxxxxxxxxxxx> wrote in message news:%23K9qw$GhHHA.1216@xxxxxxxxxxxxxxxxxxxxxxx
Hi Tom,

Suppose the string you want to search is in A1, the substring you want to find is in A2 and the substring instance you want to find is in A3, then the following formula will return the position where the nth occurrence of the substring being searched for starts:
=FIND(A2&A3,SUBSTITUTE(A1,A2,A2&A3,A3))

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"TomCon" <someone@xxxxxxxxxxx> wrote in message news:3cxWh.992$YI1.87@xxxxxxxxxxx
One can use SEARCH() or FIND() to find a string within another. And, if you wanted to find the first few, you can nest the calls and it probably works ok. But, this has its limits. Lets say you wanted to find the 10th occurance of a particular string within a string, now the formula gets really hairy, if you nest.

Any clever ideas out there to find the n-th occurance of a string within another?

Thanks,
tom






.

Relevant Pages

  • [TOMOYO #15 3/8] Common functions for TOMOYO Linux.
    ... This file contains common functions (e.g. policy I/O, pattern matching). ... Since TOMOYO Linux is a name based access control, ... TOMOYO Linux's string manipulation functions make reviewers feel crazy, ... the Linux kernel accepts all characters but NUL character ...
    (Linux-Kernel)
  • Re: Help with Replace()
    ... but you're asking to return 1 character, from the 16th....this is the "-" letter, which is now null, hence the null string returned. ... The actual Variable StrToSearch was valued with the " " ... String expression containing sub string to replace. ... Substring being searched for. ...
    (microsoft.public.excel.programming)
  • RfD: Escaped Strings version 4
    ... the S" string can only contain printable characters, ... the S" string cannot contain the '"' character, ... as an escape character for the entry of characters that cannot be ... \b BS (backspace, ASCII 8) ...
    (comp.lang.forth)
  • RfD: Escaped Strings version 4
    ... the S" string can only contain printable characters, ... the S" string cannot contain the '"' character, ... as an escape character for the entry of characters that cannot be ... \b BS (backspace, ASCII 8) ...
    (comp.lang.forth)
  • Re: RfD: Escaped Strings
    ... the S" string can only contain printable characters, ... the S" string cannot contain the '"' character, ... \b BS (backspace, ASCII 8) ... \ ** escapes to characters much as C does. ...
    (comp.lang.forth)