Consulting

Results 1 to 14 of 14

Thread: extracting a string from a line of text

  1. #1

    extracting a string from a line of text

    I have a macro that runs through a text file checking for instances of a string, and then extracting some characters after the string is identified. A while back I was helped with coding the line below to do just that.
    sn = Split(Replace(Join(Filter(Filter(Split(.Content, ")"), "GS1"), "( ", 0), "|"), " GS1    : (", "'"), "|")
    that line of code helped me pull the "XXXXXXXXXXXXXXXXXXXXX" from the text file for every instance like the line of text below:
    3) GS1 : (XXXXXXXXXXXXXXXXXXXXX)

    now I have a different text file, with similar data, but with a different text structure that I need to pull the same string from.

    the new line(s) of text I need to pull from are structured like the line below:
    04:06:57.450| SendIOS |XXXXXXXXXXXXXXXXXXXXX| 00000| X134| | 077| | | 07| 1| 4| | Del_Day_Ind| 3

    I need to modify the line of code above, to still give me the XXXXXXXXXXXXXXXXXXXXX as the result. the rest of the macro is already setup to run through the entire text file and uses the line of code above to extract the XXXXXXXXXXXXXXXXXXXXX each time and enter it in a cell, which works as expected for my original conditions. So it's just the line of code to extract the XXXXXXXXXXXXXXXXXXXXX from the new text structure that i need to modify.

    I'm no excel genius, but I've dabbled many times, and can usually make things work since I do have a basic understanding of programming...but this one is making a fool of me.
    here is the entire macro, in case that would be of assistance

    Sub open_file2()
        Dim FSO As Object
        Dim blnOpen
        strFileToOpen = Application.GetOpenFilename(Title:="Select your PASS file")
        If strFileToOpen = False Then
          MsgBox "You did not select a PASS File", vbExclamation, "!"
          Exit Sub
        Else
            Sheet9.Range("G5").Value = strFileToOpen
            With GetObject(Sheet9.Range("G5").Value)
            sn = Split(Replace(Join(Filter(Filter(Split(.Content, ")"), "GS1"), "( ", 0), "|"), " GS1    : (", "'"), "|")
            .Close 0
        End With
         Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
        End If
        Sheet9.Range("G5").clear
    End Sub

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Please, post a sample Word - file

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Looking at your sample text, it looks as if it may ultimately come from a .txt file or some other kind of file; if you could attach that it could be quite straightforward.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Cross-posted at: extracting a string between 2 characters (excelforum.com)
    Please read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Not much detail to go on, maybe ...?

    Option Explicit
    
    
    Sub Example()
        Dim s As String
    
    
        s = "04:06:57.450| SendIOS |XXXXXXXXXXXXXXXXXXXXX| 00000| X134| | 077| | | 07| 1| 4| | Del_Day_Ind| 3"
    
    
        MsgBox Extract(s, "|", 2)
    End Sub
    
    
    
    
    Function Extract(sIn As String, SepChar As String, Num As Long) As String
        Extract = Trim(Split(sIn, SepChar)(Num))  ' 0 based
    End Function
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    I have attached an example word document. the file itself is a .txt file, but the forum wouldn't allow me upload it as text...The original macro does pull from a word document, so if we had to open, and save the text file as a word document to make it work, its not the end of the world.


    The file normally contains information that some would consider sensitive so I had to replace some of the text with repeating letters so at least the structure remained intact. as noted the macro does work a it should when it pulls from the original text structure, I just need to modify it to work with the new structure
    Attached Files Attached Files

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Either zip it, or change its name to .zip, and attach.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    is the word document not sufficient? It's exactly the same as what the text file would be, except I removed a few thousand lines of text and only left a handful for the example document.

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    It's not clear how many lines your file represents. Nor the meaning of
    |
    You'd better post a zipped .txt file.

    It is not exactly as a .txt file, since the Word file contains vbcr where a txt file has vbcrlf.
    the wordfile has 'paragraphs' with 8 points 'after' distance to the next Paragraph. Impossible in a .txt file.

  10. #10
    Quote Originally Posted by Paul_Hossler View Post
    Not much detail to go on, maybe ...?

    Option Explicit
    
    
    Sub Example()
        Dim s As String
    
    
        s = "04:06:57.450| SendIOS |XXXXXXXXXXXXXXXXXXXXX| 00000| X134| | 077| | | 07| 1| 4| | Del_Day_Ind| 3"
    
    
        MsgBox Extract(s, "|", 2)
    End Sub
    
    
    
    
    Function Extract(sIn As String, SepChar As String, Num As Long) As String
        Extract = Trim(Split(sIn, SepChar)(Num))  ' 0 based
    End Function

    So when I use this method, if I use it outside of my original macro, it works for a single line. If I modify my macro to the below, I get a type mismatch.

    Sub open_file3()    Dim FSO As Object
        Dim blnOpen
        strFileToOpen = Application.GetOpenFilename(Title:="Select your PASS file")
        If strFileToOpen = False Then
          MsgBox "You did not select a PASS File", vbExclamation, "!"
          Exit Sub
        Else
            Sheet9.Range("G5").Value = strFileToOpen
            With GetObject(Sheet9.Range("G5").Value)
            sn = Extract(.Content, "|", 2)
            .Close 0
        End With
         Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
        End If
        Sheet9.Range("G5").clear
    End Sub
    
    
    Function Extract(sIn As String, SepChar As String, Num As Long) As String
        Extract = Trim(Split(sIn, SepChar)(Num))  ' 0 based
    End Function
    the type mismatch occurs on this line:
    Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)

  11. #11
    example.zip

    ok here's a plain text version, zipped.

  12. #12
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
      sn=filter(filter(split(createobject("scirpting.filesystemobject").opentextfile("G:\OF\example.txt").readall,vbcrlf),"#",0),"|")
    
      for j=0 to ubound(sn)
        sn(j)=split(sn(j),"|")(2)
      next
    
      Msgbox join (sn,vblf)
    End Sub

  13. #13
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    …and a Power Query offering. Right-click the table and choose Refresh to update, after putting the correct path and file name into cell A1 (which is a named range 'Source')
    It doesn't have to be this way; if you're having to do this on a regular basis the path can be built in. It can even be set up to process multiple files, and/or just appropriate file(s) it finds in a location at the time of the refresh. The refresh can be automatically periodic or on workbook open. No macro in the attached.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  14. #14
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    …and if you want to go hunting for the file each time, add a button to the sheet in the file I attached and assign to it this macro (yours adapted):
    Sub open_file2()
    strFileToOpen = Application.GetOpenFilename(Title:="Select your PASS file")
    If strFileToOpen = False Then
      MsgBox "You did not select a PASS File", vbExclamation, "!"
    Else
      Range("Source").Value = strFileToOpen
      Range("example").ListObject.QueryTable.Refresh BackgroundQuery:=False
    End If
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •