jitganguly
asked on
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"
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"
ASKER
No boss, The IBF is returning IB only, it has to be IBF
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?
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?
ASKER
It could be 2 or 3, right now it is NY or GC or IBF
Hi,
I don't understand what you want.please eyplain a bit more detailed
Andy
I don't understand what you want.please eyplain a bit more detailed
Andy
sorry, wrong posting. cancel my remark!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Answer = IIf(InStr(1, InputText, "_"), Mid(InputText, 3, 4), Mid(InputText, 3, 2))
HTH
dragontooth