extracting a string from a line of text

[tefuzz]

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

[snb]

Please, post a sample Word - file

[p45cal]

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.

[macropod]

Cross-posted at: extracting a string between 2 characters (excelforum.com) (https://www.excelforum.com/excel-programming-vba-macros/1342639-extracting-a-string-between-2-characters.html)
Please read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

[Paul_Hossler]

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

[tefuzz]

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

[p45cal]

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

[tefuzz]

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.

[snb]

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.

[tefuzz]

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)

[tefuzz]

28032

ok here's a plain text version, zipped.

[snb]

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

[p45cal]

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

[p45cal]

…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