cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 extract to a column is the value 5555,55 right after C.

- The amount of numbers before C is always the same. 

- The number 5555,55 can be anything, its the amount deposited or drafted from a bank account

- There is always an alphabetical value after the wanted value of 5555,55€, but the lenght of the variables behind 5555,55 are not constant.

 

I would imagine that is solved by 

1. Splitting the column by taking away first 11 characters.

2. Finding the position of the first alphabetical figure.

3. Extracting the values before the first alphabetical figure.

 

>> How do i find the first alphabetical figure in Power Query of a string? I've been googling this forever without a result..

 

1 ACCEPTED SOLUTION
mcybulski
Solution Specialist
Solution Specialist

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}

View solution in original post

3 REPLIES 3
mcybulski
Solution Specialist
Solution Specialist

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}

View solution in original post

Jimmy801
Community Champion
Community Champion

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

Greg_Deckler
Super User
Super User

@ImkeF  can likely help with the Power Query. One method you might be able to use in DAX assuming you always have 2 decimal points would be something like:

Column = 
    VAR __Start = 12
    VAR __Decimal = FIND(",",[Column1],__Start,LEN([Column1]) - __Start - 1)
RETURN
    MID([Column1],__Start,__Decimal -__Start + 3)

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors