SOLVED

Extract several values from a column

%3CLINGO-SUB%20id%3D%22lingo-sub-1210652%22%20slang%3D%22en-US%22%3EExtract%20several%20values%20from%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1210652%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20Does%20anyone%20have%20any%20suggestions%20for%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChris%3C%2FP%3E%3CP%3E---------------------------------------%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%20I%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20already%20posted%20this%20on%20the%20SQL%20community%20forum%2C%20but%20I%20am%20now%20thinking%20this%20may%20also%20be%20good%20place%20to%20post%20this%20question.%20%26nbsp%3B%20I%20use%20PowerQuery%20however%20the%20job%20I%20am%20trying%20to%20do%20below%20just%20causes%20the%20application%20to%20crash%2C%20it%20seems%20to%20be%20memory%20related%26nbsp%3B%20So%20I%20am%20hoping%20doing%20this%20in%20MS%20Access%20will%20be%20much%20easier%20and%20less%20strain%20on%20excel.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20my%20rawdata%20comes%20from%20an%20SQL%20View%2C%20I%20want%20to%20strip%20out%20all%20the%20corresponding%20codes%20in%20the%20column%20called%20'%3CSTRONG%3EIN_CODE%3C%2FSTRONG%3E'%2C%20and%20create%20a%20new%20record%20for%20each%20corresponding%20code%20I%20find.%26nbsp%3B%20Some%20columns%20can%20have%2010%20or%20more%20codes%2C%20so%20the%20routine%20needs%20to%20know%20when%20there%20are%20no%20more%20values%20to%20check%20or%20extract.%26nbsp%3B%20I%20want%20to%20strip%20out%20any%20unnecessary%20text%2C%20for%20example%2C%20any%20%3CSTRONG%3Ecommas%3C%2FSTRONG%3E%2C%20%3CSTRONG%3Eperiods%3C%2FSTRONG%3E%2C%20%3CSTRONG%3Enames%20or%20words%3C%2FSTRONG%3E%20and%20even%20%3CSTRONG%3Ebrackets%20used%20around%20any%20text%3C%2FSTRONG%3E%20etc.%26nbsp%3B%20Each%20code%20is%20only%206%20digits%20long%2C%20I%20thought%20if%20I%20could%20remove%20unwanted%20text%2C%20and%20try%20and%20remove%20every%20space%2C%20I%20could%20extract%20the%20code%20in%206%20digits%20chunks%3B%20this%20roughly%20what%20I%20was%20doing%20in%20power%20query%20but%20the%20process%20or%20task%20was%20too%20memory%20intensive.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%201%3B%20my%20raw%20data%2C%20I%20am%20needing%20to%20extract%20all%20the%206%20digit%20codes%20from%20the%20column%20called%20'%3CSTRONG%3EIN_CODE%3C%2FSTRONG%3E'.%20%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22654%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2257%22%3EStep1%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2257%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2257%22%3ECodes%20are%20all%20%3CSTRONG%3E6%20digits%3C%2FSTRONG%3E%20length%2C%20there%20also%20extra%20spaces%20between%20codes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E%3CSTRONG%3EPROJECT_ID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3E%3CSTRONG%3EPROJECT_TITLE%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3E%3CSTRONG%3EIN_CODE%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1246%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME2%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ER12345%20and%20%26nbsp%3B%26nbsp%3B%20R45647%2C%20RA4564%20%26amp%3B%20RB4561%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1261%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME3%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ER12478%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1276%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME4%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ER12457%3CSTRONG%3E%2C%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1291%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME5%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ERA4789%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1306%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME6%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ETY7894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1321%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME7%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3EFG5674%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1336%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME8%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ER45789%20%3CSTRONG%3E%2C%3C%2FSTRONG%3E%20%26nbsp%3B%20TY7894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2257%22%3E1351%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2257%22%3ENAME9%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2257%22%3ER13445%20R78456%20%26amp%3B%20R96325%20(SCCA)%20R44557%20(Dept)%20R77889%20R11122%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1366%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME10%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ERT7845%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1381%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME11%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ERT1234%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%202%2C%20I%20then%20want%20to%20remove%20all%20unwanted%20text%2C%20e.g.%20'%3CSTRONG%3Eand%3C%2FSTRONG%3E'%20'%3CSTRONG%3E%2C%3C%2FSTRONG%3E'%2C%20'%3CSTRONG%3E%26amp%3B'%3C%2FSTRONG%3E%20%2C%26nbsp%3B%3CSPAN%3E%3CSTRONG%3E%20'(SCCA)'%20%3C%2FSTRONG%3Eetc%26nbsp%3B%20There%20are%20also%20extra%20spaces%20just%20for%20a%20good%20measure.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CTABLE%20width%3D%22654%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2286%22%3EStep2%3C%2FTD%3E%3CTD%20width%3D%22162%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22406%22%3ECodes%20are%20all%206%20digits%20length%2C%20there%20also%20extra%20spaces%20between%20codes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPROJECT_ID%3C%2FTD%3E%3CTD%3EPROJECT_TITLE%3C%2FTD%3E%3CTD%3EIN_CODE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1246%3C%2FTD%3E%3CTD%3ENAME2%3C%2FTD%3E%3CTD%3ER12345%20R45647%20%26nbsp%3B%26nbsp%3B%20RA4564%20RB4561%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1261%3C%2FTD%3E%3CTD%3ENAME3%3C%2FTD%3E%3CTD%3ER12478%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1276%3C%2FTD%3E%3CTD%3ENAME4%3C%2FTD%3E%3CTD%3ER12457%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1291%3C%2FTD%3E%3CTD%3ENAME5%3C%2FTD%3E%3CTD%3ERA4789%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1306%3C%2FTD%3E%3CTD%3ENAME6%3C%2FTD%3E%3CTD%3ETY7894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1321%3C%2FTD%3E%3CTD%3ENAME7%3C%2FTD%3E%3CTD%3EFG5674%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1336%3C%2FTD%3E%3CTD%3ENAME8%3C%2FTD%3E%3CTD%3ER45789%20TY7894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER13445%20R78456%20R96325%20R44557%20R77889%20R11122%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1366%3C%2FTD%3E%3CTD%3ENAME10%3C%2FTD%3E%3CTD%3ERT7845%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1381%3C%2FTD%3E%3CTD%3ENAME11%3C%2FTD%3E%3CTD%3ERT1234%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%203%2C%20so%20I%20want%20to%20get%20the%20data%20from%20step%201%20to%20look%20like%20this%20-%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22414%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2286%22%3EStep3%3C%2FTD%3E%3CTD%20width%3D%22162%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22166%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CSTRONG%3EPROJECT_ID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EPROJECT_TITLE%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EIN_CODE%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1246%3C%2FTD%3E%3CTD%3ENAME2%3C%2FTD%3E%3CTD%3ER12345%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1246%3C%2FTD%3E%3CTD%3ENAME2%3C%2FTD%3E%3CTD%3ER45647%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1246%3C%2FTD%3E%3CTD%3ENAME2%3C%2FTD%3E%3CTD%3ERA4564%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1246%3C%2FTD%3E%3CTD%3ENAME2%3C%2FTD%3E%3CTD%3ERB4561%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1261%3C%2FTD%3E%3CTD%3ENAME3%3C%2FTD%3E%3CTD%3ER12478%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1276%3C%2FTD%3E%3CTD%3ENAME4%3C%2FTD%3E%3CTD%3ER12457%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1291%3C%2FTD%3E%3CTD%3ENAME5%3C%2FTD%3E%3CTD%3ERA4789%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1306%3C%2FTD%3E%3CTD%3ENAME6%3C%2FTD%3E%3CTD%3ETY7894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1321%3C%2FTD%3E%3CTD%3ENAME7%3C%2FTD%3E%3CTD%3EFG5674%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1336%3C%2FTD%3E%3CTD%3ENAME8%3C%2FTD%3E%3CTD%3ER45789%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1336%3C%2FTD%3E%3CTD%3ENAME8%3C%2FTD%3E%3CTD%3ETY7894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER13445%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER78456%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER96325%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER44557%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER77889%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER11122%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1366%3C%2FTD%3E%3CTD%3ENAME10%3C%2FTD%3E%3CTD%3ERT7845%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1381%3C%2FTD%3E%3CTD%3ENAME11%3C%2FTD%3E%3CTD%3ERT1234%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChris%20C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1210652%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1691085%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20several%20values%20from%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1691085%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544914%22%20target%3D%22_blank%22%3E%40ChrisC365%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20solve%20this%20with%20a%20helper%20table%20and%20VBA%20in%20Access.%20The%20helper%20table%20is%20called%20%3CSTRONG%3EUnwantedText%3C%2FSTRONG%3E%20and%20contains%20the%20(pieces%20of)%20text%20you%20want%20to%20remove%20from%20IN_CODE%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22incode1.png%22%20style%3D%22width%3A%20488px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F220127i4BB9DD0AE985759D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22incode1.png%22%20alt%3D%22incode1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EYou%20can%20extend%20this%20table%20with%20any%20text%20you%20want%2Fneed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20we%20define%20a%20table%20(%3CSTRONG%3EProjectParsed%3C%2FSTRONG%3E)%20for%20the%20parsed%20result%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22incode2.png%22%20style%3D%22width%3A%20682px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F220128iF46FDA3B5FC81078%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22incode2.png%22%20alt%3D%22incode2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ENote%20that%20the%20three%20fields%20together%20form%20the%20primary%20key.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20that%20the%20raw%20project%20data%20is%20in%20a%20(linked)%20table%20which%20is%20called%20%3CSTRONG%3EProjectRaw%3C%2FSTRONG%3E%20(you%20can%20name%20it%20anything%20you%20like%2C%20but%20you%20have%20to%20make%20a%20slight%20modification%20in%20the%20coming%20VBA%20code%20if%20you%20name%20it%20different)%2C%20this%20VBA%20subroutine%20removes%20the%20unwanted%20text%20and%20parses%20the%206%20digit%20occurrences%20in%20IN_CODE%20into%20separate%20rows%2Frecords%20in%20the%20table%26nbsp%3B%3CSTRONG%3EProjectParsed%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20RemoveAndParse()%0A%20%20'Declare%0A%20%20Dim%20sInCode%20As%20String%2C%20sSQL%20As%20String%0A%20%20Dim%20i%20As%20Integer%0A%20%20%20%20%0A%20%20'Switch%20off%20warnings%0A%20%20DoCmd.SetWarnings%20False%0A%20%20%20%20%0A%20%20'Remove%20all%20earlier%20records%20from%20the%20result%20table%0A%20%20sSQL%20%3D%20%22DELETE%20FROM%20ProjectParsed%22%0A%20%20DoCmd.RunSQL%20sSQL%0A%20%20%20%20%20%20%0A%20%20'Open%20tables%20Project%20and%20UnwantedText%20as%20recordsets%20to%20loop%20over%0A%20%20Dim%20rsProject%20As%20DAO.Recordset%0A%20%20Set%20rsProject%20%3D%20CurrentDb.OpenRecordset(%22ProjectRaw%22%2C%20dbOpenTable)%0A%0A%20%20Dim%20rsUnwantedText%20As%20DAO.Recordset%0A%20%20Set%20rsUnwantedText%20%3D%20CurrentDb.OpenRecordset(%22UnwantedText%22%2C%20dbOpenTable)%0A%0A%20%20'Loop%20over%20the%20(raw)%20projects%0A%20%20Do%20Until%20rsProject.EOF%0A%20%20%20%20sInCode%20%3D%20rsProject!IN_CODE%0A%20%20%20%20%0A%20%20%20%20'Remove%20all%20the%20spaces%20from%20IN_CODE%0A%20%20%20%20sInCode%20%3D%20Replace(sInCode%2C%20%22%20%22%2C%20%22%22)%0A%20%20%20%20%0A%20%20%20%20'Remove%20the%20unwanted%20text%20from%20IN_CODE%0A%20%20%20%20rsUnwantedText.MoveFirst%20'go%20back%20to%20first%20unwanted%20text%20for%20every%20cycle%0A%20%20%20%20'Loop%20over%20the%20unwanted%20text%0A%20%20%20%20Do%20Until%20rsUnwantedText.EOF%0A%20%20%20%20%20%20sInCode%20%3D%20Replace(sInCode%2C%20rsUnwantedText!Unwanted%2C%20%22%22)%20'remove%20text%20(replace%20it%20by%20a%20blank)%0A%20%20%20%20%20%20rsUnwantedText.MoveNext%20'move%20to%20next%20unwanted%20text%0A%20%20%20%20Loop%0A%20%20%20%20%0A%20%20%20%20'Parse%20the%206%20digit%20codes%0A%20%20%20%20For%20i%20%3D%201%20To%20Len(sInCode)%20Step%206%0A%20%20%20%20%20%20sSQL%20%3D%20%22INSERT%20INTO%20ProjectParsed%20VALUES('%22%20%26amp%3B%20rsProject!PROJECT_ID%20%26amp%3B%20%22'%2C%20'%22%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20rsProject!PROJECT_TITLE%20%26amp%3B%20%22'%2C%20'%22%20%26amp%3B%20Mid(sInCode%2C%20i%2C%206)%20%26amp%3B%20%22')%22%0A%20%20%20%20%20%20DoCmd.RunSQL%20sSQL%0A%20%20%20%20Next%0A%20%20%20%20%0A%20%20%20%20rsProject.MoveNext%20'move%20to%20next%20project%0A%20%20Loop%0A%20%20%0A%20%20rsUnwantedText.Close%0A%20%20rsProject.Close%0A%20%20%0A%20%20'Switch%20on%20warnings%20again%0A%20%20DoCmd.SetWarnings%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20assume%20and%20hope%20that%20the%20comments%20in%20the%20code%20explain%20what%20the%20subroutine%20does.%26nbsp%3BYou%20can%20run%20the%20routine%20from%20the%20VBA%20editor%20or%20you%20can%20call%20it%20from%20a%20button%20in%20one%20of%20your%20Access%20forms.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20running%20it%20you'll%20get%20something%20in%20the%20table%26nbsp%3B%3CSTRONG%3EProjectParsed%3C%2FSTRONG%3E%26nbsp%3Blike%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22incode3.png%22%20style%3D%22width%3A%20723px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F220130i0F7A74F714ED5C0B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22incode3.png%22%20alt%3D%22incode3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20does%20the%20job.%20And%20if%20you%20need%20more%20clarification%2C%20please%20let%20me%20know.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20wishes%2C%3C%2FP%3E%3CP%3ETieme%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1710464%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20several%20values%20from%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1710464%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F131546%22%20target%3D%22_blank%22%3E%40Tieme%20Woldman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20looks%20really%20good%2C%20I%20will%20give%20it%20a%20whirl.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, Does anyone have any suggestions for this?

 

