vairable value for a calculated column based specific words found in text data

[steambucky]

I have some of the ideas, but not all to create the calculated column I need.

I want to write a calculated column where the value varies depending what specific words it finds in the text.

There are the words we are looking for:

Big = 1

Black = 1

Cats = 1

Data and the value I would be expecting

I like big black cats = 3

I like big black dogs = 2

I like big ginger cats = 2

I like Black animals = 1

I like dogs  =  0

I like cats =  1

I THINK that I need to write some scripting that, if written in just plain English would be like this:

Big_black_cats = IF(

        (If Data contains  = big then Big_black_cats = Big_black_cats +1)

        (If Data contains  = black then Big_black_cats = Big_black_cats +1)

        (If Data contains  = cat then Big_black_cats = Big_black_cats +1)

                                )             

I know that’s NOT right,  but can anyone get me started?

[affan · Accepted solution]

@steambucky

You can use the following to create a calculated column

CalcCats = var _black=IF (
ISBLANK ( SEARCH ( "Black", Table1[Data], 1, BLANK () ) ),
0,1)
var _big=IF (
ISBLANK ( SEARCH ( "big", Table1[Data], 1, BLANK () ) ),
0,1)
var _cats=IF (
ISBLANK ( SEARCH ( "cats", Table1[Data], 1, BLANK () ) ),
0,1)
return _black+_big+_cats

Attached is the file

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

[steambucky]

Thanks so much for your help. 

[Zubair_Muhammad]

@steambucky

You can use this calculated column

See attached file as well

Calculated Column =
SUMX (
    ADDCOLUMNS (
        Words,
        "Number of Matches", IF ( SEARCH ( [Words], [Sentence], 1, 0 ) > 0, 1 )
    ),
    [Number of Matches]
)