I have a column of addresses within Google Sheets formatted as:

"1234 Western Ave, Cincinnati 45226"

"5678 Eastern Ave, Columbus 45126"

and I wish to use regex within a second google sheets column to identify only zip codes beginning with 452.

I've tried search for regex within an IF statement:

=if( search( "regex", A1), "success message", "fail message")

and I've tried regex match within an if statement.

=if( REGEXMATCH("A1","regex"), "success message", "fail message")

I've tried numerous regex expressions including the following, they all validate within regex101 and various online testers - just never inside of Google sheets where I need them to. I could use some community assistance.

RegEx that works online but not in Google Sheets:

1.

452(\d{2})

2.

\b452\b(\d{2})

and so on.

How do I find only US zip codes beginning with a specific initial three digits?

2 Answers

=if( REGEXMATCH(A1,"452(\d{2})"), "success message", "fail message")

Notes:

• A1 is not quoted
• I suggest regex "452\d{2}$". It uses $ char to check the end of a string.

---

EDIT:

if you have a number, convert it into text like this: TO_TEXT(A2). The formula should be:

=if( REGEXMATCH(TO_TEXT(A2),"452(\d{2})"), "success message", "fail message")

4

Regexextract function might work -

=REGEXEXTRACT(A1, "452\d{2}")

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.