Searching for text within text

  • Thread starter Thread starter Don Guillett
  • Start date Start date
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.
 
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 ...
 
CRS said:
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.
Click to expand...
....

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)
 
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 ...
 
CRS said:
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.
Click to expand...
....

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.
 
Back
Top