Extract matching lines from .txt

Discussion in 'General Programming Chat' started by sendlerad, Oct 9, 2012.

  1. sendlerad

    sendlerad Jr. VIP Jr. VIP

    Sep 5, 2007
    Likes Received:

    I have this problem i have 2 .txt files, one contains lines like-

    other .txt file contains lines like-

    I need to match all emails from first .txt file and output only-

    from second .txt file,

    There should be some regex, excel function for this but i cant find any solution :(
  2. s0ap

    s0ap Executive VIP Jr. VIP Premium Member

    Sep 23, 2008
    Likes Received:
    :] guess
    =RIGHT( A1, LEN( A1 )-FIND( ",", A1 ) )

    This will copy anything to the right of the ',' delimiter in cell A1, use fill and sort by value to do whatever organization you want. If you need more help look up Excel's LEFT/MID/RIGHT functions.
    • Thanks Thanks x 1
  3. adh0c

    adh0c Newbie

    Apr 20, 2012
    Likes Received:
    Not sure about Regex but here is a quick Sub I wrote specially for you. (I'm bored)

    I commented it so you can understand but basically
    Column A is your "emailATdomain"
    Column C is your "emailATdomain,name1"
    Column E is where your results are output.

    Make sure there are no blank cells or it will stop.

    Goto View > Tools > Control Toolbox
    Then Drag a button onto your Sheet. Double click that button and you should get a code window. Make it look like this

    Private Sub CommandButton1_Click()
        Call FindSomething("A", "C", "E", ",")
    End Sub
    Sub FindSomething(ByVal sContentRange As String, ByVal sSearchRange As String, ByVal sResultRange As String, ByVal Delimeter As String)
        'sContentRange = "A" Range of Content to try and find
        'sSearchRange = "C"  Range of Content to Search through
        'sResultRange = "E" Range of which results are output
        Dim iResultCntr As Integer
        iResultCntr = 1
        Dim iContentCntr As Integer
        iContentCntr = 1
        'Loop through Content Range until empty cell
        Do Until IsEmpty(Range(sContentRange & iContentCntr))
            Dim iSearchCntr As Integer
            iSearchCntr = 1
            'Store the Content we are trying to search for
            Dim sContentCell As String
            sContentCell = Range(sContentRange & iContentCntr).Value
            'Loop through Search Range, Exit if found
            Do Until IsEmpty(Range(sSearchRange & iSearchCntr))
                Dim sSearchCell As String
                sSearchCell = Range(sSearchRange & iSearchCntr).Value
                If InStr(1, sSearchCell, Delimeter) Then
                    'If we find the content, output to the "Result Range"
                    If InStr(1, sContentCell, Left(sSearchCell, InStr(1, sSearchCell, Delimeter) - Len(Delimeter)), vbTextCompare) <> 0 Then
                        Range(sResultRange & iResultCntr).Value = sSearchCell
                        'Increment to next cell
                        iResultCntr = iResultCntr + 1
                        Exit Do
                    End If
                End If
                'Increment to next cell
                iSearchCntr = iSearchCntr + 1
            'Increment to next cell
            iContentCntr = iContentCntr + 1
    End Sub
    • Thanks Thanks x 1
    Last edited: Oct 12, 2012
  4. mjack

    mjack Newbie

    Nov 8, 2012
    Likes Received:
    Have you tried Textpipe Pro? It's awesome - something like this is pretty easy to do.

    To do it in excel you need to load each list into a different worksheet (page). Call the worksheet with emails and names "names" and call the worksheet without names "justemails"

    On the worksheet with the ,name you need to get the name into its own column. To do this select the whole column which contains "emailxxxx,name" and then use excel to convert "Text to Columns". On Excel 2010 it's on the "Data" ribbon.

    Once you have the column split into two columns it should be like this:
    Column A: just the email
    column B: just the name

    Now flick to your worksheet with just the emails in (no names) which is the one you're going to lookup against. Make sure all the emails are in column A and don't have any whitespaces around it.

    Go back to the main worksheet (with names).

    In row C1 type: =COUNTIF(justemails!A:A,names!A1)

    If the email in column A is in the list in "just emails" you'll get a 1 (or more).

    Copy and paste this formula all the way down column C (quickest way is to click on cell C1 then quickly double click the tiny black box in the corner of the cell).

    Now you have a worksheet with:

    email address, name, number of times the email appears in your other list

    If you copy the whole worksheet then paste -> paste as text you can order the worksheet by column C descending and then easily remove all the emails with 0 counts.

    Job done.

    If you need a clearer explanation let me know.

  5. hpv222

    hpv222 Power Member

    Feb 8, 2010
    Likes Received:
    for some reason I always use Access for this sort of thing - import 1.txt to one table, 2.txt to another table and then run a quick query, something like

    Select Table2.email, Table2.name
    From Table 2
    Where Table1.email = Table2.email

    ^^ sample code, not sure if the syntax is correct, but you should be able to figure it out in 5 minutes tops