cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vippankapoor Frequent Visitor
Frequent Visitor

Extracting specific number form alphanumeric text

 

Hi. Need help in extracting consecutive numeric string from cell. for example extracting "987654321" from string below

 

"P06 Payroll 92 team 32A ACCRUAL 987654321 Sub"

 

Numeric string to be pulled can begin from any number however total digits in string will always be same. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Extracting specific number form alphanumeric text

Not particularly elegant, but works for me:

 

(TextInput as text) =>
let
    Source = TextInput,
    Custom1 = Text.Split(Source, " "),
    Custom2 = List.Select(Custom1, each Text.Length(_) = 9),
    Custom3 = List.Transform(Custom2, each Text.ToList(_)),
    Custom5 = List.Select(Custom3, each List.MatchesAll(_, (li)=> List.Contains({"0".."9"}, li))){0},
    Custom4 = Text.Combine(Custom5)
in
    Custom4

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

7 REPLIES 7
Super User
Super User

Re: Extracting specific number form alphanumeric text

Does it always follow a specific term or number of spaces from the beginning or the end?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

vippankapoor Frequent Visitor
Frequent Visitor

Re: Extracting specific number form alphanumeric text

there is  no specific place in string this might come. Dpending on user input (geogrpahically dispersed users), numeric string can  appear anywhere in the cell. Numeric string to be pulled out will always be 9 characters if that helps. 

Super User
Super User

Re: Extracting specific number form alphanumeric text

There's no easy DAX way that I can think of off the top of my head, perhaps @ImkeF has an M way.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Extracting specific number form alphanumeric text

Not particularly elegant, but works for me:

 

(TextInput as text) =>
let
    Source = TextInput,
    Custom1 = Text.Split(Source, " "),
    Custom2 = List.Select(Custom1, each Text.Length(_) = 9),
    Custom3 = List.Transform(Custom2, each Text.ToList(_)),
    Custom5 = List.Select(Custom3, each List.MatchesAll(_, (li)=> List.Contains({"0".."9"}, li))){0},
    Custom4 = Text.Combine(Custom5)
in
    Custom4

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

vippankapoor Frequent Visitor
Frequent Visitor

Re: Extracting specific number form alphanumeric text

Thanks for the inputs. I am not trained with M query. Can  this be used while adding custom column? 

Will request you to share any quick tutorial that might help me to implement the solution.  

 

vippankapoor Frequent Visitor
Frequent Visitor

Re: Extracting specific number form alphanumeric text

Hello Greg, 

Thanks a lot for your help. 

 

regards

Highlighted
Super User
Super User

Re: Extracting specific number form alphanumeric text

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 58 members 1,176 guests
Please welcome our newest community members: