Solved: Finding a character in a text string

[austenr]

I need to find the first occurence of '2006' in a space delimited text file. I need to do that in order to start parsing a text file based on that string. I receive a text file that has a query and other misc. characters before the text I want to start parsing with. For example the first 125 characters might be throw away and the first character I want to parse with is character 126. Any ideas?

[OBP]

Assuming that you can set the file content to a string variable, you can set "2006" to a search variable and then use "instring" to search for the occurance and position of it.
See "InStr Function" in the Visual Basic Editor's Help (Not Excel help).
You can then use right(stringname,value) to discard the left part of the string.
If the file comes in to Excel as a column rather than a row then you would need a for/next, do/while loop or with Cells loop.

[ALe]

Function GetStringAfterCh(MyString As String, Ch As String) As String
GetStringAfterCh = Mid(MyString, InStr(1, MyString, "2006"), 1000)
End Function
Sub test()
MsgBox GetStringAfterCh(MyString:="abcde2006efghilmn", Ch:="2006")
End Sub

[OBP]

Does it come in on one row or in one column?

[OBP]

Nice one ALe!

[TonyJollans]

What aboutSub test()
MsgBox Split("abcde2006efghi2006lmn", "2006", 2)(1)
End Sub?

[ALe]

WOW

[austenr]

Thanks for the reply's. That works as a starting point well, another option I am considering is parsing the file as is and the deleting the rows that do not have 2006 as the first four characters in the first cell of the row. I was thinking of using MID to check if the first 4 characters are 2006. Not sure how I would blend that in to an IF statement or another statement though.

[jungix]

You can just use:

If Left(YourString,4) = "2006" Then
....
EndIf