Chris

---------------------------------------

 

Hi  I

 

I have already posted this on the SQL community forum, but I am now thinking this may also be good place to post this question.   I use PowerQuery however the job I am trying to do below just causes the application to crash, it seems to be memory related  So I am hoping doing this in MS Access will be much easier and less strain on excel.  

 

So my rawdata comes from an SQL View, I want to strip out all the corresponding codes in the column called 'IN_CODE', and create a new record for each corresponding code I find.  Some columns can have 10 or more codes, so the routine needs to know when there are no more values to check or extract.  I want to strip out any unnecessary text, for example, any commas, periods, names or words and even brackets used around any text etc.  Each code is only 6 digits long, I thought if I could remove unwanted text, and try and remove every space, I could extract the code in 6 digits chunks; this roughly what I was doing in power query but the process or task was too memory intensive.

 

Step 1; my raw data, I am needing to extract all the 6 digit codes from the column called 'IN_CODE'.  

Step1 Codes are all 6 digits length, there also extra spaces between codes
PROJECT_IDPROJECT_TITLEIN_CODE
1246NAME2R12345 and    R45647, RA4564 & RB4561
1261NAME3R12478
1276NAME4R12457,
1291NAME5RA4789
1306NAME6TY7894
1321NAME7FG5674
1336NAME8R45789 ,   TY7894
1351NAME9R13445 R78456 & R96325 (SCCA) R44557 (Dept) R77889 R11122
1366NAME10RT7845
1381NAME11RT1234

 

