PowerFx formula similar to contains

(0) Share

Report

Posted on by wendy123

I would like to write a formula that is similar to CONTAINS that can search strings of text and if the strings of text contains this specified word, it will return TRUE, e.g. if the strings of text is 'the lazy fox jumps over the quick brown dog.' and my specified word is 'FOX', it should return true.

I thought IN might work but seems like the entire strings of text has to be the specified word i.e. 'fox' in this case, else it will fail to work. Is there any formula I can use in PowerFx to do this kind of substring search regardless of position, case? Current PowerFx in Copilot Studio doesn't have accept using MATCH/MATCHALL.

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Artur Stepniak 1,539 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hello,

can you convert the searched string first with Lower() or Upper() function and then perform the search? It should always contain lowercase/uppercase letters then. You can use it on both the searched string and search word.

In case of any other questions, let me know. If the answer helped you, mark it, so that others can benefit from it.

Best regards,

Artur Stepniak

Interested in GenAI? Visit my site!

Was this reply helpful? Yes No
wendy123 75 on at

Like (0)

Report
Copy link

Link copied!

Hi Artur,

My question is not so much about the converting the casing but what formula could be used to perform the string search so that as long as the specified word appears in the strings of text, it will return true? Currently IN does not achieve this in Copilot Studio as the entire strings of text has to match the specified word for it to return true,

Was this reply helpful? Yes No
Trait007 303 on at

Like (1)

Report
Copy link

Link copied!
In PowerFx, while IN only checks for an exact match and doesn't support case-insensitive substring searches, you can accomplish your goal using a combination of functions that allow you to perform a case-insensitive search for a substring.

Since you're using Copilot Studio (which doesn’t support MATCH or MATCHALL), you can work around this limitation by using the Lower function (for case insensitivity) combined with Search or Len (for checking the presence of a substring).

Here’s an approach to do a case-insensitive substring search:

Formula:

If( Len(Search(Lower(Text), Lower("fox"))) > 0, true, false )

Explanation:

Lower(Text): Converts the entire text to lowercase to ensure the search is case-insensitive.

Lower("fox"): Converts the specified word ("fox" in this case) to lowercase as well.

Search(Lower(Text), Lower("fox")): Searches for the substring "fox" within the text, ignoring case. If the substring is found, Search returns the position of the first character of the match. If not found, it returns -1.

Len(...) > 0: If Search returns a position greater than -1, it means the substring is found, and the length of the result will be greater than 0. This indicates the word is present in the string.

Example:

Text: 'the lazy fox jumps over the quick brown dog.'

Search Word: 'FOX'

This formula will return TRUE because "fox" is found in the string, regardless of case.

If you want a simpler boolean result (i.e., just true or false without using If), you can directly use:

Len(Search(Lower(Text), Lower("fox"))) > 0

This directly returns true or false based on whether the substring is found.

Was this reply helpful? Yes No