We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

# String manipulation

on
Medium Priority
212 Views
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"

Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT

Commented:
Hi jitganguly,

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

HTH
dragontooth

Commented:
No boss, The IBF is returning IB only, it has to be IBF
High School Computer Science, Computer Applications, Digital Design, and Mathematics Teacher
CERTIFIED EXPERT
Top Expert 2009

Commented:
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?

Commented:
It could be 2 or 3, right now it is NY or GC or IBF

Commented:
Hi,

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

Andy

Commented:
sorry, wrong posting. cancel my remark!
Commented:
may be

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

Not the solution you were looking for? Getting a personalized solution is easy.

High School Computer Science, Computer Applications, Digital Design, and Mathematics Teacher
CERTIFIED EXPERT
Top Expert 2009

Commented:
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

##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile