String Function

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

Thanks

Brenda

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" <[email protected]> wrote in message
news:[email protected]...
> What is the string function to find the second occurence of a character in
a
> string?
>
> Thanks
>
> Brenda

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 <[email protected]>
wrote:

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

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

On 3 Feb 2005 15:37:59 -0800, "Harlan Grove" <[email protected]> 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

"Myrna Larson" <[email protected]> wrote...
>On 3 Feb 2005 15:37:59 -0800, "Harlan Grove" <[email protected]> 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.