Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Find alphabetical letters from a string

Hi,   I need to be able to extract MT940 (type :61:) sums from the string. Basically the issue is that i have the following:   1911011101C5555,55NTRF201911015U//SOM 19111503828   What i need to...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    This expression will break up a string into a list based on alpha characters.

     

    Text.SplitAny([nameofstringhere],Text.Combine({"a".."z","A".."Z"}))

     

    Now just reference the second value with the index of {1}

    Text.SplitAny([nameofstringhere],Text.Combine({"a".."z","A".."Z"})){1}

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Hello Anonymous ,

 

check out this solution.

Basically it eliminates the first 11 character and then extracts characters er as long numbers, "." and "," are found

let
    Quelle = "1911011101C5555,55NTRF201911015U//SOM 19111503828",
    startnumber = Text.End
    (
        Quelle,
        Text.Length(Quelle)-11
    ),
    ListText = Text.ToList
    (
        startnumber
    ),
    GoUntilNotNumberOrComma = List.FirstN
    (
        ListText,
        (val) =>
            let 
                isnumber = try if Value.Is(Number.From(val),type number)= true then true else false otherwise false,
                iscomma = if val = "," then true else false ,
                ispoint = if val = "." then true else false,
                partofnumber = if isnumber = true or iscomma = true or ispoint = true then true else false
            in 
                partofnumber
    ),
    ConvertToText = Lines.ToText(GoUntilNotNumberOrComma, "")
in
    ConvertToText

 

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy