1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Extract matching lines from .txt

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

  1. sendlerad

    sendlerad Regular Member

    Joined:
    Sep 5, 2007
    Messages:
    307
    Likes Received:
    51
    Hello,

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

    Code:
    email1@domain1.com
    email2@domain2.com
    email3@domain3.com
    email4@domain4.com
    other .txt file contains lines like-

    Code:
    email1@domain1.com,name1
    email2@domain2.com,name2
    email3@domain3.com,name3
    email4@domain4.com,name4
    email1@domain11.com,name5
    email2@domain6.com,name6
    email3@domain9.com,name7
    email4@domain100.com,name100
    I need to match all emails from first .txt file and output only-

    Code:
    email1@domain1.com,name1
    email2@domain2.com,name2
    email3@domain3.com,name3
    email4@domain4.com,name4
    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

    Joined:
    Sep 23, 2008
    Messages:
    230
    Likes Received:
    810
    Occupation:
    :] guess
    Location:
    Congo/DRC
    Code:
    =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

    Joined:
    Apr 20, 2012
    Messages:
    11
    Likes Received:
    7
    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

    Code:
    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))
            DoEvents
            
            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
            Loop
            
            'Increment to next cell
            iContentCntr = iContentCntr + 1
        Loop
    End Sub
    
    
    
    
    
     
    • Thanks Thanks x 1
    Last edited: Oct 12, 2012
  4. mjack

    mjack Newbie

    Joined:
    Nov 8, 2012
    Messages:
    14
    Likes Received:
    0
    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.

    mjack
     
  5. hpv222

    hpv222 Power Member

    Joined:
    Feb 8, 2010
    Messages:
    736
    Likes Received:
    274
    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