String manipulation

Posted on 2006-04-04
Last Modified: 2010-05-01

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


Question by:jitganguly
    LVL 14

    Expert Comment

    by:Tommy Kinard
    Hi jitganguly,

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


    LVL 20

    Author Comment

    No boss, The IBF is returning IB only, it has to be IBF
    LVL 85

    Expert Comment

    by:Mike Tomlinson
    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?
    LVL 20

    Author Comment

    It could be 2 or 3, right now it is NY or GC or IBF
    LVL 7

    Expert Comment


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

    LVL 7

    Expert Comment

    sorry, wrong posting. cancel my remark!
    LVL 26

    Accepted Solution

    may be

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

    Expert Comment

    by:Mike Tomlinson
    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," & _
        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


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now