Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Power Query
- Re: Find alphabetical letters from a string

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Find alphabetical letters from a string

12-27-2019
07:55 AM

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..

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-27-2019
11:41 AM

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}

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-27-2019
11:41 AM

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}

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-27-2019
10:29 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-27-2019
08:37 AM

@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)
```

Check out my latest book!