Text File multiple random values to excel columns...

[Clintano]

here is an example of the text file:

Bob Noname
robertnoname@comcast.net
Melissa noname
chanoname@aol.com
Nancy noname
12195551212
nancynoname@gmail.com
Michael noname
16305551212
Dyana noname
dyanasonname@yahoo.com
Kiley noname
kcnoname@yahoo.com
Tammy noname
tammynoname@gmail.com
Steven noname
nynoname@yahoo.com
Dan nonameguy
17085551212
Henry
17085551212

I want to import that text file and extract out the name into its own column and the email into its own column and the phone number into its own column without either going through the whole text file inputting commas.. also I would like the correct values to flow into the correct columns in excel, in other words, not having the phone numbers show up in the email column etc... Bonus would be to have the name separated into first and last name also...

Any help would be appreciated, I kinda understand how programs work ie loops etc. because of previous background with anci C, but this is new as far as what code to use where... So I am a noob, if that helps your response be more step by step...

Thanks Clint

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

Sub Import()
    Dim ShNew As Worksheet
    Dim r As Long
    Dim FileName As String
    Dim FileNum As Integer
    Dim Data As Variant
    Set ShNew = Worksheets.Add
    With ShNew
        .Range("A1:C1").Value = Array("Name", "Email", "Phone")
        r = 1
'       *** Change path and file name to suit ***
        FileName = CreateObject("Wscript.Shell").SpecialFolders("Desktop") & "\Test.txt"
        FileNum = FreeFile
        Open FileName For Input As #FileNum
        Do While Not EOF(FileNum)
            Line Input #FileNum, Data
            If InStr(Data, "@") > 0 Then
                .Cells(r, 2) = Data
            ElseIf IsNumeric(Data) Then
                .Cells(r, 3) = Data
            Else
                r = r + 1
                .Cells(r, 1) = Data
            End If
        Loop
        Close #FileNum
        .Cells.EntireColumn.AutoFit
    End With
End Sub

 

[ParamRay]

.
.

You got there first. Mine was almost identical.

 

[Clintano]

Thanks, this I would put in a macro correct? Just not sure of how to make this data populate in Excel, sorry, I just have never run a macro before../

 

[Andrew Poulsom]

Open a new workbook, press Alt+F11 to go to the Visual Basic Editor and choose Insert|Module from the menu. Copy the code I posted and paste it into the window on the right. Change the path and file name to suit, then position your cursor in the procedure and press F5.

 

[Clintano]

Open a new workbook, press Alt+F11 to go to the Visual Basic Editor and choose Insert|Module from the menu. Copy the code I posted and paste it into the window on the right. Change the path and file name to suit, then position your cursor in the procedure and press F5.

I am getting a blank goto window when I do what you said above...? Please advise... I changed \Test.txt to the path with the filename, was that correct?

 

[Andrew Poulsom]

No, you press F5 in the Visual Basic Editor when the cursor is in the procedure, not on a worksheet. On a worksheet you can press Alt+F8 and run the macro,

 

[Clintano]

No, you press F5 in the Visual Basic Editor when the cursor is in the procedure, not on a worksheet. On a worksheet you can press Alt+F8 and run the macro,

Got it finally, put file on desktop and created appropriate filename for \Test.txt and it fired right up..

Andrew, THANK YOU SO MUCH!!!!