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?
 
EDDYKTCommented:
may be

IIf(InStr(1, InputText, "_"), Mid(InputText, 3, Len(InputText) - 15), Mid(InputText, 3, Len(InputText) - 14))
0
 
Tommy KinardCommented:
Hi jitganguly,

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

HTH
dragontooth

0
 
jitgangulyAuthor Commented:
No boss, The IBF is returning IB only, it has to be IBF
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Mike TomlinsonMiddle School Assistant TeacherCommented:
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?
0
 
jitgangulyAuthor Commented:
It could be 2 or 3, right now it is NY or GC or IBF
0
 
IT-SchubertzCommented:
Hi,


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

Andy
0
 
IT-SchubertzCommented:
sorry, wrong posting. cancel my remark!
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.