How to search values within a cell?

Question

I'm working with a large amount of data and am trying to enter standardized formulas to repeatedly convert to a more comprehensive version. What I'm having trouble with now is identifying each of these tiers of addresses and assigning a label. I'm stuck on the zip codes now. I want to be able to search each cell in this column for a 3-digit # vs a 5-digit #. I've been online searching all morning and most of the formulas I can find count the # of cells vs counting within an individual cell -- or -- apply to Excel. Any help would be greatly appreciated

Answer 1

if you want to extract the digits from each cell:

=index(ifna(REGEXEXTRACT(B2:B, "\d+")*(B2:B<>"")))

if you want to know the number of digits of each zip:

=index(ifna(len(REGEXEXTRACT(B2:B, "\d+")*(B2:B<>""))))

Answer 2

You can test the data for various patterns using ifs() and regexmatch(), like this:

=arrayformula( 
  ifs( 
    regexmatch(trim(B3:B), "(^|[^\d])\d{5}\s*.\s*\d{4}([^\d]|$)"), 
      "ZIP+4 code", 
    regexmatch(trim(B3:B), "(^|[^\d])\d{5}([^\d]|$)"), 
      "ZIP code", 
    regexmatch(trim(B3:B), "(^|[^\d])\d{3}([^\d]|$)"), 
      "sectional center facility code", 
    true, 
      iferror(1/0) 
  ) 
)

To learn the exact regular expression syntax used by Google Sheets, see RE2.