Forum Discussion

Applicable88's avatar
Applicable88
Icon for Impactful Individual rankImpactful Individual
4 years ago
Solved

Find string in text and display it in calculated column

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 st...
Show and Tell
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

     

Applicable88's avatar
Applicable88
Icon for Impactful Individual rankImpactful Individual
4 years ago

Ashish_Mathur davehus ,

 

here is a sample table:

 

OriginalTextColumn CalclatedColumn
***Productionkey***Additional Information for Assingement*** Material001Material001
***Productionkey***Material002*** Additional Information for AssingementMaterial002
Additional Information for AssingementNo Material found

 

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

 

Hope that clarifies.

 

Best regards. 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

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.