Extract matching lines from .txt

sendlerad · Oct 9, 2012

Hello,

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

Code:

[email protected]
[email protected]
[email protected]
[email protected]

other .txt file contains lines like-

Code:

[email protected],name1
[email protected],name2
[email protected],name3
[email protected],name4
[email protected],name5
[email protected],name6
[email protected],name7
[email protected],name100

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

Code:

[email protected],name1
[email protected],name2
[email protected],name3
[email protected],name4

from second .txt file,

There should be some regex, excel function for this but i cant find any solution

s0ap · Oct 10, 2012

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.

adh0c · Oct 12, 2012

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

mjack · Nov 8, 2012

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

hpv222 · Nov 8, 2012

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

Extract matching lines from .txt

sendlerad

Elite Member

s0ap

Regular Member

adh0c

Newbie

mjack

Newbie

hpv222

Power Member

Main Menu

Marketplace

Making Money

BlackHat World