extract text between delimiters from the very last

[Anonymous]

hi guys

i need to extract text between delimiter

my start delimiter *

my end delimiter *

sometimes i need to extract text from the very last right

you may refer to the below screen shot

if you observe that i kept my text between delimiters in Red ( Comment column )

*  the yellow represent what will happen if i choose extract function from add column tab ( which sometimes give me wrong output)
*  the blue column that what i need to get.

so how do i get the below column result ?  i need to extract text between delimiters from the last right

[ronrsnfld · Accepted solution]

Text.BetweenDelimiters([Comments],"*","*",{5,RelativePosition.FromEnd})

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcyxCoMwEADQXzkyHi1JTgM2q9DBpdCOJoPQgNJwBzH+f6WLQ/2A98ZRDTIzCIOx2pAmQw6o8c550wTGfsoZ+sS1JLyXaXtfER91TmVFVPFycGr1Pvy47TzdvDWnHA4fOPAgZ7rzrt31c1k/8JK81UX436sYvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Comments = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Comments", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        Text.BetweenDelimiters([Comments],"*","*",
        {5,RelativePosition.FromEnd}))
in
    #"Added Custom"

[v-nmadadi-msft]

Hi @Anonymous,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[v-nmadadi-msft]

Hi @Anonymous ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[dufoq3 · Accepted solution]

Hi @Anonymous, another solution:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcyxCoMwEADQXzkyHpZcTgM2q+DQRWhHzSA0oDRcIMb/r3RxqR/w3jiqR1oEkgAZTayZ2ALXzlpH9STYzTFCF6TkgH2e9zfcABCHsoS8ISpfnQM3+kh+g2kd352hi+EMJrnQrbPNoZ/r9oFXintZk/zxyvsv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_Extracted = Table.AddColumn(Source, "Extracted", each Text.BetweenDelimiters(Text.Replace([Column1], "**", "|"), "*", "*", {1, RelativePosition.FromEnd}), type text)
in
    Ad_Extracted

[ronrsnfld · Accepted solution]

Text.BetweenDelimiters([Comments],"*","*",{5,RelativePosition.FromEnd})

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcyxCoMwEADQXzkyHi1JTgM2q9DBpdCOJoPQgNJwBzH+f6WLQ/2A98ZRDTIzCIOx2pAmQw6o8c550wTGfsoZ+sS1JLyXaXtfER91TmVFVPFycGr1Pvy47TzdvDWnHA4fOPAgZ7rzrt31c1k/8JK81UX436sYvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Comments = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Comments", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        Text.BetweenDelimiters([Comments],"*","*",
        {5,RelativePosition.FromEnd}))
in
    #"Added Custom"

[Omid_Motamedise]

hi, use the following code

let
Source = YourTable,

LastStartPos = Table.AddColumn(Source, "LastStart", each Text.PositionOf([Comment], "*", Occurrence.Last) + 1, Int64.Type),

LastEndPos = Table.AddColumn(LastStartPos, "LastEnd", each Text.PositionOf([Comment], "*", Occurrence.Last, [LastStart]), Int64.Type),

ExtractedText = Table.AddColumn(LastEndPos, "Extracted", each
if [LastStart] > 0 and [LastEnd] > [LastStart] then
Text.Middle([Comment], [LastStart], [LastEnd] - [LastStart])
else
null
),

FinalTable = Table.SelectColumns(ExtractedText, {"Comment", "Extracted"})
in
FinalTable

[PwerQueryKees]

Your problem is multiple lines in one cell.

No worries

= Table.AddColumn(Your_Source_Here, "Custom", each 
let
    lines = Text.Split([Comments], "#(lf)"), // split in lines
    last_line = List.Last(lines)             // get the last line
in 
    Text.BetweenDelimiters(last_line, "*", "*")) // normal extract between delimiters

Produces:

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[Anonymous]

thank you for your reply

i tried to copy and paste your comment into my power query and it wont work it shows me the below

i will be thankful if you can guide me and tell me what should i do to apply your step into my query.  should i add a step ?  and what do you mean by your_sourse_here?  do you mean my table name?

kindly advice

[v-nmadadi-msft]

Hi @Anonymous,

Please use get data

And select the data source where your data which you would like to transform exist.
For ex if we select CSV, we will get M query auto generated like this

and if we choose to manually enter data  

We will get the M query similar to this

This is how we get your_source_here, After this step go ahead and paste the rest of the code which will help you to transform the data to your desired form.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS.
Thanks and Regards

[v-nmadadi-msft]

Hi @Anonymous ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.