Find the nth occurence of a character in a text string

[ctrlaltdel]

Does someone know of either a formula or VBA code to locate and give the position of the nth occurence of a character in a text string in a cell?

For example:

If cell C5 = "Colorado - 1995 - Math - State Deptartment"

How might I get the character position of the 3rd hyphen without doing a nested formula?

Thanks for any help on this.

 

[Aladin Akyurek]

=FIND(CHAR(127),SUBSTITUTE(A1,"-",CHAR(127),3))

 

[ctrlaltdel]

Perfect! Thank you.

So to isolate all of the text after the third hyphen, I use the formula:

=MID(A1,FIND(CHAR(127),SUBSTITUTE(A1,"-",CHAR(127),3))+2,LEN(A1))

I know that there are no trailing spaces, but could add trim to this if necessary.

Does this sound like the easiest way?

 

[Aladin Akyurek]

Perfect! Thank you.

So to isolate all of the text after the third hyphen, I use the formula:

=MID(A1,FIND(CHAR(127),SUBSTITUTE(A1,"-",CHAR(127),3))+2,LEN(A1))

I know that there are no trailing spaces, but could add trim to this if necessary.

Does this sound like the easiest way?

+2 is risky. Why 3 -- because it's the last hyphen?

 

[ctrlaltdel]

There is always a space after the hyphen, so by using the +2, I get to the first letter of the word following.

Unfortunately I cannot be assured that the 3rd hyphen will be the last, so I need to extract all the text after the 3rd hyphen from the left.

Do you have a better solution?

 

[Aladin Akyurek]

There is always a space after the hyphen, so by using the +2, I get to the first letter of the word following.

Unfortunately I cannot be assured that the 3rd hyphen will be the last, so I need to extract all the text after the 3rd hyphen from the left.

Do you have a better solution?

=TRIM(RIGHT(C5,LEN(C5)-SEARCH(CHAR(127),SUBSTITUTE(C5,"-",CHAR(127),LEN(C5)-LEN(SUBSTITUTE(C5,"-",""))))))

would give you whatever is after the last hyphen.

 

[ctrlaltdel]

Does anyone have a VB solution?

 

[Seti]

This worked for me, its a function rather than a macro.

Function AfterLastHyphen(celldata)
Dim i As Long
    AfterLastHyphen = ""
    For i = Len(celldata) To 1 Step -1
        If Mid(celldata, i, 1) = "-" Then Exit For
        AfterLastHyphen = Mid(celldata, i, 1) & AfterLastHyphen
    Next i
    AfterLastHyphen = Trim(AfterLastHyphen)
End Function