Doctor Garrabrant -

What is your ultimate goal? Are you simply trying to filter a text string by a keyword, or do something more interesting like get the count of a specific word from a string?

Based on your original expression, I'm assuming you only need to know if the word in question exists vs. getting the count of a specific word in a string.

That being said, try this:

FIND( [The Text], [My Keyword], 1) > 0

It returns TRUE or FALSE based on whether the keyword specified in the [My Keyword] parameter is found.

Sample attached.

Good luck!

Hi, I have the same type of question. I need to find out how many time a specific keyword can be found within a text.

any clue?

thanks

Antonio

I don't think you will be able to get a count unless your data is a live connection to a database that supports that feature. If so, you might be able to use a Pass Through function. Even then, unless your database has a built-in function for doing this, you're probably out of luck.

If you only need to test for small counts of occurrences (say countable on your fingers), you can do one of these pseudo-recursive calculated fields (but it's ugly):

if find([Item],'a') > 0

then

    if find([Item],'a',find([Item],'a')+len('a')) > 0

    then

        if find([Item],'a',find([Item],'a',find([Item],'a')+len('a'))+len('a')) > 0

        then

            if find([Item],'a',find([Item],'a',find([Item],'a',find([Item],'a')+len('a'))+len('a'))+len('a')) > 0

            then "4 or more"

            else "3"

            end

        else "2"

        end

    else "1"

    end

else "None"

end

I see thanks.

Unfortunately the keywords may be hundreds of time in the entire text column I am looking into.

For now I took this task offline. doing a basic copy paste into MS Word then doing a search for the keywords.

Antonio Willybiro wrote:

 

Hi, I have the same type of question. I need to find out how many time a specific keyword can be found within a text.

 

any clue?

 

thanks

Antonio

Created a calculated field:

SUM(IF CONTAINS(Text,"Smith") THEN 1 END)

Replace Text with your field name and "Smith" with the text you're trying to find.

I was under the impression that you were trying to count the occurrences of substring with a string, but now it sounds like maybe you want to know how many rows in which the substring occurs.  If that's the case Tom W's calculated field should do just fine.

If you want to take a more automated approach than searching with MS Word for your offline approach, I would recommend using some Linux flavored command line tools like grep and wc.  If you are using Windows, I recommend (GnuWin32). Cygwin in another possibility.

Yes Tom's response is in the right direction but limits me to 1 occurrence per row.

I was indeed looking to have substring with a string so if on a row we have the keyword twice then the count should be 2 and not 1.

Thanks for the tips.

Best

Antonio

Sorry, my mistake.

If you want the total times a word / phrase appears within a string, use a calculated field like follows (this is Psuedo code, not real Tableau code);

(LEN(stringtosearch) - LEN(Replace(stringtosearch,"WordToFind",""))) / LEN("WordToFind")

This will take the length of the string and subtract the length of the same string with the word you wish to find removed. You divide the result by the length of the word you wish to find and you have the count of the words.

You'll need to wrap this within a SUM function to get a total across multiple rows.

Great!

That's beautiful and works like a charm.

The formula I used based on the above attached sample workbook is:

sum((LEN([The Text]) - LEN(Replace([The Text],[My Keyword],""))) / LEN([My Keyword]))sum((LEN([The Text]) - LEN(Replace([The Text],[My Keyword],""))) / LEN([My Keyword]))

Thanks guys

Antonio

Russell :

This very helpful but I want to make it more complicated. So I have an excel file with a column with text. I want to be able to search that column based on certain parameters  and then label it into a specific  "bucket" label.  Below an example of the terminology I am looking to filter but don't know how the terminology will work on a If and else statement.

Here is how I want to filter out to specific Label  by word searching an excel column:

("eng" or "eng1" or "eng2" or "eng3" or "eng4" or "engine" or "engines" ) and ("shut down" or "shutdown" or "IFSD") and ("atb" or NEAR(("emerg*", "declar*"), 2, FALSE) or "diver*" or "turn back" or "turnback" or "turned back" or "mayday" or "may day" or "21.3") and not (NEAR(("no", "shut*"), 2, TRUE) or NEAR(("not", "shut*"), 2, TRUE) or NEAR(("apu", "shut*"), 2, FALSE) or NEAR(("no", "emerg*"), 2, TRUE))

I Thank you ahead of time.

Cesar A.

So I got Half way there but I still need help below what I got so far:

IF (CONTAINS([Event Description],'eng') OR CONTAINS([Event Description],'eng1')OR CONTAINS([Event Description],'eng2')OR CONTAINS([Event Description],'eng3')OR CONTAINS([Event Description],'eng4')OR CONTAINS([Event Description],'engine'))

AND  CONTAINS([Event Description],'shutdown' )

THEN 'Engine Shutdown'

ELSE 'TRY AGAIN'

END

My problem is that I need to add more 'keywords' to the AND part of the formula as  OR type. For Example : AND CONTAINS ([Event Description], 'Shutdown' or 'Shut down' or 'Emer' or......)

HELP