Forum Discussion
Impactful IndividualFind 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 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.
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.
4 Replies
davehusMemorable Member
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.
- Ashish_Mathur
Super User
Hi,
Your expected result is not clear. Show the actual data and expected result very clearly.
Applicable88here 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
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.