"SteveC" wrote:
An alternative approach is just
to return the last piece of text with "NYSE:" in it...
I noticed that the correct NYSE ticker is
always the last one listed in the cell...
I just want to return in Cell H2: "NYSE:MER"

Steve,

In the interim, found one possible way:

With Myrna Larson's UDF* implemented in the book
we could put in H2: =MID(G2,xInstrRev(G2,"NYSE:"),99)
and copy H2 down

(the 99 is just an arbitrary number)

*Myrna's UDF is pasted below (for xl97, my ver)

Install the UDF in the *same* book (reqd for UDFs)
that the UDF is going to be used

To install, press Alt+F11, click Insert Module,
then paste the UDF into the code window
then press Alt+Q to get back to Excel

Here's a sample file with Myrna's UDF implemented:
http://www.savefile.com/files/7035665
Extract_From_Right_MyrnaLarson_UDF.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

'---- UDF by Myrna Larson --------
Function xInstrRev(ByVal Target As String, ByVal Fragment As String, _
Optional StartPos As Long = -1, Optional CompareMode _
As Long = vbBinaryCompare) As Long
'Myrna Larson

Dim Start As Long
Dim PrevStart As Long
Dim LastPossibleStart As Long

If StartPos = -1 Then StartPos = Len(Target)
LastPossibleStart = StartPos + 1 - Len(Fragment)

PrevStart = 0
Do
Start = InStr(PrevStart + 1, Target, Fragment, CompareMode)
If Start = 0 Or Start LastPossibleStart Then Exit Do
PrevStart = Start
Loop
xInstrRev = PrevStart

End Function
'------ end -------