Finding Text

[RhinoNeil]

I have a workbook containing downloaded Emails and I now need to identify a particular code appearing somewhere in the Subject field. I am using Excel 2007.

The code will always be 8-numbers and should start and end with a space, unless at start or end of Subject Line (but often people put punctuation instead - e.g. , or . There also may be a code of 10-numbers that I do not want to incorrectly identify.

I am using "Like" to identify the pattern but it does not seem to be working correctly.

 If Subject_Array(Data_Row) Like "*[!#]########[!#]*" = True Or _
            Subject_Array(Data_Row) Like "*[!#]########" = True Or _
            Subject_Array(Data_Row) Like "########[!#]*" = True Then

As I read the help section, the "*[!#]########[!#]*" should look for eight numbers where the characters either side are not a number. The second and third tests are for the cases when the code starts/ends the Subject line.

For the example "Shipment Notification WARE - GSK SOUTH AFRICA - 10097777 - 577758340010001 - AIR" it correctly identifies the "10097777" as valid.
However for "RE: Packing list Notification>Barnard Castle>Israel>FPA: 578707300010001, 578688000000000" it finds the first two tests as TRUE. It results in a code of "00010001"

Can anybody tell me why "Like" gives a TRUE result for the second example and also how to fix it?

Thanks

 

[tlowry]

Here's a UDF that may work for you:

Function find8(sIn) As String
    Dim shold, i
    find8 = ""
    shold = Split(sIn, " ")
    For i = 0 To UBound(shold)
        If shold(i) Like "########" Then
            find8 = shold(i)
            Exit Function
        End If
    Next
End Function

Sub test()
    MsgBox find8("Shipment Notification WARE - GSK SOUTH AFRICA - 10097777 - 577758340010001 - AIR")
    MsgBox find8("RE: Packing list Notification>Barnard Castle>Israel>FPA: 578707300010001, 578688000000000")
End Sub

 

[RhinoNeil]

tlowry, thanks but if the code was entered with a comma, i.e. "10097777," won't split fail to catch it.
As I said, I need to find an 8 number code which, may or may not start/end with a , or a ; and it cannot take a 10 number code.
Examples of how people enter the code -
" 10097777 "
" 10097777,"
"10097777 - Shipment"
"Shipment - 10097777"
"Shipments 10097777/10097778, Shipment"

All of which I want the code to find, but also ignore -
" 1009777712 "

 

[s hal]

Without trying to write the code for you (mostly because I don't know what your code is doing necessarily at this point), have you tried to include a like ######## condition AND not like ########## where the second is the longer string of numbers that you don't want?

 

[tlowry]

Next:

Function find8(sIn) As String
    Dim shold, i
    shold = sIn
    For i = 1 To Len(shold)
        If Not Mid(shold, i, 1) Like "#" Then Mid(shold, i, 1) = " "
    Next i
    Dim arr
    Do While InStr(shold, "  ") > 0
        shold = Replace(shold, "  ", " ")
    Loop
    arr = Split(shold, " ")
    For i = 0 To UBound(arr)
        If arr(i) Like "########" Then
            find8 = arr(i)
            Exit Function
        End If
    Next i
End Function

Sheet1

*	A	B
Examples of how people enter the code -	*
10097777 - Shipment
Shipment - 10097777
Shipments 10097777/10097778, Shipment
*	*
*	*
*
*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:270px;"><col style="width:111px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]10097777[/TD]
[TD="align: right"]10097777[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"] 10097777,[/TD]
[TD="align: right"]10097777[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]10097777[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]10097777[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]10097777[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]1009777712[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =find8(A1) B2 =find8(A2) B3 =find8(A3) B4 =find8(A4) B5 =find8(A5) B6 =find8(A6) B7 =find8(A7) B8 =find8(A8) B9 =find8(A9) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[RhinoNeil]

Thanks for your reply, this will help me get what I want.
I never knew you could "not like" as well as "like" (still very early on with macros). The help function never mentions it so that will help a lot.