Find Text (Number) in text string (Beginning with) and return it in Custom Column

[sploits]

Hi,

I have been searching high and low for an answer to this and so far can't find anything.

Within Power Query I have a column that I want it to find text (a number) that begins with the same 4 numbers and then return that as a single entry in a custom column. the Issue I have is that the number of delimiters change every now and again so I can't use the extract function. The source data can't be changed as it comes from client level and is locked in.

So as an example the text in the cell within Power Query looks like this.

ABC-Works-North Project-NP 2 - 5001123456 - Concrete Pour

ABC - Works - South-Project - SP1 - 5001256852 - Light Fitting

ABC - Works-West Project- 5001985500 - Room Painting

The part I want to extract is the numbers that begin with 5001 (so 5001123456, 5001256852 etc). These 4 starting numbers will never change (so hopefully make it easier to extract......).

The issue is that the delimiter (-) changes as it is sometimes in a title (South-Project for example) and sometimes it isn't the same place (sometimes 5 delimiters across, sometimes 4, sometimes 6, sometimes spaces and sometimes not (if that is relevant)).

I currently do this in excel using a MID and FIND function and it works well so was hoping Power Query could do something similar. Not the end of the world if not as I can dump the data into an Excel table and have that do the formula before uploading to SharePoint but would have preferred to get the source data file, add that to SharePoint and then have Power Query do the work which then would link nicely into my Power Bi report (the 5001 numbers are our unique reference to other reports so would bring it all together nicely).

Many thanks

[sploits]

@wdx223_Daniel That worked perfectly!!! Thank you so much!

[wdx223_Daniel · Accepted solution]

=List.Select(Text.SplitAny([YourColumnName],Text.Remove([YourColumnName],{"0".."9"})),each Text.StartsWith(_,"5001") and Text.Length(_)=10){0}?

[khisla]

I have a similar request however I need to find the ILXXX within the text sting

Works North Project MP2 IL207 Concrete Pour

Works South Project SP1 IL315 Light Fitting

Works West Project IL216 Room Painting

The result would be another column with the IL207, IL315 and IL216

[OmarAl]

Hi Daniel, thanks for sharing this ! Could you please help me with the formula you created? Is it simlpy a new step inserted in the column or simply a custom column? 

[dufoq3]

Hi @OmarAl,

add that code as custom column and replace both [YourColumnName]

[OmarAl]

Many thanks for the reply 🙂 

OxO compan Inc. - PO 4500806733/280

or

Xyz Limtd. - PO 4500806733_10

My column has such text in the above. I tried manipulating the characted in your formula from {10} to {13}, but unfortuantely it's not reading the numbers after the "/" or "_". Any recommendation?

[dufoq3]

What should be the output of 2 examples you've provided?

[OmarAl]

Ideally, to read the "/280" or "_10" after the 10 digits. "45xxxxxxxx"

Or to read eaverything until it finds space " ". That's where I know the Purchase Order is complete 🙂

[dufoq3]

What about this?

Text.Range([YourColumnName], 13)

[OmarAl]

Hi I found another workaround but would be great if you can giveme the whole forumla 🙂 Where should I plug the

Text.Range([YourColumnName], 13) 

[dufoq3]

Custom Column