CODEWITHSUNDEEP
regexgoogle-sheets
user3846962
user3846962

Reputation: 51

How To Use RegEx inside of Google Sheets within IF statements to only find specific zip codes (not all zip codes adhering to format)?

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?

Upvotes: 1

Views: 6805

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18727

=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")

Upvotes: 3

derrickw
derrickw

Reputation: 41

Regexextract function might work - 

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

Upvotes: 1

Related Questions