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




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?


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. 

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


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




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

Super User
Super User

Re: Extracting specific number form alphanumeric text

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 101 members 1,612 guests
Please welcome our newest community members: