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.

Code:

---

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

Code:

---

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

---

Please, post a sample Word - file

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.

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

Not much detail to go on, maybe ...?

Code:

---

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

---

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

Either zip it, or change its name to .zip, and attach.

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.

It's not clear how many lines your file represents. Nor the meaning of

Code:

---

|

---

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.

Quote:

---

Originally Posted by Paul_Hossler

Not much detail to go on, maybe ...?

Code:

---

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.

Code:

---

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:

Code:

---

Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)

---

Attachment 28032

ok here's a plain text version, zipped.

Code:

---

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

---

…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.

…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):

Code:

---

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

---