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

081-8185-4765		Research
695-13834170 ATL-21408790		Research
00695446750MSP		95446750
00695466766ATL		95466766
006DTW		DTW - needs to say "research"
00695466044MSP		95466044
ATL00695479042		need last 8 digits
06DTW95547384		95547384
006AT		AT - needs to say "research"
081-8185-4776		Research
081-8185-4791		Research
006MSP34504901-39:05167		MSP34504 - needs to say "research"

<tbody>
</tbody>

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]"