SOLVED

Spreading data contained in column A across to other columns to form a table

%3CLINGO-SUB%20id%3D%22lingo-sub-1558232%22%20slang%3D%22en-US%22%3ESpreading%20data%20contained%20in%20column%20A%20across%20to%20other%20columns%20to%20form%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558232%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20copy%20and%20paste%20multi-line%20data%26nbsp%3B%20from%20a%20web%20page%20which%20has%20all%20gone%20into%20column%20A.%26nbsp%3B%20This%20isn't%20a%20text%20to%20column%20use%20case%20but%20one%20where%20I%20want%20to%20place%20data%20from%20column%20A%20into%20relevant%20headed%20columns%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20sample%20if%20that%20helps.%26nbsp%3B%20The%20major%20headings%20are%3A%3C%2FP%3E%3CP%3ECOMPANY%20SECTOR%20SALES%20PHONE%20CITY%20WEBSITE%20CONTACT%20SIC%20CODE%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1558232%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1558268%22%20slang%3D%22en-US%22%3ERe%3A%20Spreading%20data%20contained%20in%20column%20A%20across%20to%20other%20columns%20to%20form%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558268%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3ERather%20than%20using%20copy%20and%20paste%20method%20to%20copy%20data%20from%20web%2C%20you%20could%20have%20used%20the%20Power%20Query%20to%20fetch%20the%20data%20from%20the%20website.%20Then%2C%20you%20can%20clean%20and%20transform%20the%20data%20us%20in%20Power%20Query%20%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20can%20watch%20the%20video%20in%20the%20link%20below%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fm.youtube.com%2Fwatch%3Fv%3DNdUZx_yyEqY%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fm.youtube.com%2Fwatch%3Fv%3DNdUZx_yyEqY%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1558639%22%20slang%3D%22de-DE%22%3ESubject%3A%20Spreading%20data%20contained%20in%20column%20A%20across%20to%20other%20columns%20to%20form%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558639%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F745847%22%20target%3D%22_blank%22%3E%40Melise1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EOnce%20without%20a%20VBA%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3E%20If%20you%20liked%20my%20solution%2C%20please%20mark%20it%20as%20the%20correct%20answer%20...%20if%20possible%20with%20a%20like%20so%20that%20everyone%20else%20can%20be%20informed.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3EIf%20you%20didn't%20like%20my%20answer%2C%20please%20give%20me%20a%20quick%20feedback.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3Ethank%20you%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ENikolino%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1559797%22%20slang%3D%22en-US%22%3ERe%3A%20Spreading%20data%20contained%20in%20column%20A%20across%20to%20other%20columns%20to%20form%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1559797%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F745847%22%20target%3D%22_blank%22%3E%40Melise1%3C%2FA%3E%26nbsp%3B%2C%20I%20took%20a%20shot%20at%20this%20using%20Power%20Query.%20It's%20now%20perfect%20since%20the%20data%20in%20the%20column%20A%20is%20not%20consistent.%20There%20is%20no%20pattern%20that%20holds%20for%20all%20items%20past%20the%20Sector.%20Take%20a%20look%20at%20the%20solution%20and%20let%20me%20know%20if%20maybe%20there's%20a%20way%20to%20describe%20the%20input%20data%20pattern%20that%20can%20be%20used%20to%20automate%20the%20task.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1560041%22%20slang%3D%22en-US%22%3ERe%3A%20Spreading%20data%20contained%20in%20column%20A%20across%20to%20other%20columns%20to%20form%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1560041%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Melise1_0-1596263819983.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209515iE54ED104AE3EFA48%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Melise1_0-1596263819983.png%22%20alt%3D%22Melise1_0-1596263819983.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFoiled!%26nbsp%3B%20I'm%20using%20Chrome%2C%20BTW.%26nbsp%3B%20Any%20way%20around%20this%3F%26nbsp%3B%20Thanks....%3C%2FP%3E%3CP%3EAlso%2C%20I'm%20wanting%20to%20move%20to%20columns%20as%20labeled%20in%20this%20snip%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Melise1_1-1596263916207.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209516iD8B45A1AB67937B9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Melise1_1-1596263916207.png%22%20alt%3D%22Melise1_1-1596263916207.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThanks!%20Melise%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%3CLINGO-SUB%20id%3D%22lingo-sub-1560045%22%20slang%3D%22en-US%22%3EBetreff%3A%20Spreading%20data%20contained%20in%20column%20A%20across%20to%20other%20columns%20to%20form%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1560045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%20Hi%20Nikolino....not%20quite%20what%20was%20looking%20for....more%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Melise1_0-1596264056789.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209517i9238B91D0591DB6F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Melise1_0-1596264056789.png%22%20alt%3D%22Melise1_0-1596264056789.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Etake%20the%20lines%20of%20info%20under%20each%20company%20name%20and%20moving%20them%20to%20the%20same%20line%20as%20the%20company%20name%20but%20in%20their%20own%20column.%26nbsp%3B%20make%20sense%3F%26nbsp%3B%20Thanks!%20Melise%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1560048%22%20slang%3D%22en-US%22%3ERe%3A%20Spreading%20data%20contained%20in%20column%20A%20across%20to%20other%20columns%20to%20form%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1560048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3BWow!%26nbsp%3B%20That's%20it....can%20you%20do%20this%20for%20the%20attached%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1560217%22%20slang%3D%22en-US%22%3ERe%3A%20Spreading%20data%20contained%20in%20column%20A%20across%20to%20other%20columns%20to%20form%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1560217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F745847%22%20target%3D%22_blank%22%3E%40Melise1%3C%2FA%3E%26nbsp%3B%2C%20the%20tricky%20part%20is%20not%20every%20item%20has%20the%20same%2010%20parts.%20For%20example%2C%20%22Little%20Wickets%22%20does%20not%20have%20the%20%22Sales%22%20part%20and%20this%20makes%20it%20difficult%20to%20automate.%3C%2FP%3E%3CTABLE%20width%3D%22360%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22360%22%3ELittle%20Wickets%20(East%20Midlands)%20Ltd%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAmusement%20And%20Recreation%20Services%2C%20Sports%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E441159248476%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENottingham%2C%20Nottinghamshire%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CA%20href%3D%22http%3A%2F%2Flittlewickets.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Elittlewickets.com%3C%2FA%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E79%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECRM%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20mentioned%20that%20you%20copied%20and%20pasted%20from%20a%20website.%20Does%20the%20website%20have%20the%20data%20formatted%20in%20a%20way%20that%20includes%20all%20the%20parts%3F%20Perhaps%20the%20manual%20copy%2Fpaste%20messed%20things%20up.%20Can%20you%20check%20to%20see%20if%20you%20can%20get%20a%20more%20consistent%20data%20set%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562122%22%20slang%3D%22en-US%22%3ERe%3A%20Spreading%20data%20contained%20in%20column%20A%20across%20to%20other%20columns%20to%20form%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562122%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%20Hi%20thanks%20for%20your%20help.%26nbsp%3B%20Thing%20is%2C%20some%20fields%20are%20empty%20-%20rather%20than%20inserting%20a%20null%20character%2C%20the%20next%20line%20is%20always%20a%20field%20containing%20data.%26nbsp%3B%20This%20is%20the%20issue.%26nbsp%3B%20I%20did%20a%20bit%20of%20clean%20up%20but%20the%20set%20goes%20off%20register%20because%20of%20this.%26nbsp%3B%20Thanks%20anyway!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562123%22%20slang%3D%22en-US%22%3EBetreff%3A%20Spreading%20data%20contained%20in%20column%20A%20across%20to%20other%20columns%20to%20form%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%20Thanks...I%20think%20the%20translation%20from%20German%20to%20English%20did%20not%20make%20complete%20sense.%20Thanks%20for%20your%20help%20anyway!%26nbsp%3B%20M%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562615%22%20slang%3D%22en-US%22%3EBetreff%3A%20Spreading%20data%20contained%20in%20column%20A%20across%20to%20other%20columns%20to%20form%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3BThank%20you...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1560234%22%20slang%3D%22de-DE%22%3ESubject%3A%20Spreading%20data%20contained%20in%20column%20A%20across%20to%20other%20columns%20to%20form%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1560234%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F745847%22%20target%3D%22_blank%22%3E%40Melise1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EAlthough%20there%20is%20a%20lot%20built%20in%20with%20Power%20Query%2C%20here%20is%20a%20small%20additional%20solution%20with%20VBA%20...%20simple%20but%20effective.%20%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%3EOpen%20with%20the%20worksheet%20and%20press%20the%20button.%3C%2FSPAN%3E%20%3CSPAN%3EIf%20you%20press%20it%20several%20times%2C%20you%20will%20see%20that%20it%20can%20continue%20to%20enter%20as%20you%20imagine.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-result-transliteration-container%20result-transliteration-container%20transliteration-container%22%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-footer%20source-or-target-footer%20tlid-copy-target%22%3E%3CP%3EIf%20you%20find%20this%20helpful%2C%20please%20mark%20it%20as%20Like%20(click%20thumbs%20up)%20and%20as%20%22Best%20Answer%22%2C%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a copy and paste multi-line data  from a web page which has all gone into column A.  This isn't a text to column use case but one where I want to place data from column A into relevant headed columns

 

