SOLVED
Home

Text to Columns for Multiple Columns at Once

%3CLINGO-SUB%20id%3D%22lingo-sub-759620%22%20slang%3D%22en-US%22%3EText%20to%20Columns%20for%20Multiple%20Columns%20at%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759620%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%26nbsp%3Bmaybe%20100%26nbsp%3Bcolumns%20that%20have%20all%20similar%20info%20like%20%22number%20X%22.%20All%20of%20these%20columns%20need%20to%20be%20split%20into%20%22number%22%20and%20%22X%22.%20So%20far%20I%20have%20been%20using%20the%20text%20to%20columns%20feature%20for%20each%20individual%20column.%20I%20am%20wondering%2C%20is%20there%20a%20way%20to%20split%20all%20of%20these%20at%20once%3F%20Thanks%20in%20advanced!%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3E%7CA%26nbsp%3B%26nbsp%3B%26nbsp%3B%7CB%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7CC%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7CD%26nbsp%3B%26nbsp%3B%26nbsp%3B%7C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20to--%26gt%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%7CA%26nbsp%3B%26nbsp%3B%26nbsp%3B%7CB%26nbsp%3B%26nbsp%3B%7CC%26nbsp%3B%26nbsp%3B%26nbsp%3B%7CD%26nbsp%3B%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E%7C1%26nbsp%3BX%7C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7C2%20Y%7C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7C%201%20%26nbsp%3B%7C%20X%26nbsp%3B%20%7C%202%20%26nbsp%3B%7C%20Y%26nbsp%3B%20%7C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-759620%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%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759977%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20Columns%20for%20Multiple%20Columns%20at%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759977%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377971%22%20target%3D%22_blank%22%3E%40frri3484%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20already%20have%20blank%20columns%20next%20to%20your%20data%2C%20you%20can%20use%20a%20simple%20macro%20to%20automate%20your%20text%20to%20column%20conversions.%20I%20suggest%20recording%20the%20conversion%20of%20one%20column%2C%20then%20generalizing%20the%20recorded%20macro%20because%20the%20text%20to%20columns%20method%20is%20a%20little%20complicated.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20code%20below%20was%20based%20on%20a%20recorded%20macro.%20It%20assumes%20you%20select%20the%20cells%20to%20be%20converted%2C%20that%20every%20other%20column%20will%20be%20converted%2C%20and%20that%20you%20already%20have%20the%20necessary%20blank%20columns.%3C%2FP%3E%0A%3CPRE%3ESub%20TextToColumnator()%0ADim%20rg%20As%20Range%0ADim%20i%20As%20Long%2C%20n%20As%20Long%0AApplication.ScreenUpdating%20%3D%20False%0AApplication.DisplayAlerts%20%3D%20False%0ASet%20rg%20%3D%20Selection%0An%20%3D%20rg.Columns.Count%0AFor%20i%20%3D%201%20To%20n%20Step%202%0A%20%20%20%20rg.Columns(i).TextToColumns%20Destination%3A%3Drg.Cells(1%2C%20i)%2C%20DataType%3A%3DxlDelimited%2C%20_%0A%20%20%20%20%20%20%20%20TextQualifier%3A%3DxlDoubleQuote%2C%20ConsecutiveDelimiter%3A%3DTrue%2C%20Tab%3A%3DTrue%2C%20_%0A%20%20%20%20%20%20%20%20Semicolon%3A%3DFalse%2C%20Comma%3A%3DFalse%2C%20Space%3A%3DTrue%2C%20Other%3A%3DFalse%2C%20FieldInfo%20_%0A%20%20%20%20%20%20%20%20%3A%3DArray(Array(1%2C%201)%2C%20Array(2%2C%201))%2C%20TrailingMinusNumbers%3A%3DTrue%0ANext%0AApplication.DisplayAlerts%20%3D%20True%0AEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-760271%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20Columns%20for%20Multiple%20Columns%20at%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-760271%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377971%22%20target%3D%22_blank%22%3E%40frri3484%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EThere%20is%20another%20way%20of%20splitting%20the%20columns%20in%20excel%20using%20Left()%20%26amp%3B%20Right()%20functions.%3C%2FP%3E%3CP%3EIf%20the%20formation%20of%20your%20data%20is%20somehow%20uniform%20you%20can%20use%20these%20functions%20and%20easily%20split%20all%20columns%20in%20one%20go.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20sample%20file%20just%20for%20understanding%20purpose.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20also%20share%20your%20file%2C%20so%20more%20solutions%20can%20be%20suggested.%3C%2FP%3E%3CP%3EThanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-769999%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20Columns%20for%20Multiple%20Columns%20at%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-769999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3EI%20was%20hoping%20this%20would%20work%20since%20it's%20easier%2C%20however%20my%20data%20is%20all%20over%20the%20place%20in%20length.%20But%20thanks%20for%20the%20response!%20I%20never%20knew%20this%20was%20a%20function%20before.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772011%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20Columns%20for%20Multiple%20Columns%20at%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772011%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377971%22%20target%3D%22_blank%22%3E%40frri3484%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%22however%20my%20data%20is%20all%20over%20the%20place%20in%20length%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20why%20you%20should%20always%20post%20a%20sample%20workbook%20with%20data%20illustrating%20the%20problem.%20Then%20instead%20of%20getting%20a%20suggestion%20that%20solves%20the%20question%20you%20asked%2C%20you%20get%20one%20that%20addresses%20the%20specifics%20of%20your%20actual%20data.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%20columns%20are%20of%20different%20length%2C%20that%20is%20easily%20addressed%20with%20a%20tweak%20to%20the%20macro.%20If%20the%20blank%20columns%20between%20your%20data%20don't%20exist%2C%20that%20can%20also%20be%20addressed.%20If%20you%20may%20have%20more%20than%20one%20delimiter%20in%20each%20cell%2C%20even%20that%20can%20also%20be%20addressed%20(though%20with%20somewhat%20more%20difficulty).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBrad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-773929%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20Columns%20for%20Multiple%20Columns%20at%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773929%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374823%22%20target%3D%22_blank%22%3E%40Brad_Yundt%3C%2FA%3E%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20mean%3F%20I'm%20sorry%2C%20I'm%20new%20to%20this%20forum%20and%20didn't%20see%20a%20place%20to%20attach%20examples%2C%20so%20I%20copied%20and%20pasted%20this%20in.%20I%20can%20easily%20add%20in%20the%20spaces%20between%20data%20columns%2C%20but%20I%20get%20stuck%20on%20separating%20the%20data%20from%20there%20(separating%20the%20number%20including%20%5B%5D%20from%20the%20letter).%20So%20far%20a%20recorded%20macro%20is%20working%20best%2C%20but%20I%20haven't%20been%20able%20to%20make%20it%20work%20outside%20specific%20columns.%20I'm%20still%20trying%20to%20learn%20how%20macros%20work%20to%20fully%20understand%20how%20to%20edit%20the%20recorded%20macro%2C%20so%20I'm%20pursuing%20that%20for%20now.%20Any%20help%20or%20how-to%20video%20references%20are%20appreciated.%26nbsp%3B%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERaw%20Data%3C%2FTD%3E%3CTD%3E0.072%20J%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E0.0002%20J%3C%2FTD%3E%3CTD%3E%5B0.00055%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.05%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.00025%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.007%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.005%5D%20U%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.0025%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.000275%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.0025%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.005%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.00025%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.005%5D%20U%3C%2FTD%3E%3CTD%3E0.05%20J%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.005%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3CTD%3E%5B0.005%5D%20U%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAdd%20Spaces%20w%20sort%3C%2FTD%3E%3CTD%3E0.072%20J%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSPAN%3E0.0002%20J%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSPAN%3E%5B0.00055%5D%20U%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.00025%5D%20U%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.007%5D%20U%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.0025%5D%20U%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.000275%5D%20U%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.0025%5D%20U%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.00025%5D%20U%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.005%5D%20U%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.005%5D%20U%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%5B0.0005%5D%20U%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-773945%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20Columns%20for%20Multiple%20Columns%20at%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374823%22%20target%3D%22_blank%22%3E%40Brad_Yundt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOh%20wow!%20I%20got%20the%20macro%20you%20shared%20to%20work%2C%20thank%20you!%20I%20was%20initially%20trying%20to%20record%20my%20own%20macro%20instead%2C%20which%20I'd%20still%20like%20to%20figure%20out.%20But%20for%20my%20current%20purposes%2C%20yours%20works%20exactly%20how%20I%20need!%3C%2FP%3E%3CP%3E%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774256%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20Columns%20for%20Multiple%20Columns%20at%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377971%22%20target%3D%22_blank%22%3E%40frri3484%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20is%20also%20possible%20to%20use%20a%20macro%20to%20insert%20the%20blank%20columns.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20your%20data%2C%20then%20run%20the%20macro.%20It%20will%20add%20blank%20columns%20after%20each%20column%20in%20your%20selection.%3C%2FP%3E%0A%3CPRE%3ESub%20InsertBlankColumns()%0ADim%20rg%20As%20Range%0ADim%20i%20As%20Long%2C%20n%20As%20Long%0AApplication.ScreenUpdating%20%3D%20False%0ASet%20rg%20%3D%20Selection.EntireColumn%0An%20%3D%20rg.Columns.Count%0AFor%20i%20%3D%20n%20To%201%20Step%20-1%0A%20%20%20%20rg.Columns(i%20%2B%201).EntireColumn.Insert%0ANext%0AEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774302%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20Columns%20for%20Multiple%20Columns%20at%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774302%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much!%3C%2FLINGO-BODY%3E
frri3484
Occasional Contributor

