Google-sheets – Search a text for specific text, then multiple if statements depending on what’s there

google sheetsregexworksheet-function

I've got a weird question about Google Sheets.

First, here's what I've got so far, but it returns an error "Function REGEXEXTRACT parameter 2 value "My Text here" is not a valid regular expression."

=IF(REGEXEXTRACT(W8, $Y$1), SUBSTITUTE(W8,$Y$1, ""), 
 IF(REGEXEXTRACT(W8, $Y$2), SUBSTITUTE(W8,$Y$2, ""), 
    IF(REGEXEXTRACT(W8, $Y$3), SUBSTITUTE(W8,$Y$3, ""),
       IF(REGEXEXTRACT(W8, $Y$4), SUBSTITUTE(W8,$Y$4, ""), ""
                    )
     )
   )
)

Basically, what I want it to do, is check if a cell contains certain text, with different results based on what text it finds. I have 4 possibilities of what it could find, let's say it Looks for Abc, def, ghi, jkl in a line of text. When it sees abc, it looks at W8, and substitutes the matching text in W8 with $Y$1. If it sees def, it looks at W8, and substitutes the matching text in W8 with $Y$2. Etc. Etc. Etc. And then Ideally it shows as blank if it doesn't find any of those.

I feel like i'm really close to getting it right, but I just can't see it.

Edit

I've added the istext function to it, and it removes the error message, but now it's just returning a blank

=IF(ISTEXT(regexextract(W8, $Y$1)), SUBSTITUTE(W8,$Y$1, ""), 
 IF(ISTEXT(regexextract(W8, $Y$2)), SUBSTITUTE(W8,$Y$2, ""), 
    IF(ISTEXT(regexextract(W8, $Y$3)), SUBSTITUTE(W8,$Y$3, ""),
       IF(ISTEXT(regexextract(W8, $Y$4)), SUBSTITUTE(W8,$Y$4, ""), ""
                    )
     )
   )

Edit 2

For clarity's sake, I'm including what each of the cells I'm editing are:

W8: Value: =SUBSTITUTE(C8,V8, "")
       Text: /slideshows/blah-blah.html?utm_source=naytev&utm_campaign=ab&utm_medium=cwpg&utm_term=paid

$Y$2: ?utm_source=naytev&utm_campaign=ab&utm_medium=cwpg&utm_term=paid

EDIT 3

I've condensed my problem down to one line, to further simplify the problem: =IF(ISTEXT(REGEXMATCH(W8, $Y$2)), SUBSTITUTE(W8,$Y$2, ""), "00") Returns 00

Best Answer

Short Answer

W8: ... text /slideshows/blah-blah.html?utm_source=naytev&utm_campaign=ab&utm_medium=cwpg&utm_term=paid
$Y$2: ?utm_source=naytev&utm_campaign=ab&utm_medium=cwpg&utm_term=paid

The formula doesn't work because, among other possible causes, the values of W8 and $Y$2 are't valid regular expressions. Both looks like parts of an URL and both include characters that in a regular expression have special meanings.

As was mentioned in a comment to this answer instead of regular expression functions you could use FIND or SEARCH as this could be the fasted (and easier?) solution to apply.

Explanation

The initial error message implies that the value at least one of $Y$1, $Y$2, $Y$3, $Y$4 are not valid regular expressions.

From REGEXEXTRACT

Syntax

REGEXEXTRACT(text, regular_expression)

text - The input text.

regular_expression - The first part of text that matches this expression will be returned.

Notes

  • Google products use RE2 for regular expressions. Learn more on how to use RE2 expressions.
  • This function only works with text (not numbers) as input and returns text as output. If a number is desired as the output, try using the VALUE function in conjunction with this function. If numbers are used as input, convert them to text using the TEXT function.

It's worth to note that when we are working with nested functions and a large formula doesn't work, a good practice is to test each part individually, in other words validate that the value or values of each argument of each function are valid and of the proper type.

H/T to an user that use to change their display name at any time who posted this answer

Related Topic