Hello everyone. I have data that looks like this:
ID | Notes |
123456 | <BR> <B>08/21/2020 at 4:51AM</B><BR> User scheduler changed the workflow state to "Backlog"<BR><BR> <BR> <B>08/14/2020 at 4:34AM</B><BR> User scheduler changed the workflow state to "Backlog"<BR><BR> <BR> <B>08/07/2020 at 12:07PM</B><BR> User scheduler changed the workflow state to "Backlog"<BR><BR> <BR> <B>07/24/2020 at 4:23AM</B><BR> User scheduler changed the workflow state to "Backlog"<BR><BR> <BR> <B>07/17/2020 at 4:23AM</B><BR> User scheduler changed the workflow state to "Backlog"<BR><BR> <BR> <B>02/25/2020 at 1:27PM</B><BR> User John Doe changed the workflow state to "Vulnerability Assigned"<BR><BR> <BR> <B>02/21/2020 at 3:31AM</B><BR> User scheduler changed the workflow state to "Open"<BR><BR> <BR> <B>02/19/2020 at 12:18PM</B><BR> User scheduler changed the workflow state to "Open"<BR><BR> <BR> <B>02/14/2020 at 8:50AM</B><BR> User System Administrator changed the workflow state to "Open"<BR><BR> |
I am trying to get to to look like this:
ID | Date | Action |
123456 | 08/21/2020 at 4:51AM | User scheduler changed the workflow state to "Backlog" |
123456 | 08/14/2020 at 4:34AM | User scheduler changed the workflow state to "Backlog" |
123456 | 08/07/2020 at 12:07PM | User scheduler changed the workflow state to "Backlog" |
123456 | 07/24/2020 at 4:23AM | User scheduler changed the workflow state to "Backlog" |
123456 | 07/17/2020 at 4:23AM | User scheduler changed the workflow state to "Backlog" |
123456 | 02/25/2020 at 1:27PM | User John Doe changed the workflow state to "Vulnerability Assigned" |
123456 | 02/21/2020 at 3:31AM | User scheduler changed the workflow state to "Open" |
123456 | 02/19/2020 at 12:18PM | User scheduler changed the workflow state to "Open" |
123456 | 02/14/2020 at 8:50AM | User System Administrator changed the workflow state to "Open" |
I am weak at parsing, so any help is very much appreciated! I have attached an excel file too that has what the original data looks like and what I would like it to look like. I have gotten the <B> and <BR> removed, but left them in this sample data in case they can be used to make it easier. I am assuming it needs to be a multi-row formula?
Thanks!
Allison
Solved! Go to Solution.
Hi @aberry0613
Here is how you can do it. Having <B> and <BR> was helpful.
Workflow:
Hope this helps : )
Thank you!
Happy to help : ) @aberry0613
Cheers and have a nice day!