Text to columns

[SylviaK]

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
ID	Comments
1	May 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".
2	Jun 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".
3	Apr 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".
4	Dec 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
ID	Comments	New column
1	May 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 commentcommentcomment
2	Jun 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.
3	Apr 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.
4	Dec 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

[clmc9601]

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!

[DavidP]

Hi @SylviaK 

I would use a Text to Rows with \n 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.