I have maybe 100 columns that have all similar info like "number X". All of these columns need to be split into "number" and "X". So far I have been using the text to columns feature for each individual column. I am wondering, is there a way to split all of these at once? Thanks in advanced!

Example:

|A   |B     |C    |D   |         to-->        |A   |B  |C   |D   |

|1 X|        |2 Y|      |                        | 1  | X  | 2  | Y  |            

8 Replies
Solution

@frri3484 

If you already have blank columns next to your data, you can use a simple macro to automate your text to column conversions. I suggest recording the conversion of one column, then generalizing the recorded macro because the text to columns method is a little complicated.

 

The code below was based on a recorded macro. It assumes you select the cells to be converted, that every other column will be converted, and that you already have the necessary blank columns.

Sub TextToColumnator()
Dim rg As Range
Dim i As Long, n As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set rg = Selection
n = rg.Columns.Count
For i = 1 To n Step 2
    rg.Columns(i).TextToColumns Destination:=rg.Cells(1, i), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Next
Application.DisplayAlerts = True
End Sub

@frri3484 

Hi,

There is another way of splitting the columns in excel using Left() & Right() functions.

If the formation of your data is somehow uniform you can use these functions and easily split all columns in one go.

 

I have attached a sample file just for understanding purpose.

 

You may also share your file, so more solutions can be suggested.

