Extracting a number string after a two character delimiter

[m00ch]

Hello everyone, 

Could someone help me create an efficient way to extract an alphanumeric string that varies in length but always comes after the first "T#"? There may be a whole bunch of garbage before the delimiter like dates and times as well as after. There can also be a Carriage Return (CR) immediately after the string. Here is what the data looks like showing all characters and the string that I would like to extract is highlighted.

2/6/2022 3:05:16 PM: Audit: T#23A7561845685567
2/5/2024 1:57:54 PM: Audit: Following up for update.
2/3/2025 6:20:11 PM: Audit: Requested update.
1/24/2025 3:44:07 PM: Audit: Date=1/2/2015

[v-karpurapud]

Hello @m00ch 

Could you please confirm if your query have been resolved the solution provided by @dufoq3 ?If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[Fowmy]

@m00ch 

You may try AddColumn > Extract > Text Between Delimiters:

= Table.AddColumn(Source, "Text Between Delimiters", each Text.BetweenDelimiters([Column1], "T#", " "), type text)

[m00ch]

I am sorry, I did not describe that well. The screenshot is the data that is all in one row with Carriage Returns (CR). So using a space as the second delimiter does not seem to work well and if I use clean it removes CR but puts the data of the next line right up against the alphanumeric string that I am trying to get. Which essentially eliminates the second delimiter.  

[dufoq3]

Hi @m00ch, 

if this code doesn't work with your data. Replace "#(lf)" with "#(cr)"

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc/LCsIwEIXhVxnqVmxmkkllwEVB3Aki7touhEYpaOslRR/fqReocCCb/4NJUSSU+pQMEVgxLOhhsxbI+7qJArsJ2Txjj3PHfs7ss7JNqumAeEAOUDgTdmO06k6n7tG0R+gvcOhu+tT7GGZfaAfI4IWMII7hNlz7cI+h/hOYkvsQK86JycZkqdlCCw2Q37nBVEfGGL0Nh0H+6/U/SLlF0jld2d67c4AYnjGpqhc=", 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([Column1], "T#", "#(lf)"))
in
    Ad_Extracted

[m00ch]

HI @dufoq3  and @Fowmy  Unfortunately that does not work either, the CF and LB's are in the same cell. Hopefully this screenshot will explain it more clearly.

[dufoq3]

Hi @m00ch, so it works. You wanted to extract "extract an alphanumeric string that varies in length but always comes after the first "T#""

[m00ch]

@dufoq3 I do appreciate your help and it seems that I wasn't clear enough. I should have said or displayed better that the CR and LF was in the same cell and that there was more data after the CR and LF. That is my fault. My initial screenshot was from one row in Power Query but I can see how it looks like separate rows. 

I'll do better next time.

[v-karpurapud]

Hi @m00ch 
Hope you are doing well!

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.

[m00ch]

Unfortunately no, it did not resolve my issue as I did not clearly state my issue in the first post.