Searching for text within text

[Don Guillett]

Nest them together by using the find number as the last number in the
formula

 

[CRS]

Is there an Excel function which will enable me to find the position of
the 2nd (or 3rd, or 4th, etc) occurence of a specific text string within
a longer text string?

The FIND and SEARCH functions appear to find only the 1st occurence, if
I understand them right.

Would appreciate any help/advice. Many thanks,

CRS.

 

[Harlan Grove]

Nest them together by using the find number as the last number in the
formula ....

FWIW, nesting will cease to work after the 8th instance since no further
nested calls would be allowed. See the following linked message thread for
alternatives.

http://google.com/groups?threadm=#[email protected]

 

[CRS]

Many thanks Harlan. That gives me something to work on. Still a bit
surprised however that Excel doesn't have this as a "basic" function.

Regards,

CRS.

________________________________________________________________________
Original message from Harlan Grove follows ...

 

[Harlan Grove]

Many thanks Harlan. That gives me something to work on. Still a bit
surprised however that Excel doesn't have this as a "basic" function.

....

First, please post in PLAIN TEXT only. Not MIME or HTML.

Spreadsheets aren't meant to be text processing tools, so the absence of
built-in functions for text processing rivalling Python or Perl shouldn't
come as much of a surprise.

If you need to do this sort of thing frequently, you should download and
install Laurent Longre's MOREFUNC.XLL add-in, available from

http://longre.free.fr/english/

It provides functions named WORDCOUNT and WMID that make this sort of text
parsing fairly simple. Or you could use a user-defined function wrapper
around VBScript's regular expression object to provide an even more general
approach to parsing specific fields from text strings.

At least it's not too difficult to add this functionality. That said, my
Excel wish list would contain alternative functions like FIND and SEARCH
that would return 0 rather than #VALUE! if the given substring weren't
found, would take a string as well as an integer as optional 3rd argument
for starting location, and would take an optional 4th argument as instance
number relative to the starting position given in the 3rd argument.
Something like


Function myfind( _
ss As String, _
s As String, _
Optional sp As Variant = 1, _
Optional inst As Long = 1, _
Optional cmp As Long = vbBinaryCompare _
) As Long
'--------------------------------
Dim k As Long

If TypeOf sp Is Range Then sp = sp.Value

If TypeName(sp) = "String" Then sp = InStr(1, s, sp, cmp)

k = Len(ss)

If k > 0 Then
Do While sp > 0 And inst > 1
myfind = InStr(sp, s, ss, cmp)
If myfind > 0 Then inst = inst - 1
sp = myfind + k
Loop

If sp > 0 Then myfind = InStr(sp, s, ss, cmp)

Else
myfind = Len(s)
If myfind > sp Then myfind = sp

End If

End Function


When used like FIND, it returns the same results except when FIND would
return #VALUE!, this udf returns 0 when the substring sought isn't "", and
the lesser of the starting position or the length of the string being
searched when the substring sought is "".

Illustrations
myfind("x",y,"z") == FIND("x",y,FIND("z",y))
myfind("x",y,"z",3) == FIND("x",y,FIND("x",y,FIND("x",y,FIND("z",y))+1)+1)

 

[CRS]

Thanks again Harlan, and YOUR point taken about spreadsheets and text
processing.

I'm a bit worried about your comment regarding posting in plain text
only. I completely agree, and I must say I thought I was. On my
server, my posts certainly look like plain text, but having said that,
whilst I think I'd be able to spot an HTML post, I'm not at all sure I'd
recognise a MIME post. Thanks for the tip-off, and I'll look into it.

CRS.

________________________________________________________________________
Original message from Harlan Grove follows ...

 

[Harlan Grove]

I'm a bit worried about your comment regarding posting in plain text
only. I completely agree, and I must say I thought I was. On my
server, my posts certainly look like plain text, but having said that,
whilst I think I'd be able to spot an HTML post, I'm not at all sure I'd
recognise a MIME post. Thanks for the tip-off, and I'll look into it.

....

Some of your most recent reply's headers.

MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"
Content-Transfer-Encoding: quoted-printable
X-Newsreader: MicroPlanet Gravity v2.60

It's your newsreader, MicroPlanet Gravity, rather than your NNTP server that
generates MIME'd postings. While Content-Type: text/plain; may look like
plain text, it isn't. It's MIME'd text that just appearing as plain text.
Plain text is *plain*. There's no MIME, no Content-Type, nothin'. There
should be a configuration setting in your newsreader to use truly plain
text.

 

[CRS]

Genuine thanks (I simply hadn't realised). I'll get that sorted out.

CRS.