VBA - throwing exceptions for specific errors in working code, IsNumeric issue?

There are a couple things that I would do differently.

1. I would store the result of the Instr function in a variable
2. When you find the first "TL" entry you keep those characters as part of your answer. But that means you need to worry about spaces and hyphens between the text and the numbers. I would look for the first "TL" and then from that position look at successive characters looking for the first numeric one. This is the start of your number. Anything before that character should get removed.
3. To format a number with leading zeroes you can use the Format$ function. To remove leading zeroes, you can convert the string into a long using CLng.
4. It looks like you might need similar code for later in your code when you look for "CT" so I suggest creating a function that returns the number.

Here is the function:

Public Function ExtractNumberWithLeadingZeroes(ByRef theWholeText As String, ByRef idText As String, ByRef numCharsRequired As Integer) As String

' Finds the first entry of idText in theWholeText
' Returns the first number found after idText formatted
' with leading zeroes

Dim i As Integer
Dim j As Integer
Dim thisChar As String
Dim returnValue As String
Dim tmpText As String
Dim firstPosn As Integer
Dim secondPosn As Integer

    returnValue = ""
    firstPosn = InStr(1, theWholeText, idText)
    If firstPosn > 0 Then
        ' remove any text before first idText, also remove the first idText
        tmpText = Mid(theWholeText, firstPosn + Len(idText))
        'if more than one idText value, delete everything after (and including) the second idText
        secondPosn = InStr(1, tmpText, idText)
        If secondPosn > 0 Then
            tmpText = Mid(tmpText, 1, secondPosn)
        End If
        ' Find first number
        For j = 1 To Len(tmpText)
            If IsNumeric(Mid(tmpText, j, 1)) Then
                tmpText = Mid(tmpText, j)
                Exit For
            End If
        Next j
        ' Find where the numbers end
        returnValue = tmpText
        For j = 1 To Len(returnValue)
            thisChar = Mid(returnValue, j, 1)
            If Not IsNumeric(thisChar) Then
                returnValue = Mid(returnValue, 1, j - 1)
                Exit For
            End If
        Next j
        'force to numCharsRequired numbers if too short; add 0s immediately after idText
        'force to numCharsRequired numbers if too long; eliminate 0s immediately after idText
        ' The CLng gets rid of leading zeroes and the Format$ adds any required up to numCharsRequired chars
        returnValue = Format$(CLng(returnValue), String(numCharsRequired, "0"))
    End If

    ExtractNumberWithLeadingZeroes = returnValue

End Function

You call this function like this:

ret = ExtractNumberWithLeadingZeroes(str, "TL", 6)

And you get something like "000487".

Your original block of code becomes:

'force length of TL/CT to be 6/4 numbers long, eliminate spaces
Dim str As String, ret As String, k As Integer

For k = 2 To StartSht.Range("C2").End(xlDown).Row
    ret = ""
    str = StartSht.Range("C" & k).Value

    ret = ExtractNumberWithLeadingZeroes(str, "TL", 6)
    If ret <> "" Then
        StartSht.Range("C" & k).Value = "TL-" & ret
    Else

        'for CT numbers
        ret = ExtractNumberWithLeadingZeroes(str, "CT", 4)
        If ret <> "" Then
            StartSht.Range("C" & k).Value = "CT-" & ret
        End If

    End If
Next k

EDIT: OP clarified his position so I've re-written the ExtractNumberWithLeadingZeroes function and included the new version below:

Public Function ExtractNumberWithLeadingZeroes(ByRef theWholeText As String, ByRef idText As String, ByRef numCharsRequired As Integer) As String

' Finds the first entry of idText in theWholeText
' Returns the first number found after idText formatted
' with leading zeroes

Dim returnValue As String
Dim extraValue As String
Dim tmpText As String
Dim firstPosn As Integer
Dim secondPosn As Integer
Dim ctNumberPosn As Integer

    returnValue = ""
    firstPosn = InStr(1, theWholeText, idText)
    If firstPosn > 0 Then
        ' remove any text before first idText, also remove the first idText
        tmpText = Mid(theWholeText, firstPosn + Len(idText))
        'if more than one idText value, delete everything after (and including) the second idText
        secondPosn = InStr(1, tmpText, idText)
        If secondPosn > 0 Then
            tmpText = Mid(tmpText, 1, secondPosn)
        End If
        returnValue = ExtractTheFirstNumericValues(tmpText, 1)
        If idText = "CT" Then
            ctNumberPosn = InStr(1, tmpText, returnValue)
            ' Is the next char a dash? If so, must include more numbers
            If Mid(tmpText, ctNumberPosn + Len(returnValue), 1) = "-" Then
                ' There are some more numbers, after the dash, to extract
                extraValue = ExtractTheFirstNumericValues(tmpText, ctNumberPosn + Len(returnValue))
            End If
        End If
        'force to numCharsRequired numbers if too short; add 0s immediately after idText
        'force to numCharsRequired numbers if too long; eliminate 0s immediately after idText
        ' The CLng gets rid of leading zeroes and the Format$ adds any required up to numCharsRequired chars
        If returnValue <> "" Then
            returnValue = Format$(CLng(returnValue), String(numCharsRequired, "0"))
            If extraValue <> "" Then
                returnValue = returnValue & "-" & extraValue
            End If
        End If
    End If

    ExtractNumberWithLeadingZeroes = returnValue

End Function

Private Function ExtractTheFirstNumericValues(ByRef theText As String, ByRef theStartingPosition As Integer) As String

Dim i As Integer
Dim j As Integer
Dim tmpText As String
Dim thisChar As String

    ' Find first number
    For i = theStartingPosition To Len(theText)
        If IsNumeric(Mid(theText, i, 1)) Then
            tmpText = Mid(theText, i)
            Exit For
        End If
    Next i
    ' Find where the numbers end
    For j = 1 To Len(tmpText)
        thisChar = Mid(tmpText, j, 1)
        If Not IsNumeric(thisChar) Then
            tmpText = Mid(tmpText, 1, j - 1)
            Exit For
        End If
    Next j

    ExtractTheFirstNumericValues = tmpText

End Function