Forum Discussion
Power Query - extracting project ID in text
Hi,
I have an excel field that contains a project ID, and a description. In perfect conditions it should be: P2019 00002 - Project of House Credit.
I want to extract the ID in Power Query Editor, wich should be easy (a simple extract before limiter "-"), and i would get the ID: P2019 00002.
The problem, is that this field is filled by more than 80 people, many of whom like to freestyle and not fulfill the filling instructions. I get things like:
- Project of House Credit - P20109 00002 - further description (with limiters)
- Project of House Credit P2019 00002 blablabla (with no limiters)
- P2019 002 - House of credit (missing zeros)
Assuming there will always be people who do not follow the rules, how would you approach this? I would not want by now to create an unique column in excel for people to put the ID... (last approach only).
Is there a way to extract the numbers from a text for instance?
Many thanks, friends
Hi campelliann ,
Use this code for a custom column:
Text.Combine(List.Select(Text.Split([Column1], " "), each if Text.Length(Text.Select(_, {"0".."9"})) > 0 then true else false)," ")
2 Replies
Hi campelliann ,
Use this code for a custom column:
Text.Combine(List.Select(Text.Split([Column1], " "), each if Text.Length(Text.Select(_, {"0".."9"})) > 0 then true else false)," ")
