Import Data Into Limited Number of Columns

[LNORG]

I have a text file that is delimited by @. Each time you see the @ it means 'tab'. However, I want this to go into a spreadsheet that is only 4 columns wide. So, if the data in getting put into the 4th column, and there is a @, then it needs to 'tab' to the next row in the first column.

Is there a way to 'preset' my spreadsheet to limit entry to the first 4 columns, so that when I do the import, the 'tab' from column D will 'go to' the next row in column A?

FYI: There could potentially be more than one @ in a row. My sample data will have the name of the column in which it is supposed to land.
textA @ textB @@ textD @@ textB @@@ textA @@@@ textA

Any thoughts?

 

[Domenic]

Try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] x [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Data [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]

    r = 1
    [color=darkblue]Open[/color] "C:\Path\sample.txt" [color=darkblue]For[/color] [color=darkblue]Input[/color] [color=darkblue]As[/color] #1  [color=seagreen]'Change the path and filename accordingly[/color]
        [color=darkblue]Do[/color] [color=darkblue]Until[/color] EOF(1)
            Line [color=darkblue]Input[/color] #1, Data
            x = Split(Data, "@")
            Cnt = LBound(x)
            [color=darkblue]Do[/color]
                [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] 4
                    Cells(r, i).Value = Trim(x(Cnt))
                    Cnt = Cnt + 1
                    [color=darkblue]If[/color] Cnt > [color=darkblue]UBound[/color](x) [color=darkblue]Then[/color]
                        r = r + 1
                        [color=darkblue]Exit[/color] [color=darkblue]Do[/color]
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]Next[/color] i
                r = r + 1
             [color=darkblue]Loop[/color]
        [color=darkblue]Loop[/color]
    [color=darkblue]Close[/color] #1
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "Completed...", vbInformation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[LNORG]

AMAZING!!! Works like a charm! Thank you so much!

Domenic, you made my day!