I have a sample if that helps.  The major headings are:

COMPANY SECTOR SALES PHONE CITY WEBSITE CONTACT SIC CODE

13 Replies
Hello,

Rather than using copy and paste method to copy data from web, you could have used the Power Query to fetch the data from the website. Then, you can clean and transform the data us in Power Query

You can watch the video in the link below

https://m.youtube.com/watch?v=NdUZx_yyEqY

@Melise1 

 

Once without a VBA

If you liked my solution, please mark it as the correct answer ... if possible with a like so that everyone else can be informed. If you didn't like my answer, please give me a quick feedback.

 

thank you
Nikolino
I know I don't know anything (Socrates)

@Melise1 , I took a shot at this using Power Query. It's now perfect since the data in the column A is not consistent. There is no pattern that holds for all items past the Sector. Take a look at the solution and let me know if maybe there's a way to describe the input data pattern that can be used to automate the task.

@Abiola1 

 

Melise1_0-1596263819983.png

 

Foiled!  I'm using Chrome, BTW.  Any way around this?  Thanks....

Also, I'm wanting to move to columns as labeled in this snip:

Melise1_1-1596263916207.png

Thanks! Melise

 

 

 

@Nikolino  Hi Nikolino....not quite what was looking for....more like this:

 

Melise1_0-1596264056789.png

