Find string in text and display it in calculated column

[Applicable88]

Hello,

i have a column with long text. The string within that text I'm looking for has either 001-005 at the end, like that:

Material001, Material 002, Material 003, Material.....

That string is always at another position, in the text. But if its there my calculated column should display that text 

Calculated Column

Material001

Material002

"N/A" 

etc. 

The function Search and Find has not the features for that purpose.

Thank you very much in advance.

Best. 

[Applicable88]

@Ashish_Mathur @davehus ,

here is a sample table:

OriginalTextColumn		CalclatedColumn
***Productionkey***Additional Information for Assingement*** Material001	Material001
***Productionkey***Material002*** Additional Information for Assingement	Material002
Additional Information for Assingement	No Material found

As you can see the word MaterialXXX is always at another position. 

Hope that clarifies.

Best regards. 

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = coalesce(FIRSTNONBLANK(FILTER(VALUES('Search strings'[Strings]),SEARCH('Search strings'[Strings],Data[OriginalTextColumn],1,0)),1),"Not found")

Hope this helps.

[Ashish_Mathur]

Hi,

Your expected result is not clear.  Show the actual data and expected result very clearly.

[davehus]

Hi @Applicable88 ,

Is it not possible for you to this transformation upstream in Power Query?

Did I help you today? Please accept my solution and hit the Kudos button.