Forum Discussion
Extracting a number string after a two character delimiter
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
Hi m00ch
To better understand the issue and provide an accurate solution, could you share a sample dataset in a .csv or Excel file? This will allow us to analyze the data structure and understand how the CR and LF characters are impacting the results. Please anonymize any sensitive information if necessary before sharing.
Thank you!
13 Replies
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.
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
I have tried the solutions above and the issue is that the solutions above would work great if there was no data after the CR and LF's. Unfortunately there is data after the CR and LF's as you can see in the example below. I have also tried to split columns using CR as the split point and then using textbetweendelimiters and although this works most of the time it seems to be a quite messy solution.
Below in Column1 is the data and Column2 I have what I want to pull out of the data in Column1.
Hi m00ch
To better understand the issue and provide an accurate solution, could you share a sample dataset in a .csv or Excel file? This will allow us to analyze the data structure and understand how the CR and LF characters are impacting the results. Please anonymize any sensitive information if necessary before sharing.
Thank you!Hello m00ch
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
