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?

[TABLE="class: cms_table, width: 114"]
<tbody>[TR]
[TD]006ATL95210452[/TD]
[/TR]
[TR]
[TD]006MSP95212176[/TD]
[/TR]
[TR]
[TD]006MSP95212176[/TD]
[/TR]
[TR]
[TD]006DTW95214475[/TD]
[/TR]
[TR]
[TD]006ATL95219924[/TD]
[/TR]
[TR]
[TD]006ATL95219924[/TD]
[/TR]
[TR]
[TD]006ATL97146350[/TD]
[/TR]
[TR]
[TD]006MSP95228184[/TD]
[/TR]
[TR]
[TD]006MSP95230612[/TD]
[/TR]
[TR]
[TD]00695446750MSP[/TD]
[/TR]
[TR]
[TD]00695466766MSP[/TD]
[/TR]
[TR]
[TD]00695466044MSP[/TD]
[/TR]
</tbody>[/TABLE]

 

[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