• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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"


0
jitganguly
Asked:
jitganguly
  • 2
  • 2
  • 2
  • +2
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

IIf(InStr(1, InputText, "_"), Mid(InputText, 3, Len(InputText) - 15), Mid(InputText, 3, Len(InputText) - 14))
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now