Step 2, I then want to remove all unwanted text, e.g. 'and' ',', '&' '(SCCA)' etc  There are also extra spaces just for a good measure. 

Step2 Codes are all 6 digits length, there also extra spaces between codes
PROJECT_IDPROJECT_TITLEIN_CODE
1246NAME2R12345 R45647    RA4564 RB4561
1261NAME3R12478
1276NAME4R12457
1291NAME5RA4789
1306NAME6TY7894
1321NAME7FG5674
1336NAME8R45789 TY7894
1351NAME9R13445 R78456 R96325 R44557 R77889 R11122
1366NAME10RT7845
1381NAME11RT1234

 

Step 3, so I want to get the data from step 1 to look like this - 

Step3  
PROJECT_IDPROJECT_TITLEIN_CODE
1246NAME2R12345
1246NAME2R45647
1246NAME2RA4564
1246NAME2RB4561
1261NAME3R12478
1276NAME4R12457
1291NAME5RA4789
1306NAME6TY7894
1321NAME7FG5674
1336NAME8R45789
1336NAME8TY7894
1351NAME9R13445
1351NAME9R78456
1351NAME9R96325
1351NAME9R44557
1351NAME9R77889
1351NAME9R11122
1366NAME10RT7845
1381NAME11RT1234

 

