We help IT Professionals succeed at work.

Find txt after a string

mtrussell asked
I have an EDI message that I need to return the value in the 20C: line.  The lines will be stacked as below and the number of characters is variable before and after the line so the Left, Right, Mid, Instrg don't really help me out in this.  However the 20C: line is always on the message and the number of characters after 20C will always equal 15.

How would I be able to return the value 45029, for example, in the below message either in a query or vba?

:35B:ISIN NO0010985
Watch Question

Solutions Architect
In your example there are 13 characters after "20C:" (:SEME//45029).

SELECT Mid([MyField],InStr(1,[MyField],"20C")+11,15) AS TextStr
FROM MyTable;

Returns 45029 but assumes the string you want returned is always in the same position and same number of characters as your example...
Try this
OM Gang

Public Function FindStringInFile()

On Error GoTo Err_FindStringInFile

    Dim objFSO As New FileSystemObject
    Dim objFile As Object
    Dim intSearchLen As Integer
    Dim lngPos As Long
    Dim strLine As String, strSearch As String
    strSearch = "20C"
    intSearchLen = Len(strSearch)
    Set objFile = objFSO.OpenTextFile("c:\temp\EDIMsg.txt", ForReading)
    Do Until objFile.AtEndOfStream
        strLine = objFile.ReadLine
        lngPos = InStr(strLine, strSearch)
        If lngPos <> 0 Then
            Debug.Print Right(strLine, (Len(strLine) - (lngPos + intSearchLen - 1)))
        End If

    Set objFile = Nothing
    Set objFSO = Nothing
    Exit Function

    MsgBox Err.Number & " (" & Err.Description & ") in procedure FindStringInFile of Module Module9"
    Resume Exit_FindStringInFile

End Function

In my example, I'm returning everything after '20C'.  You can adjust the length of the piece of string you want to return by changing
(lngPos + intSearchLen - 1)

(lngPos + intSearchLen + 3) will return 'ME//45029'
(lngPos + intSearchLen + 7) will return '45029'

As mvasilevsky pointed out, your example has 13 characters after the 20C but your Q stated the EDI file always has 15 after the 20C.  Adjust the expression to suit your needs.
OM Gang


I have never split points but this time you both gave me what I need in sql and vba.  both will be used in the program i am building.  

sorry for the split but in this case it is the only way i see to do this.