String manipulation

MMGC20060404.txt
MMGC_B_20060404.TXT
MMIBF20060404.txt
MMIBF_B_20060404.TXT
MMNY20060404.txt
MMNY_B_20060404.TXT

Based on these how do I determine the branch ? I need to extract GC or GC_B in one statement

These values are in a array and date is dynamic. I want output to be something like

mystring(0)="GC"
mystring(1)="GC_B"


LVL 20
jitgangulyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tommy KinardCommented:
Hi jitganguly,

Answer = IIf(InStr(1, InputText, "_"), Mid(InputText, 3, 4), Mid(InputText, 3, 2))

HTH
dragontooth

jitgangulyAuthor Commented:
No boss, The IBF is returning IB only, it has to be IBF
Mike TomlinsonHigh School Computer Science, Computer Applications, and Mathematics TeachersCommented:
Before the underscore (if there is one), you have some branches with a length of 2 (GC, NC) and some with a length of 3 (IBF).

Is there a wider range that you are not showing?

Can we always start at the 3rd character and then stop at the first digit we find (the first digit in the year portion of the date)?  Then trim off an underscore if there is one at the end?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jitgangulyAuthor Commented:
It could be 2 or 3, right now it is NY or GC or IBF
IT-SchubertzCommented:
Hi,


I don't understand what you want.please eyplain a bit more detailed

Andy
IT-SchubertzCommented:
sorry, wrong posting. cancel my remark!
EDDYKTCommented:
may be

IIf(InStr(1, InputText, "_"), Mid(InputText, 3, Len(InputText) - 15), Mid(InputText, 3, Len(InputText) - 14))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike TomlinsonHigh School Computer Science, Computer Applications, and Mathematics TeachersCommented:
Does it have to be a one liner?  I think it would be easier to understand in a function...

Option Explicit

Private Sub Command1_Click()
    Dim data As String
    Dim Values() As String
    Dim Branches() As String
   
    data = "MMGC20060404.txt," & _
        "MMGC_B_20060404.TXT," & _
        "MMIBF20060404.TXT," & _
        "MMIBF_B_20060404.TXT," & _
        "MMNY20060404.TXT," & _
        "MMNY_B_20060404.TXT"
    Values = Split(data, ",")
    Branches = GetBranches(Values)
   
    Dim i As Integer
    For i = LBound(Values) To UBound(Values)
        Debug.Print Branches(i), Values(i)
    Next i
End Sub

Private Function GetBranches(ByRef vals() As String) As String()
    Dim Branches() As String
    ReDim Branches(UBound(vals))
    Dim i As Integer
    Dim j As Integer
    For i = LBound(vals) To UBound(vals)
        For j = 3 To Len(vals(i))
            Select Case Mid(vals(i), j, 1)
                Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "0"
                    Branches(i) = Mid(vals(i), 3, j - 3)
                    If Right(Branches(i), 1) = "_" Then
                        Branches(i) = Left(Branches(i), Len(Branches(i)) - 1)
                    End If
                    Exit For
            End Select
        Next j
    Next i
    GetBranches = Branches
End Function

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.