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

Input Data	Prefix	Suffix
100.100.100.100:100	100.100.100.100	100
100.100.100.100:1000	100.100.100.100	1000
100.100.100.100:10	100.100.100.100	100
…
…

<tbody>
</tbody>

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