VBA: Accounting for Non-Numeric #s, How if not ISNUMERIC?

[Sphinx404]

I am trying to account for non-numeric values at the end of my string. I input MSP just to test if the code would work with my data set. THE PROBLEM: "MSP" can be any combination of letters and I need to account for all variations. The part of the code in PURPLE has no effect on the data, which renders my CASE useless.

Anyone have any idea how I can account for the random letters at the end of my string?

Select Case True    Case Left(mycell.Value, 3) = "006"
        If IsNumeric(Right(mycell.Value, 8)) Then
            mycell.Offset(, 16).Value = Right(mycell.Value, 8)
        Else
            mycell.Offset(, 16).Value = Mid(mycell.Value, 4, 8)
        End If
[COLOR=#4b0082]    Case Right(mycell.Value, 3) = "[/COLOR][COLOR=#0000ff][B]MSP[/B][/COLOR][COLOR=#4b0082]"[/COLOR]
[COLOR=#4b0082]        If IsNumeric(Left(mycell.Value, 3)) Then[/COLOR]
[COLOR=#4b0082]            mycell.Offset(, 16).Value = Mid(mycell.Value, 4, 8)[/COLOR]
[COLOR=#4b0082]        Else[/COLOR]
[COLOR=#4b0082]            mycell.Offset(, 16).Value = "Research"[/COLOR]
[COLOR=#4b0082]        End If[/COLOR]
    Case Left(mycell.Value, 2) = "06"
        If IsNumeric(Right(mycell.Value, 8)) Then
            mycell.Offset(, 16).Value = Right(mycell.Value, 8)
        End If
    Case Len(mycell.Value) = 0
            mycell.Offset(, 16).Value = "Research"
End Select

[TABLE="width: 530"]
<tbody>[TR]
[TD]081-8185-4765[/TD]
[TD][/TD]
[TD]Research[/TD]
[/TR]
[TR]
[TD]695-13834170 ATL-21408790[/TD]
[TD][/TD]
[TD]Research[/TD]
[/TR]
[TR]
[TD]00695446750MSP[/TD]
[TD][/TD]
[TD]95446750[/TD]
[/TR]
[TR]
[TD]00695466766ATL[/TD]
[TD][/TD]
[TD]95466766[/TD]
[/TR]
[TR]
[TD]006DTW[/TD]
[TD][/TD]
[TD]DTW - needs to say "research"[/TD]
[/TR]
[TR]
[TD]00695466044MSP[/TD]
[TD][/TD]
[TD]95466044[/TD]
[/TR]
[TR]
[TD]ATL00695479042[/TD]
[TD][/TD]
[TD]need last 8 digits[/TD]
[/TR]
[TR]
[TD]06DTW95547384[/TD]
[TD][/TD]
[TD]95547384[/TD]
[/TR]
[TR]
[TD]006AT[/TD]
[TD][/TD]
[TD]AT - needs to say "research"[/TD]
[/TR]
[TR]
[TD]081-8185-4776[/TD]
[TD][/TD]
[TD]Research[/TD]
[/TR]
[TR]
[TD]081-8185-4791[/TD]
[TD][/TD]
[TD]Research[/TD]
[/TR]
[TR]
[TD]006MSP34504901-39:05167[/TD]
[TD][/TD]
[TD]MSP34504 - needs to say "research"[/TD]
[/TR]
</tbody>[/TABLE]

Excel MVPs? Macro Kings? Giants of Excel? I need you. THANKS!

 

[pgc01]

Hi

For any combination of 3 letters at the end of the string try:

       Case UCase(mycell.Value) Like "*[A-Z][A-Z][A-Z]"