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

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

  • Yeah, I realized that regex doesn't like text much, so the ISTEXT fixed that, I think, it no longer produced an error, anyways. It's returning a blank now – Chris McMahon Jan 9 at 15:57
  • 1
    @ChrisMcMahon The main take-away is that regular expressions have certain syntax in which, for example, ? has special meaning. If you are looking for substring search, use search or find, depending on whether case-sensitivity is needed. Wrap those in if(iserror(find(...), ..., ...) because they return an error if not found. – user135384 Jan 9 at 16:02

The previous answers were right. Your regular expression contains vague characters like ?, which would mean different things in regular expression. But,Fortunately , re2 supports literal text.

\Q...\E

Anything written between Q and E is taken literally.

If

A1:

/slideshows/blah-blah.html?utm_source=naytev&utm_campaign=ab&utm_medium=cwpg&utm_term=paid

A2:

?utm_source=naytev&utm_campaign=ab&utm_medium=cwpg&utm_term=paid

A3:

=IF(REGEXMATCH(A1,"\Q"&A2&"\E"),SUBSTITUTE(A1, A2,""))

A3 Value: /slideshows/blah-blah.html

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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