How to specify 'cut off point' for pulling data from within a cell

[pete212]

Hello, I have the below code that inserts data from a text file into cell A1, if the text file contains the string "Checking for drops":

Dim FileNum As Long, TotalFile As String
FileNum = FreeFile
Open "C\pete.txt" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum , , TotalFile
Close #FileNum

If InStr(TotalFile, "Checking for drops") Then Range("A1") = Mid(Split(TotalFile, "Checking for drops")(1), 2, 7190)

The 7190 shown in red is the number of characters it pastes into A1 after this string. The problem I have is that this character limit can vary each time the code runs. 7190 is the worst case sceanrio, so often A1 contains data beyond what I actually need.

Is there a way to define a cut off string, so that rather than defining a character limit i can pass a second string that tells it to stop when it reaches that string? E.G. "Checking for drops" is where I need it to start and "show spp" is where I need it to stop.

If its easier, instead we could query A1 for "show spp" directly and paste the contents into another cell, rather than trying to deal with it straight from the text file.

I was thinking in B1 I could do a "=FIND("show spp",A1)" to get the character number of where show spp appears and then in C1 a "=LEFT(A1,1,<b1>B1)" but just seeing if there is any easier way?</b1>

 

[JackDanIce]

A guess, but perhaps:

If InStr(TotalFile, "Checking for drops") Then Range("A1") = Mid(Split(TotalFile, "Checking for drops")(1), 2, Mid(Split(TotalFile, "show spp")(1), 1))