1

I try to figure out how to get a value based on "if some word is in a range". So formulas from (Google Sheets formula for "if contains") works:

=IF(ISTEXT(REGEXEXTRACT( A1, "sites")), 1,0)

=IF(regexmatch( A1, "sites"), 1, 0)

but instead of a specific word like here i.e. is "site", I would like to get True, False based on condition if any word from range is in a string. Also, it would be nice if it automatically expands the range in the formula, if I'll add some new word.

Improve this question
4
  • Share your sheet/sample
    – TheMaster
    Jul 6, 2018 at 16:22
  • What's regexextract? If that does what I think it does, does A1 have a regex pattern or is "sites" your pattern?
    – BruceWayne
    Jul 7, 2018 at 18:07
  • @BruceWayne REGEXEXTRACT will extract "needle" from "This is a needle of choice" if used: =REGEXEXTRACT("This is a needle of choice."; ".e{2}dle")
    – user0
    Jul 8, 2018 at 9:50
  • Your question is of very low quality both here and in SO. Make effort to make your question clear,concise and complete. Especially edit your post to include all information from your comments in a clear,concise way.
    – TheMaster
    Jul 8, 2018 at 15:59

3 Answers 3

Reset to default
1

for "true / false" part:

=IF(ISTEXT(REGEXEXTRACT(A1;"sites"));"True";"False")

for "more words crossreferenced with another sheet" part:

=IF(ISTEXT(FILTER({'sheet1'!A1:A3};{'sheet1'!A1:A3}=A1));"True";"False")

Improve this answer
5
  • Thanks, but I would like to get values from an other sheet's range. Any idea how to do that?
    – Lukasz Kawalec
    Jul 6, 2018 at 9:48
  • So I have a list of word. I keep them in "sheet1". I would like to use this formula in "sheet2". so instead of "site" or as you proposed "{"sites","web","page"}" I would like to this function to look for one of the words from the list. If it finds any of the word from list like for example: A1 = "chair", A2 = "wooden chair", A3 = "wooden chair for kids" it's true. So instead of: {"sites","web","page"} something like this: "A1:A3"
    – Lukasz Kawalec
    Jul 6, 2018 at 9:56
  • Not really what I wanted, but this {'sheet1'!A1:A} helped. So now I'll try to continue myself and if I'll stuck I'll ask again ;)
    – Lukasz Kawalec
    Jul 6, 2018 at 20:45
  • So it doesn't work :( I've prepared Google Spreadsheet, so maybe it will be more clear what I try to achive. Here is the link: docs.google.com/spreadsheets/d/…
    – Lukasz Kawalec
    Jul 7, 2018 at 12:59
  • @LukaszKawalec can you explain what exactly you want to accomplish? if you add a new item in "Items" sheet you want to have true/false in C column in "Main sheet" sheet? because if not, then this should be different question
    – user0
    Jul 7, 2018 at 13:33
0

sadly, this is the answer for a question, that wasn't asked in the initial post:

Not really what I wanted, but this {'sheet1'!A1:A} helped. So now I'll try to continue myself and if I'll stuck I'll ask again – Lukasz Kawalec 21 hours ago

...

So it doesn't work :( I've prepared Google Spreadsheet, so maybe it will be more clear what I try to achive. Here is the link: https://docs.google.com/spreadsheets/d/1ibLoxybv6jZi9pMJJOo5R7LCT1JgnJd6JRzEDnslUxs/edit?usp=sharing – Lukasz Kawalec 5 hours ago

SPREADSHEET: https://docs.google.com/spreadsheets/d/

GIF: https://i.imgur.com/FZwW2o0.gif (open in new window)

Improve this answer
5
  • Awesome! Almost there. In "Main Sheet" in "Item Name" I should be able to type i.e. "1kg Oranges" (without an additional column, which you've named "Quantity"), and formula should find if in these case "Oranges" are in the cell. Another example: "a lot of asparagus". In sheet "Items" there is "asparagus", but "a lot of asparagus" contains "asparagus" so it should be named "Vegetable".
    – Lukasz Kawalec
    Jul 8, 2018 at 9:14
  • @LukaszKawalec yeah, unfortunately, thats not possible. non of those two Category columns will detect it in this way, thats why I added Quantity. if you still want to get it detected you need to add in "Items" sheet a full phrase eg. "a lot of asparagus" - this way it will get detected and it will be detected even if you type "a lot aspargus" or "asparg" (in 2nd Category column). all same for kg - you will need to add all variations (1kg oranges, 2kg oranges, 3kg oranges ...) in Items sheet. its a limmitation of REGEXMATCH... it can detect if a phrase is shorter or exact but not longer.
    – user0
    Jul 8, 2018 at 9:45
  • And is it possible to use macro or I would have to do it in Google Script? I have something like that in Excel VBA but have to clue how to translate it into Google Script language.
    – Lukasz Kawalec
    Jul 8, 2018 at 10:00
  • @LukaszKawalec see: i.imgur.com/nLpqEOH.gif ...yes its possible with google script (sadly thats beyond my skills)
    – user0
    Jul 8, 2018 at 10:16
  • 1
    OK, anyway THANK YOU very much for your help!
    – Lukasz Kawalec
    Jul 8, 2018 at 10:44
0

I use this one:

=TEXTJOIN(", ",TRUE,ArrayFormula(IF(ISNUMBER(FIND(I$2:I$18&" ",D2&" ")),I$2:I$18,"")))

I2 through I18 is list of strings. D2 is the value you're testing against.

Really useful for tagging cells based off a list of tags. Gives a comma separated list.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.