Forum Discussion
Formula or VBA Help Needed to Collapse Zip Code Ranges
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
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
