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

Sphinx404

Board Regular
Joined
May 2, 2015
Messages
186
Office Version
  1. 365
Platform
  1. Windows
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?

Code:
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!
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Hi

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

Code:
       Case UCase(mycell.Value) Like "*[A-Z][A-Z][A-Z]"
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,711
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top