String Function
 

What is the string function to find the second occurence of a character in a
string?

Thanks

Brenda

String Function
 

I don't think there is a canned function for that. If you want to embed the
formula in your worksheet, you can do the following:

in cell C6, put a string "abcdefghiabcdefghi"

in another cell, put in the formula below. You will see it give you
different results depending on if you have one "c", two "c" or no "c" in the
string.

=IF(ISNUMBER(FIND("c",C6,1+IF(ISNUMBER(FIND("c",C6 )),FIND("c",C6),"no1"))),F
IND("c",C6,1+IF(ISNUMBER(FIND("c",C6)),FIND("c",C6 ),"no2")),0)


D Zook

"Brenda" wrote in message
...
What is the string function to find the second occurence of a character in
a
string?

Thanks

Brenda

String Function
 

There's no function to do it directly. Here's a function to find the nth
occurrence. Change vbTextCompare option as needed to make it case-sensitive.

Option Explicit

Function NthMatch(sText As String, sTarget As String, Which As Long)
Dim i As Long
Dim N As Long
Dim Where As Long

Where = 0
N = 0
i = 0
Do
i = InStr(i + 1, sText, sTarget, vbTextCompare)
If i = 0 Then
Exit Do
Else
N = N + 1
If N = Which Then
Where = i
Exit Do
End If
End If
Loop
NthMatch = Where
End Function



On Thu, 3 Feb 2005 14:09:06 -0800, Brenda
wrote:

What is the string function to find the second occurence of a character in a
string?

Thanks

Brenda

String Function
 

Brenda wrote...
What is the string function to find the second occurence of a
character in a
string?

Worksheet function,

=IF(SUBSTITUTE(s,c,"",2)<s,FIND(c,s,FIND(c,s)+1), 0)

or to find the N_th instance,

=IF(SUBSTITUTE(s,c,"",N)<s,FIND(CHAR(127),SUBSTIT UTE(s,c,CHAR(127),N)),0)

In VBA, consider wrapping either formula above inside an Evaluate call.
There's no single function call equivalent in VBA. The most compact
inline approach would be


Dim p As Long
p = InStr(1, s, c)
If p 0 Then p = InStr(p + 1, s, c)


or for the N_th instance


Dim i As Long, p As Long
For i = 1 To N
p = InStr(p + 1, s, c)
if p = 0 Then Exit For
Next i

String Function
 

On 3 Feb 2005 15:37:59 -0800, "Harlan Grove" wrote:
or for the N_th instance
Dim i As Long, p As Long
For i = 1 To N
p = InStr(p + 1, s, c)
if p = 0 Then Exit For
Next i

Hi, Harlan:

Where and how are you intending to set the function return value? After the
"Next i" statement, if i < N + 1, then there was no Nth occurrence, right?

Myrna

String Function
 

"Myrna Larson" wrote...
On 3 Feb 2005 15:37:59 -0800, "Harlan Grove" wrote:
or for the N_th instance
Dim i As Long, p As Long
For i = 1 To N
p = InStr(p + 1, s, c)
if p = 0 Then Exit For
Next i
....
Where and how are you intending to set the function return value? After the
"Next i" statement, if i < N + 1, then there was no Nth occurrence, right?

I did mention that this was intended to be inline code. I guess I wasn't
explicit that the value, if 0, would be stored in p, and p = 0 would
indicate no N_th instance.