Forum Discussion

tomgag's avatar
tomgag
Icon for Resolver I rankResolver I
8 years ago
Solved

Search amd exclude certain strings

Hi, I have the search function but want to exclude certain strings.

 

Ball= IF (ISBLANK ( SEARCH ( "Messi", Table[Column], 1, BLANK () ) ),
0,
1
)

 

It gives me 1 or 0 depending if Messi is in the string. I want to add exclusion for Ronaldo and Pogba. So if Messi only is in the string it is 1, if "Messi and Ronaldo or Pogba" are it is 0.

 

'Messi is great' the result is 1

'Messi and Ronaldo are great' the result is   0

'Messi and Ronaldo or Pogba are great'the result is 0

 

Need Help
Show and Tell
  • tomgag's avatar
    tomgag
    8 years ago
    Ball = IF (NOT(ISBLANK ( SEARCH ( "Messi", 'Table'[Column], 1, BLANK () ) )) && ISBLANK ( SEARCH ( "Ronaldo", 'Table'[Column], 1, BLANK () ) ) && ISBLANK ( SEARCH ( "Pogba", 'Table'[Column], 1, BLANK () ) ), 1, 0)

15 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    8 years ago
    Ball= 
IF (
ISBLANK ( SEARCH ( "Messi", Table[Column], 1, BLANK () ) )
&&
(
ISBLANK ( SEARCH ( "Ronaldo", Table[Column], 1, BLANK () ) )
||
ISBLANK ( SEARCH ( "Pogba", Table[Column], 1, BLANK () ) )

)
,
0,
1
)
    • tomgag's avatar
      tomgag
      Icon for Resolver I rankResolver I
      8 years ago

      I give 1 when Messi and Ronaldo or Pogba inside the string.

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        8 years ago

        Can you confirm your logic, it is not super clear

         

        give example of your data and expected result.

         

         

  • tomgag's avatar
    tomgag
    Icon for Resolver I rankResolver I
    8 years ago
    Ball = IF (NOT(ISBLANK ( SEARCH ( "Messi", 'Table'[Column], 1, BLANK () ) )) && ISBLANK ( SEARCH ( "Ronaldo", 'Table'[Column], 1, BLANK () ) ) && ISBLANK ( SEARCH ( "Pogba", 'Table'[Column], 1, BLANK () ) ), 1, 0)
    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      8 years ago

      My code will work but if you got it resolved, tha'ts what all matters. There is always many way to do the things. Good for you!!

    • v-piga-msft's avatar
      v-piga-msft
      Icon for Resident Rockstar rankResident Rockstar
      8 years ago

      Hi tomgag,

       

      It's glad that you have solved your problem. Please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

       

      Best Regargds,

      Cherry

      • tomgag's avatar
        tomgag
        Icon for Resolver I rankResolver I
        8 years ago

        None of your solutions worked I am afraid.