Any help appreciated.

 

Chris C

2 Replies
best response confirmed by ChrisC365 (Occasional Contributor)
Solution

@ChrisC365 

You could solve this with a helper table and VBA in Access. The helper table is called UnwantedText and contains the (pieces of) text you want to remove from IN_CODE:

incode1.png

You can extend this table with any text you want/need.

 

Next, we define a table (ProjectParsed) for the parsed result:

incode2.png

Note that the three fields together form the primary key.

 

Assuming that the raw project data is in a (linked) table which is called ProjectRaw (you can name it anything you like, but you have to make a slight modification in the coming VBA code if you name it different), this VBA subroutine removes the unwanted text and parses the 6 digit occurrences in IN_CODE into separate rows/records in the table ProjectParsed:

Sub RemoveAndParse()
  'Declare
  Dim sInCode As String, sSQL As String
  Dim i As Integer
    
  'Switch off warnings
  DoCmd.SetWarnings False
    
  'Remove all earlier records from the result table
  sSQL = "DELETE FROM ProjectParsed"
  DoCmd.RunSQL sSQL
      
  'Open tables Project and UnwantedText as recordsets to loop over
  Dim rsProject As DAO.Recordset
  Set rsProject = CurrentDb.OpenRecordset("ProjectRaw", dbOpenTable)

  Dim rsUnwantedText As DAO.Recordset
  Set rsUnwantedText = CurrentDb.OpenRecordset("UnwantedText", dbOpenTable)

  'Loop over the (raw) projects
  Do Until rsProject.EOF
    sInCode = rsProject!IN_CODE
    
    'Remove all the spaces from IN_CODE
    sInCode = Replace(sInCode, " ", "")
    
    'Remove the unwanted text from IN_CODE
    rsUnwantedText.MoveFirst 'go back to first unwanted text for every cycle
    'Loop over the unwanted text
    Do Until rsUnwantedText.EOF
      sInCode = Replace(sInCode, rsUnwantedText!Unwanted, "") 'remove text (replace it by a blank)
      rsUnwantedText.MoveNext 'move to next unwanted text
    Loop
    
    'Parse the 6 digit codes
    For i = 1 To Len(sInCode) Step 6
      sSQL = "INSERT INTO ProjectParsed VALUES('" & rsProject!PROJECT_ID & "', '" & _
             rsProject!PROJECT_TITLE & "', '" & Mid(sInCode, i, 6) & "')"
      DoCmd.RunSQL sSQL
    Next
    
    rsProject.MoveNext 'move to next project
  Loop
  
  rsUnwantedText.Close
  rsProject.Close
  
  'Switch on warnings again
  DoCmd.SetWarnings True
End Sub

 

I assume and hope that the comments in the code explain what the subroutine does. You can run the routine from the VBA editor or you can call it from a button in one of your Access forms.

 

After running it you'll get something in the table ProjectParsed like this:

incode3.png

 

Hope this does the job. And if you need more clarification, please let me know.

 

Best wishes,

Tieme

 

@Tieme Woldman 

 

That looks really good, I will give it a whirl.

 

thanks.