Extract text between delimiters that contain special character

[CharC]

Hi, 

I have an excel file exported from MS Planner. The file is a list of tasks.

There's a column called "Description", which is description of the task, which is a multi-line text column.

In the tasks on Planner, sometimes strings are bolded. The bolded strings have the asterisks ** around them in the "Description" column in the excel file.

I want to extract NAME 1 in the below example, where "Round 1" and "Round 2" are bolded in the tasks (have ** around them in excel):

**Round 1** - NAME 1

**Round 2** - Name 2

When I tried Text.BetweenDelimiters([Description], "**Round 1** - ", **Round 2**") to extract NAME 1, it gives me null.

I've also tried removing ** as a step and then do Text.BetweenDelimiters([#"Description-clean"], "Round 1", "Round 2"), it still gives me null.

How can I extract NAME 1 in this case?

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[dufoq3 · Accepted solution]

Hi @CharC ,

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0tIKyi/NS1Ew1NJS0FXwc/R1VTBUitVBSBhBJBJzUxWMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hours = _t]),
    #"Inserted Text After Delimiter" = Table.AddColumn(Source, "Text After Delimiter", each Text.AfterDelimiter([Hours], " - "), type text)
in
    #"Inserted Text After Delimiter"

[CharC]

Thank you for your reply. Does it mean I need to load the xlsx file as json? 

[dufoq3]

No, read note below my post.

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.