Text to columns

Question

Hi

I am trying to get from Table 1 to Table 2. I've tried the Text to Column tool using : as a separator. Unfortunately, I get stuck with the items on the second line within the comments cell. Any ideas?

Table 1     IDComments 1May 12, 2020 4:33:58 PM - Dblue: comment comment comment comment commentcommentcomment commentcommentcomment
May 12, 2020 4:34:07 PM - Dgreen: Assigned to "Dbrown". Changed status to "comment comment". 2Jun 22, 2020 10:30:23 AM - MacY: comment comment comment comment commentcommentcomment commentcommentcomment comment comment comment comment commentcommentcomment commentcommentcomment.  
Jun 22, 2020 10:30:32 AM - Macx: Assigned to "MashnitskiY". Changed status to "comment comment". 3Apr 20, 2020 10:04:27 AM - Dbrown: comment comment comment comment commentcommentcomment commentcommentcomment comment. 
Apr 20, 2020 10:04:34 AM - Dbrown: Assigned to "Dbrown". Changed status to "comment". 4Dec 12, 2020 4:33:58 PM - Dblue: Ccomment comment comment comment commentcommentcomment
Dec 12, 2020 4:34:07 PM - Dbrown: Assigned to "Dbrown". Changed status to "comment".    Table 2     IDCommentsNew column1May 12, 2020 4:33:58 PM - Dblue: comment comment comment comment commentcommentcomment commentcommentcomment
May 12, 2020 4:34:07 PM - Dgreen: Assigned to "Dbrown". Changed status to "comment comment".comment comment comment comment commentcommentcomment commentcommentcomment2Jun 22, 2020 10:30:23 AM - MacY: comment comment comment comment commentcommentcomment commentcommentcomment comment comment comment comment commentcommentcomment commentcommentcomment.  
Jun 22, 2020 10:30:32 AM - Macx: Assigned to "MashnitskiY". Changed status to "comment comment".comment comment comment comment commentcommentcomment commentcommentcomment comment comment comment comment commentcommentcomment commentcommentcomment.   3Apr 20, 2020 10:04:27 AM - Dbrown: comment comment comment comment commentcommentcomment commentcommentcomment comment. 
Apr 20, 2020 10:04:34 AM - Dbrown: Assigned to "Dbrown". Changed status to "comment".comment comment comment comment commentcommentcomment commentcommentcomment comment.  4Dec 12, 2020 4:33:58 PM - Dblue: Ccomment comment comment comment commentcommentcomment
Dec 12, 2020 4:34:07 PM - Dbrown: Assigned to "Dbrown". Changed status to "comment".Ccomment comment comment comment commentcommentcomment

Many thanks,

Sylvia

DavidP · Answer

Hi @SylviaK

I would use a Text to Rows with 
 as delimiter to split each row into 2, then use a sample tool to only look at the 1st line of every ID. In this case, you're looking for a column split where ": " appears (colon followed by a space), which can be done with a simple regex statement

(.+):\s(.+)

The 2nd column contains the comments, so you can use the ID field to join this back to the original data.

colon space regex split.yxmd

clmc9601 · Answer

Hi Sylvia,

Since you are trying to grab a very specific section of the text, I'd recommend using Regex. The regex I wrote is dependent on the date placement and embedded newlines. It extracts all the text between the two sets of "date - username:". If your data is formatted differently, the regex will have to be slightly different, but using regex should still do the trick.

Hope this helps!

Comments.yxzp