Thanks, 

Tauqeer

@tauqeeracmaI was hoping this would work since it's easier, however my data is all over the place in length. But thanks for the response! I never knew this was a function before.

@frri3484 

"however my data is all over the place in length"

 

That's why you should always post a sample workbook with data illustrating the problem. Then instead of getting a suggestion that solves the question you asked, you get one that addresses the specifics of your actual data.

 

If the columns are of different length, that is easily addressed with a tweak to the macro. If the blank columns between your data don't exist, that can also be addressed. If you may have more than one delimiter in each cell, even that can also be addressed (though with somewhat more difficulty).

 

Brad

@Brad_Yundt

Is this what you mean? I'm sorry, I'm new to this forum and didn't see a place to attach examples, so I copied and pasted this in. I can easily add in the spaces between data columns, but I get stuck on separating the data from there (separating the number including [] from the letter). So far a recorded macro is working best, but I haven't been able to make it work outside specific columns. I'm still trying to learn how macros work to fully understand how to edit the recorded macro, so I'm pursuing that for now. Any help or how-to video references are appreciated.  :)

 1357
Raw Data0.072 J[0.0005] U[0.0005] U[0.0005] U
 0.0002 J[0.00055] U[0.05] U[0.0005] U
 [0.00025] U[0.007] U[0.0005] U[0.005] U
 [0.0025] U[0.0005] U[0.0005] U[0.0005] U
 [0.000275] U[0.0025] U[0.005] U[0.0005] U
 [0.00025] U[0.005] U0.05 J[0.0005] U
 [0.005] U[0.0005] U[0.0005] U[0.005] U
     
     
 1234
Add Spaces w sort0.072 J [0.0005] U 
 0.0002 J [0.00055] U
 [0.00025] U [0.007] U 
 [0.0025] U [0.0005] U 
 [0.000275] U [0.0025] U 
 [0.00025] U [0.005] U 
 [0.005] U [0.0005] U 

@Brad_Yundt 

Oh wow! I got the macro you shared to work, thank you! I was initially trying to record my own macro instead, which I'd still like to figure out. But for my current purposes, yours works exactly how I need!

:)

@frri3484 

It is also possible to use a macro to insert the blank columns.

 

Select your data, then run the macro. It will add blank columns after each column in your selection.

Sub InsertBlankColumns()
Dim rg As Range
Dim i As Long, n As Long
Application.ScreenUpdating = False
Set rg = Selection.EntireColumn
n = rg.Columns.Count
For i = n To 1 Step -1
    rg.Columns(i + 1).EntireColumn.Insert
Next
End Sub
Thank you very much!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies