Forum Discussion
Extract text between delimiters that contain special character
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?
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
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"
4 Replies
- Ahmedx
Super User
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
- dufoq3
Community Champion
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"
