VBA Macro: MID - Pulling Data from Inside Strings

[Sphinx404]

You'll notice the last 3 entries in the data set begin with "006", however, the alphabetic characters are at the end of the string. This poses a new question, I can account for the what the string begins with and what the string ends with, but I'm stuck on how to write the code to pull the information in-between. Below my "non-working" code is in as I have attempted to create a rule that accounts for these random outliers.

I need the 8 numeric values in-between... between the "006" & the "MSP","ATL", etc.

Select Case True
Case Left(mycell.Value, 3) = "006"
If IsNumeric(Right(mycell.Value, 8)) Then
mycell.Offset(, 16).Value = Right(mycell.Value, 8)
End If
Case Left(mycell.Value, 2) = "06"
If IsNumeric(Right(mycell.Value, 8)) Then
mycell.Offset(, 16).Value = Right(mycell.Value, 8)
Case Left(mycell.Value, 3) = "006"
If Not IsNumeric(Right(mycell.Value, 8)) Then
mycell.Value = Mid(mycell.Value, 3, 8)
End If
End Select


Ideas?

006ATL95210452

006MSP95212176

006MSP95212176

006DTW95214475

006ATL95219924

006ATL95219924

006ATL97146350

006MSP95228184

006MSP95230612

00695446750MSP

00695466766MSP

00695466044MSP

<tbody>
</tbody>

 

[Sphinx404]

Solved, thank you folks

 

[Joe4]

Duplicate/continuation of this: https://www.mrexcel.com/forum/excel-questions/1027766-vba-macro-if-begins-then.html

In the future, please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

 

[Sphinx404]

I apologize. I looked for a "Delete thread Topic" button but I could not locate one. I wanted to delete this, but was (and still am) unaware of how.

Thanks. Won't happen again.

Steve