Hello

Here is my problem

I have a row of Stock tickers in Column A. They are futures.

and can look like this as an example. EMDH7, NQZ6-NQH7

What i have to do is change the ticker name to what it actually means

The ticker structure has 2 parts: (name)(date) the date is a code
the codes look like this: U = september
                          H = march
                          Z = december
                          M = june
So using the ticker from above, what it needs to look like is.
EMDH7 = EMD..0703
NQZ6 = NQ..0612
NQH7 = NQ..0703

And if the ticker has a hyphen that means the first part is being sold and the second bought so it needs to be on 2 new lines.

The thing that i'm having a hard time with is figuring out how to recognize these tickers and split them up like that? How would I go about making the macro look for the second part of the ticker and know to replace it with the proper code and year.

Thank you in advance.

Keith

Keith,
The way I would approach is firstly to build a function which converts a single stock ticker so that EMDH7 becomes EMD..0703 with a warning in case the penultimate character in the stock ticker is not a U,H,Z,M.

Using this function, I would then build a sub which examines each stock ticker for a hyphen; upon finding one it applies the function twice, once on the characters to the left of the hyphen and again to the right of the hyphen. Each time a counter is incremented by one.

Using a boolean which starts at FALSE, this is turned to TRUE if a hyphen is found; in this way, if the boolean remains at FALSE(meaning no hyphen is found), the function is then made to act on the whole stock ticker(again incrementing the counter by one)

The counter is used to place the result in an appropriate cell.

Using the code at the bottom, my results are below, the LH column is the input and the RH column is the output:-

NQZ6-NQH7 NQ..1206
TTZ5-XXH7 NQ..0307
BBZ6 TT..1205
KKM5 XX..0307
YYTU7 BB..1206
PPD5 KK..0605
PPD6 YYT..0907
NQZ6-NQH7 PP..****checksymbol****05
TTZ5-XXH7 PP..****checksymbol****06
BBZ6 NQ..1206
                       NQ..0307
                       TT..1205
                       XX..0307
                       BB..1206

The code I used is below, hope this is useful.

Public Sub StockTicker()

Dim i As Integer
Dim k As Long
Dim cell As Range
Dim b As Boolean
Dim st1 As String
b = False
k = 1

    For Each cell In Range("a1:a" & Range("a65536").End(xlUp).Row)
    b = False
        If Len(cell) > 1 Then
            For i = 1 To Len(cell)
                If Mid(cell, i, 1) = "-" Then
                    Cells(k, 2) = ConvertTicker(Mid(cell, 1, i - 1))
                    k = k + 1
                    Cells(k, 2) = ConvertTicker(Mid(cell, i + 1, Len(cell) - i))
                    k = k + 1
                    b = True
                End If
            Next

            If b = False Then
                st1 = cell.Value
                Cells(k, 2) = ConvertTicker(st1)
                k = k + 1
            End If
        End If
    Next
End Sub


Public Function ConvertTicker(st As Variant) As String

Dim Qtr As String
    If Len(st) < 2 Then Exit Function

        Select Case Mid(st, Len(st) - 1, 1)

            Case "H"
            Qtr = "03"

            Case "M"
            Qtr = "06"

            Case "U"
            Qtr = "09"

            Case "Z"
            Qtr = "12"

            Case Else
            Qtr = "****checksymbol****"

        End Select

ConvertTicker = Left(st, Len(st) - 2) & ".." & Qtr & "0" & Right(st, 1)


End Function