Formula or VBA Help Needed to Collapse Zip Code Ranges

Copper Contributor

I am in desperate need of some assistance.  I have a large excel spreadsheet with over 200K rows of 9 digit zip code date (zip+4).  I need a way to collapse and generalize the zip code ranges as far as possible to reduce the number of rows.  I have attached a sample that includes zip+4 data and the desired output.  of what I need the output to look like.  Any help is greatly appreciated.

 

  • Device and OS platform, - PC/Windows 10 Enterprise/Version 1909/OS Build 18363.1679
  • Excel product name and version number - Version 2002 (Build 12527.20880)
1 Reply

@Diva1Web 

 

It would be good for you to familiarize yourself with the functions that manipulate text. For example, the function LEFT. I was able to use =LEFT(A2,7) to go all the way through  the yellow and the green.

That LEFT function strips off the final two characters of the original 9 character string. Then a concatenation formula, =B2&"0"  tacks on "0" to result in the 8 character string you want for the yellow and green.

 

You change the selection criteria, however, for the next several categories.....

 

Your desired output, too, is not consistent. Some have 8 digits, other 9, and then 5! So it would be important to articulate how one knows to change from 8 to 9 to 5 in your re-categorization. You know what you're doing, but haven't articulated the rule.  Here's a website that might help you understand text manipulation. https://exceljet.net/search?query=text+manipulation