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
Solved! Go to Solution.
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".
Thanks! This solution provided the exact behavior I was seeking. Appreciate the help!