parsing lists

[Brook70458]

Hi,

It has been some time since my being into developing vba code in Excel. I am [beyond] rusty in my skillset.

I receive a report generated in Excel and need it parsed correctly, and the report appended each time I update it.
(Until I figure out--LEARN--syntax, I would like the append function to be a choice (option).

Example:

Col. D Col. E Col. F
[TABLE="width: 313"]
<tbody>[TR]
[TD]Input Data
[/TD]
[TD] Prefix[/TD]
[TD] Suffix[/TD]
[/TR]
[TR]
[TD]100.100.100.100:100[/TD]
[TD] 100.100.100.100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]100.100.100.100:1000 [/TD]
[TD] 100.100.100.100[/TD]
[TD="align: right"] 1000[/TD]
[/TR]
[TR]
[TD]100.100.100.100:10 [/TD]
[TD] 100.100.100.100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Original Input data is col. D

copy & trim characters up to, and including, 'colon' from col. D to col. F

delete 'colon' from col. B

[I want a net of three columns with original INPUT data in 1st column and the data trimmed and parsed to second and third column as shown. Row length will vary to infinity.]

I would also like better wording for this question. This will help me think and speak syntax, aka "vbaeeze".

Thank you in advance.

 

[Rick Rothstein]

Give this macro a try...

Sub GetPrefixSuffix()
  Range("A2", Cells(Rows.Count, "A").End(xlUp)).TextToColumns Range("B2"), xlDelimited, , , , , , , True, ":"
End Sub