Please help with data extraction from text!

[MarkusDude]

[FONT=&quot]Hello, once again I am here to ask for your help. Thanks you folks in advance. I have spent almost all day trying to get this done but no success. Please help!<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]I have a text file that came from a web form. I have to convert this text (see shortened text below) to excel so that I will have name, last name, email, address... etc in columns. Something like:<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]FirstName LastName email Company<o></o>[/FONT]
[FONT=&quot]Mark Smith smith@atheynest.net Maguna Police Department<o></o>[/FONT]
[FONT=&quot]John Horm john.horms@adessthr.com horms auto auctions<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]<o>==============</o>[/FONT]
[FONT=&quot]<o>SHORTENED SAMPLE TEXT
</o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]From: Mark Smith<smith@atheynest.net><o></o></smith@atheynest.net>[/FONT]
[FONT=&quot]Date: Wed, 06 Oct 2010 09:14:34 -0500<o></o>[/FONT]
[FONT=&quot]To: webmaster@myplace.com<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]Tool Registration Form<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]Submitted From: My company inc <o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]First Name: Mark <o></o>[/FONT]
[FONT=&quot]Last Name: Smith<o></o>[/FONT]
[FONT=&quot]E-mail: smith@atheynest.net<o></o>[/FONT]
[FONT=&quot]Company: Maguna Police Department<o></o>[/FONT]
[FONT=&quot]Phone: 9706414582<o></o>[/FONT]
[FONT=&quot]Fax: 9707335293<o></o>[/FONT]
[FONT=&quot]Address: 209 West Wiltun Ave<o></o>[/FONT]
[FONT=&quot]Address2: <o></o>[/FONT]
[FONT=&quot]City: Appleton <o></o>[/FONT]
[FONT=&quot]State: New York<o></o>[/FONT]
[FONT=&quot]Province: <o></o>[/FONT]
[FONT=&quot]Zipcode: 59915-1613<o></o>[/FONT]
[FONT=&quot]Country: USA<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]Set: <o></o>[/FONT]
[FONT=&quot]Purchased From: <o></o>[/FONT]
[FONT=&quot]Purchase Date: <o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]Comments:<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]IP Address: 24.408.44.207<o></o>[/FONT]
[FONT=&quot]Remote Host: CPE-29-208-48-207.net.res.rr.com<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]From: John Horm<john.horm@athsa.com><o></o></john.horm@athsa.com>[/FONT]
[FONT=&quot]Date: Wed, 06 Oct 2010 09:15:17 -0500<o></o>[/FONT]
[FONT=&quot]To: webmaster@myplace.com<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]Tool Registration Form<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]Submitted From: High Tech Tools <o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]First Name: John<o></o>[/FONT]
[FONT=&quot]Last Name: Horm<o></o>[/FONT]
[FONT=&quot]E-mail: john.Horm@adrthsa.com<o></o>[/FONT]
[FONT=&quot]Company: horms auto auctions<o></o>[/FONT]
[FONT=&quot]Phone: 365788762<o></o>[/FONT]
[FONT=&quot]Fax: <o></o>[/FONT]
[FONT=&quot]Address: 16800 nw 48 ave<o></o>[/FONT]
[FONT=&quot]Address2: <o></o>[/FONT]
[FONT=&quot]City: Queen Land<o></o>[/FONT]
[FONT=&quot]State: fl<o></o>[/FONT]
[FONT=&quot]Province: <o></o>[/FONT]
[FONT=&quot]Zipcode: 38054<o></o>[/FONT]
[FONT=&quot]Country: <o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]Set: <o></o>[/FONT]
[FONT=&quot]Purchased From: <o></o>[/FONT]
[FONT=&quot]Purchase Date: <o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]Comments:<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]IP Address: 247.242.83.12<o></o>[/FONT]
[FONT=&quot]Remote Host: surf01.ad[/FONT][FONT=&quot]thy[/FONT][FONT=&quot].com[/FONT]

 

[John_w]

Try this:

Sub Import_Web_Form_Data()

    Dim filename As String, line As String
    Dim fn As Integer
    Dim row As Long
    
    filename = "C:\path\to\your\webformdata.txt"
    row = 1
    
    fn = FreeFile
    Open filename For Input As fn
    While Not EOF(fn)
        Input #fn, line
        If InStr(line, "First Name: ") = 1 Then
            row = row + 1
            Cells(row, "A").Value = Split(line, ": ")(1)
        End If
        If InStr(line, "Last Name: ") = 1 Then Cells(row, "B").Value = Split(line, ": ")(1)
        If InStr(line, "E-mail: ") = 1 Then Cells(row, "C").Value = Split(line, ": ")(1)
        If InStr(line, "Company: ") = 1 Then Cells(row, "D").Value = Split(line, ": ")(1)
    Wend
    Close fn
    
End Sub

 

[MarkusDude]

Dude,

Thanks a lot. It worked like magic (maybe it was magic...)

Thanks a lot.

 

[Cindy Ellis]

The email addresses in you post look real. If they are, you should ask the admin to edit the post to remove them so that spam agents don't grab them!
(Just a helpful hint)

 

[MarkusDude]

Thanks Cindy. Very kind of you but they are actually phony.

Markus