1

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

Example

2 Answers 2

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

digits

2
  • Can you advise how I could work that into a formula where I could isolate that # of digits, IF=3, then ="3 Zip"?
    – Jason
    Jun 9 at 21:47
  • use the second formula without an IF, but with: & " Zip" at its end, something like: =index(ifna(len(REGEXEXTRACT(B2:B, "\d+")*(B2:B<>""))&" Zip"))
    – Daniele
    Jun 9 at 21:55
0

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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