Removing text from Cell that does not fit pattern

[jzlamal]

I have an Excel file that has additional commentary added to Cells and multiple values that I need to capture also in the same cell.  The value of a single cell in the spreadsheet may look something like:

This is my comment number 1

This is my comment number 2 

John:Silver

This is. my comment number 3

Andrew:Gold

This is my comment number 4

What I am trying to extract from this cell is:

John:Silver

Andrew:Gold

I have been reading about text to column parsing but that seems to expect the data to be more structured.   I am currently experimenting with the RegEx parsing option, but I am not sure it would return the multiple values in the cell.   Has anyone done this already?   

Thank you!

John

[binuacs · Accepted solution]

@jzlamal One way of doing this with the Regex_Tokenize method

[gaoa]

Hi @jzlamal 

Another way is to use text to column tool with the configuration below, then filter out the lines that contain the word "comment".

[jzlamal]

Thanks!   This solution provided the exact behavior I was seeking.   Appreciate the help!