In Google Sheets I have the following formula:
=IF(REGEXMATCH(B1;"offers");"spring";0)
If the cell B1 contains the text "offers" the output will be "spring", otherwise the output will be "0". This works fine but now I want the formular to look at B1 and C1 and if either of them contains "offers" the output should be "spring".
Example Output with formula in column D:
| B | C | D |
|---|---|---|
| test offers test | lorem ipsum | spring |
| lorem ipsum | test offers test | spring |
| lorem ipsum | lorem ipsum | 0 |
I tried the obvious using
=IF(REGEXMATCH(B1:C1;"offers");"spring";0)
but it gives back a #VALUE!
In the second step I want to use this formula in a nested if function like here:
=IF(REGEXMATCH(B1;"offers");"spring";IF(REGEXMATCH(B1;"shop");"summer";0))
CodePudding user response：
The solution seems to be:
=if(and(arrayformula(regexmatch(B1:C1; "(^| )offers( |$)"))); "spring"; "O")
As modified from user6655984's answer in this post. Note I altered the regex to ensure the pattern you are looking to match is preceded by the start of the line or a space, and is proceeded by a space or the end of the line which ensures it does not fall in the middle of a larger string and handles being at the start or end of the main string.
CodePudding user response：
use:
=IF(REGEXMATCH(B1&C1; "offers"); "spring"; 0)
arrayformula:
=INDEX(IF(REGEXMATCH(B1:B&C1:C; "offers"); "spring"; 0))