take the lines of info under each company name and moving them to the same line as the company name but in their own column.  make sense?  Thanks! Melise

@TheAntony Wow!  That's it....can you do this for the attached?

@Melise1 , the tricky part is not every item has the same 10 parts. For example, "Little Wickets" does not have the "Sales" part and this makes it difficult to automate.

Little Wickets (East Midlands) Ltd
Amusement And Recreation Services, Sports
441159248476
Nottingham, Nottinghamshire
littlewickets.com
1
79
CRM

 

You mentioned that you copied and pasted from a website. Does the website have the data formatted in a way that includes all the parts? Perhaps the manual copy/paste messed things up. Can you check to see if you can get a more consistent data set?

@Melise1 

Although there is a lot built in with Power Query, here is a small additional solution with VBA ... simple but effective.
Open with the worksheet and press the button. If you press it several times, you will see that it can continue to enter as you imagine.
 

@TheAntony  Hi thanks for your help.  Thing is, some fields are empty - rather than inserting a null character, the next line is always a field containing data.  This is the issue.  I did a bit of clean up but the set goes off register because of this.  Thanks anyway!

@Nikolino  Thanks...I think the translation from German to English did not make complete sense. Thanks for your help anyway!  M

Best Response confirmed by Melise1 (Occasional Contributor)
Solution
Which solution is now appropriate to your desire?
Please do not forget that the VBA solution leaves one line free every 10 rows, so that all addresses of the column in the row can be kept apart.



If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.

Nikolino
I know I don't know anything (Socrates)
Always at your service. Wish you a nice day Nikolino