Split colum. Custom delimiter based on dynamic value

[NataliaOrtega]

Hi there.

I hope someone can help me. I am working in a customer service centre and I need to extract fom a text column, thetime  when one of the agents makes the first response to each case.

The problem is that all case comments are in one cell and the delimiter to split the column (from right to left) is a the agent name following by the text " (comments)".

I created an auxiliar column with the formula  << [Assigned to]&" (Comments)" >>

Please ee screenshot below. I flagged in red the text I need:

One of the problems I have is that the Assign to column is a name, not an ID, so all of them are very different.

Is it possible to use a custome delimiter taken from a column?

I also thought to use the function Extract text before delimiter, but again, is it possible to take that delimiter from a cell value?

Thank you for your kindly help

[ronrsnfld]

In your title, you write about "Split Column", but in your question, you write you want to "extract" something from the text string. Assuming it is the latter, and you want just that first date, you could use Text.Split with space as delimiter and just return the first item in the list.

[NataliaOrtega]

Hi.

Thank you for your message 🙂

The first date, neither the last is not the one I am trying to extract.

The one I need is the latest from the person in the column [Assigned to] which also include the text " (Additional commets)". Sometimes it can include other text as "(Working notes)", which is not what I need. That is why I created the column [Delimiter].

Thank you.

[ronrsnfld]

Just add a custom column using the Text.Split function. The delimiter can be the string you select from the other column. EG based on your screenshot, the custom column formula might look like =Text.Split([Case Comments],[Delimiter]). Then you can examine the resultant List to see which item contains the earliest date.

If you need more help, I suggest you post a realistic example that shows the variability in your data. Be sure to post it as TEXT which can be copy/pasted. Screenshots are virtually useless for this.