align input data

[maheshr68]

Greetings!!

have input data as below--

1) IP Address Country Region City Latitude/
Longitude ZIP Code Time Zone
137.101.228.252 UNITED KINGDOM - - 54.15
-4.473 - +00:00
Net Speed ISP Domain
DSL AKHTER GROUP PLC NETSOLIR.COM
IDD Code Area Code Weather Station
44 - UKXX0233 - ESKMEALS

2) IP Address Country Region City Latitude/
Longitude ZIP Code Time Zone
77.101.232.214 UNITED KINGDOM ENGLAND LONDON 51.517
-0.105 - +00:00
Net Speed ISP Domain
DSL BROADBANDAUDIT VIRGINMEDIA.COM
IDD Code Area Code Weather Station
44 - UKXX0085 - LONDON

Want to arrange this data into excel fields named accordingly and in a single line. Currently it is not possible to even import fields as they are not to be located in the first line.

Please assist me to get output as in the attached excel file

Best Regards
Mahesh

[p45cal]

Whilst this is possible, it might not be very robust.
I suspect this data has been brought into Excel from another kind of file. Could you attach that file (perhaps a shorter version) here?

In the meantime, a solution of sorts (puts lat/long in separate cells) and in the attachment:
Sub blah()
Set SourceWS = Sheets("Input Data")
Set DestWS = Sheets("Output Data Format")
With SourceWS
Set Pattern = .Range("$A$1:$E$1,$A$2:$C$2,$A$4:$C$4,$A$6:$C$6")
With .Columns(1)
Set c = .Find("IP Address", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set c = .FindNext(c)
Destrow = DestWS.Cells(DestWS.Rows.Count, "A").End(xlUp).Row + 1
colno = 1
For Each cll In Pattern.Offset(c.Row + 1).Cells
DestWS.Cells(Destrow, colno).Value = cll.Value
colno = colno + 1
Next cll
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End With
End Sub

[maheshr68]

Greetings p45cal

Yes you are perfectly right -- the input format is as attached in the text file.

Best Regards
Mahesh

[p45cal]

The file 'OUTIM ip2locationIPsFULL.txt' is not better than what you attached before - I expected 1 record per line. So carry on with my suggested code earlier.