Formula or VBA Help Needed to Collapse Zip Code Ranges

%3CLINGO-SUB%20id%3D%22lingo-sub-2634441%22%20slang%3D%22en-US%22%3EFormula%20or%20VBA%20Help%20Needed%20to%20Collapse%20Zip%20Code%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2634441%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20in%20desperate%20need%20of%20some%20assistance.%26nbsp%3B%20I%20have%20a%20large%20excel%20spreadsheet%20with%20over%20200K%20rows%20of%209%20digit%20zip%20code%20date%20(zip%2B4).%26nbsp%3B%20I%20need%20a%20way%20to%20collapse%20and%20generalize%20the%20zip%20code%20ranges%20as%20far%20as%20possible%20to%20reduce%20the%20number%20of%20rows.%26nbsp%3B%20I%20have%20attached%20a%20sample%20that%20includes%20zip%2B4%20data%20and%20the%20desired%20output.%26nbsp%3B%20of%20what%20I%20need%20the%20output%20to%20look%20like.%26nbsp%3B%20Any%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSTRONG%3EDevice%20and%20OS%20platform%3C%2FSTRONG%3E%2C%20-%20PC%2FWindows%2010%20Enterprise%2FVersion%201909%2FOS%20Build%2018363.1679%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EExcel%20product%20name%20and%20version%20number%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B-%20Version%202002%20(Build%2012527.20880)%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2634441%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2637333%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20or%20VBA%20Help%20Needed%20to%20Collapse%20Zip%20Code%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2637333%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1125466%22%20target%3D%22_blank%22%3E%40Diva1Web%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20good%20for%20you%20to%20familiarize%20yourself%20with%20the%20functions%20that%20manipulate%20text.%20For%20example%2C%20the%20function%20LEFT.%20I%20was%20able%20to%20use%26nbsp%3B%3CSTRONG%3E%3DLEFT(A2%2C7)%3C%2FSTRONG%3E%20to%20go%20all%20the%20way%20through%20%26nbsp%3Bthe%20yellow%20and%20the%20green.%3C%2FP%3E%3CP%3EThat%20LEFT%20function%20strips%20off%20the%20final%20two%20characters%20of%20the%20original%209%20character%20string.%20Then%20a%20concatenation%20formula%2C%20%3CSTRONG%3E%3DB2%26amp%3B%220%22%3C%2FSTRONG%3E%26nbsp%3B%20tacks%20on%20%220%22%20to%20result%20in%20the%208%20character%20string%20you%20want%20for%20the%20yellow%20and%20green.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20change%20the%20selection%20criteria%2C%20however%2C%20for%20the%20next%20several%20categories.....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20desired%20output%2C%20too%2C%20is%20not%20consistent.%20Some%20have%208%20digits%2C%20other%209%2C%20and%20then%205!%20So%20it%20would%20be%20important%20to%20articulate%20how%20one%20knows%20to%20change%20from%208%20to%209%20to%205%20in%20your%20re-categorization.%20You%20know%20what%20you're%20doing%2C%20but%20haven't%20articulated%20the%20rule.%20%26nbsp%3BHere's%20a%20website%20that%20might%20help%20you%20understand%20text%20manipulation.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fsearch%3Fquery%3Dtext%2Bmanipulation%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fsearch%3Fquery%3Dtext%2Bmanipulation%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

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