Remove text after any number

Hi non23 I suppose there are input withot Z also .... To achive this create a calculated column with the formula given

Text.Select(
    Text.BeforeDelimiter(Text.Combine({[Data], "Z"}), "Z"),
    {"A".."Z", "0".."9"}
)

The function that does most of the work for you in this scenario would be Text.PositionOfAny

Here is some M for handling all your logic in one AddColumn step. I included some logic for handling cases when there is no Z and/or no numbers

let
    Source = PreviousTransforms,

    //Adds column that provides text between first Z (exclusive) and last number (inclusive)
    AddBetweenZandNum = 
    Table.AddColumn(
        Source, 
        "BetweenZandNum", 
        each let 
            posOfLastNum = Text.PositionOfAny( [Data], {"0".."9"}, Occurrence.Last ), 
            posOfZ =  Text.PositionOf( [Data], "Z", Occurrence.First ), 
            start = List.Max( { 0, posOfZ } ), 
            end = if posOfLastNum = -1 then Text.Length( [Data] ) else posOfLastNum
        in 
            Text.Middle( [Data], start, 1 + end - start ), 
        type text
    )
in
    AddBetweenZandNum

 Output

You can use the following code into the advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcnb0sTSwcFOK1YlWikLmWBgYQASdghydvQ2MlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Splitter.SplitTextByCharacterTransition({"0".."9"},{"a".."z","A".."Z"})([Data]){0})
in
    #"Added Custom"