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

jitganguly
jitganguly asked
on
Medium Priority
212 Views
Last Modified: 2010-05-01
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

CERTIFIED EXPERT

Commented:
Hi jitganguly,

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

HTH
dragontooth

Author

Commented:
No boss, The IBF is returning IB only, it has to be IBF
Mike TomlinsonHigh 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?

Author

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

Ask the Experts
Mike TomlinsonHigh 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

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.