Look for a list of keywords in a column and have ONE word as a replacement

Comments

3 comments

  • Alan

    Hi Reeta,

    You should be able to do this with a REGEX.

    REGEX(#Sheet!Column;".*(park|playground|garden).*";"Parks";"No Match")

    You can nest this by replacing "No Match" with a nested REGEX.

    REGEX(#Sheet!Column;".*(park|playground|garden)";"Parks";REGEX(#Sheet!Column;".*(apple|orange|grape);"Fruits";"No Match"))

    Another way to approach this is with the IF and CONTAINS functions.

    IF(CONTAINS(#Sheet!Column;"park");"Parks";IF(CONTAINS(#Sheet!Column;"garden");"Parks";"Not Parks"))

    They both have their upsides and downsides as far as implementation is concerned, REGEX requires you to learn regex's.  For that I would suggest http://regex101.com.  In the long run I feel like this approach is easier since you can knock out more than one string with a single regex, rather than having to build an IF/CONTAINS structure for every single word.

    Alan

    0
    Comment actions Permalink
  • Reeta

    Hi Alan,

    Thank you for the solution.

    I am using the Regex expression as suggested by you.

    But there seems to be an issue with that-

    REGEX(#Sheet!Column;".*(park|playground|garden).*";"Parks";"No Match")----expression will show results for string like Parkal ,Parkinson as well....I want the regex to show results only for whole words

    0
    Comment actions Permalink
  • Alan

    Hi Reeta,

    Please try using (\bpark\b|\bplayground\b|\bgarden\b) instead of .*(park|playground|garden).*

    if you enclose a word in \b, you specify 'match this exact word.'

    Note, in the datameer UI if you type directly into the formula bar you will need to escape \ as \\, or you'll get an error.

    So while the valid regex for standard parsers is shown above, you'll need to apply double backslashes wherever I placed a single backslash.

    Also note this is case sensitive so if you want to match 'park', 'Park', and 'PARK' you need to have three entries.

    You can find guided lessons on how to learn regex's here: https://regexone.com/

    Alan

    0
    Comment actions Permalink

Please sign in to leave a comment.