Look for a list of keywords in a column and have ONE word as a replacement
Hi,
I am looking for a way ....in which I can search for a list of keywords in a column and have just one replacement word if any of the keywords exists in that column.
Ex: Input String : He went to the park.
Here I want to look for the keywords : park,garden,playground in the input string and have just one word to be given as an output in the result - PARKS
I have a list of many keywords - with diff categories.
Ex:-
Parks,Playgrounds,Gardens------>should give me the output Parks
Apple,orange,grapes----> should give me only Fruits
cabbage,beans--->vegetables.
Is there a way which this can be achieved???
Thanks,
Reeta
-
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
-
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
-
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
Please sign in to leave a comment.
Comments
3 comments