Extract text from string without delimeter

[deaconb]

I'm trying to extract a document number from a string of text in power query. The document number always starts with "S" and contains 6 total characters including the "S", ex. S00001. The problem I'm facing is that there are no consistent delimiters which I can use to break down the text string. 

I also don't want to get the numbers from the string, keep the first 5, then add the "S" prefix back on because I don't trust that as a future-proof solution. 

Here is an example of the varying data: 

Any help is greatly appreciated!!

[v-juanli-msft]

Hi @deaconb 

Here is a useful article for reference:

Text Extraction using the M Language with Power BI

It may be easier to do with DAX , could you accept?

Best Regards

Maggie

[deaconb]

Thanks fr that link, Maggie. Unfortunately, that doesn't exactly get me the extraction methodology I'm looking for. I guess I basically am looking to define a data pattern, S##### and have it extracted from the string. 

[Zubair_Muhammad · Accepted solution]

@deaconb 

Try this custom column.

See attached file as well. it works with your sample data

=let mylist=List.Select(
    List.Transform(
    List.Transform({0..99999},each Text.From(_)),
    each "S" & (if Text.Length(_)<5 then Text.Repeat("0",5-Text.Length(_))&_ else _))
    ,
    each Text.Length(_)=6),
myname=[Name]
 in
 Text.Combine(
List.Select(mylist,each Text.Contains(myname,_) ),",")

[deaconb]

That script worked perfectly - thank you so much! I can't tell you how much manual data cleansing you just prevented from having to do weekly!!