String Function

[Guest]

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

Thanks

Brenda

 

[z]

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

 

[Myrna Larson]

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

 

[Harlan Grove]

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),SUBSTITUTE(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

 

[Myrna Larson]

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

 

[Harlan Grove]

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