How do I split data from a column into multiple columns?

**dazzys** · 09-28-2022, 02:06 PM

Hi All,

I have some data currently stored in a column which I'd like to split into new columns. For example, in cell A1-A3 I have the following data:-

Main Colour:*Rose Gold
Gauge (Thickness):*18g (1 mm)
Diameter:*6mm

I'd like to move this data from the A column into columns B1-D1 so it looks like:-

Main Colour:*Rose Gold Gauge (Thickness):*18g (1 mm) Diameter:*6mm

Ideally, I'd like the ability to split out the words 'Main Colour:', Gauge (Thickness):, Diameter:, just leaving the actual data but that may be harder?

Can someone help please?

Cheers
Daz

**HansDouwe** · 09-28-2022, 02:15 PM

Please add a workbook (see yellow banner at the top of the page)
with this example and what your ideal result looks like exactly.

**dazzys** · 09-28-2022, 02:21 PM

Sorry...added now (I hope!).

**JohnTopley** · 09-28-2022, 02:32 PM

In B1

=IFERROR(INDEX($A$1:$A$204,((ROWS($1:1)-1)*3+COLUMN(A$1))),"")

copy across to D and down

**sandy666** · 09-28-2022, 02:48 PM

you can try Power Query (XL2013 with PQ add-in) and Pivot Table

Label	Value
Main Colour	Black
	Gold
	Rainbow
	Rose Gold
	Silver
Gauge (Thickness)	16g (1.2 mm)
	18g (1 mm)
Diameter	10mm
	12mm
	6mm
	8mm

**dazzys** · 09-28-2022, 02:48 PM

That's amazing John. Thanks so much. Can you explain what that command does?

**dazzys** · 09-28-2022, 02:49 PM

That looks great Sandy but Can you describe what I need to actually do please?

**HansDouwe** · 09-28-2022, 02:53 PM

Just leaving the actual data please try this:

Create a header and try this formula:

Formula:

Please Login or Register  to view this content.

**sandy666** · 09-28-2022, 02:58 PM

Originally Posted by dazzys

That looks great Sandy but Can you describe what I need to actually do please?

if you really have an XL2013 you'll need to download and install Power Query add-in
select your data and Ctrl+T, next Data tab and From Table
then: split column by colon, trim columns from spaces, rename columns as you wish

Insert - Pivot Table

pt1.png

pt2.png

**dazzys** · 09-28-2022, 03:01 PM

That's perfect Hans. Thanks very much!

**JohnTopley** · 09-28-2022, 03:12 PM

=IFERROR(INDEX($A$1:$A$204,((ROWS($1:1)-1)*3+COLUMN(A$1))),"")

The RED calculates every 3 rows so ROWS($1:1) =1 then (1-1)*3 =0 and then add the column A1=1, b1=2 c1=3) so we get rows 1,2 and 3 for column A as the formula is copied across

ROWS($1:2)=2 so we get(2-1)*3 = 3 then add the column to get row 4,5,6 for column A

Hope this helps

**HansDouwe** · 09-28-2022, 03:20 PM

Glad to help, thx for the feedback and rep

.

**dazzys** · 10-12-2022, 05:02 AM

Hi,

Back again. Can someone please tell me how I can automate the moving of data from one column to the other using power query if possible? Basically, I've scavenging data from ebay sales and pasting this into excel but the way it pastes means some of the data ends up in one column where I want it on other(s).

Cheers

**sandy666** · 10-12-2022, 05:14 AM

post example of raw data and expected result

**dazzys** · 10-12-2022, 05:49 AM

Here we go. There are 3 sheets; Raw Data, Desired data and Improved layout. Ideally, if simple to do, I'd prefer to have the raw data laid out as in the improved layout but if too complex then the desired data layout would be fine.

Many thanks!

**sandy666** · 10-12-2022, 07:01 AM

just for now, maybe later I will get better idea

Custom	Custom.1.1	Custom.1.2	Custom.1	Custom.2	Custom.3
0i*	Crystal Diameter	1.8mm	1.29	1	10 Oct 2022 at 6:01:10pm BST
	Main Colour	Purple	1.29	1	10 Oct 2022 at 6:01:10pm BST
	Type / Design	STRAIGHT I SHAPED STUD	1.29	1	10 Oct 2022 at 6:01:10pm BST
	(blank)	(blank)	1.29	1	10 Oct 2022 at 6:01:10pm BST
a6*	Crystal Diameter	2.6mm	1.29	1	10 Oct 2022 at 6:10:39pm BST
	Main Colour	Clear	1.29	1	10 Oct 2022 at 6:10:39pm BST
	Type / Design	SCREW SHAPED STUD	1.29	1	10 Oct 2022 at 6:10:39pm BST
	(blank)	(blank)	1.29	1	10 Oct 2022 at 6:10:39pm BST

or

Custom	Custom.1.1	Custom.1.2	Custom.1	Custom.2	Custom.3
a***6	Main Colour	Clear	1.29	1	10 Oct 2022 at 6:10:39pm BST
a***6	Type / Design	SCREW SHAPED STUD	1.29	1	10 Oct 2022 at 6:10:39pm BST
a***6	Crystal Diameter	2.6mm	1.29	1	10 Oct 2022 at 6:10:39pm BST
0***i	Main Colour	Purple	1.29	1	10 Oct 2022 at 6:01:10pm BST
0***i	Type / Design	STRAIGHT I SHAPED STUD	1.29	1	10 Oct 2022 at 6:01:10pm BST
0***i	Crystal Diameter	1.8mm	1.29	1	10 Oct 2022 at 6:01:10pm BST

**JohnTopley** · 10-12-2022, 07:18 AM

try ("Desired")

in A

=IFERROR(IF(MOD((ROWS($1:1)-1),7)=0,INDEX('Raw Data'!$A$1:$A$204,(INT((ROWS($1:1)-1)/7)*7+COLUMN(A$1))),""),"")

in B

=IFERROR(IF(MOD((ROWS($1:1)-1),7)<=2,INDEX('Raw Data'!$A$1:$A$204,(INT((ROWS($1:1)-1)/7)*7+MOD((ROWS($1:1)),7)+1)),""),"")

in C

=IFERROR(IF(MOD((ROWS($1:1)-1),7)=0,INDEX('Raw Data'!$A$1:$A$204,(INT((ROWS($1:1)-1)/7)*7+COLUMNS($A$1:E$1))),""),"")

copy to E

Copy all formulas down

**dazzys** · 10-12-2022, 07:46 AM

Thanks Sandy but I'm not sure what I'm looking at here or how to implement it. Could you advise please?

**sandy666** · 10-12-2022, 08:18 AM

sure
here is:

**dazzys** · 10-12-2022, 03:16 PM

Thanks for all the help guys. I guess what I'm trying to do is evolving and I'm trying to limit the amount of data cleaning I need to do in order to get to the final results I need to analyse such as the most popular colours, sizes, etc.

I've managed to learn how to strip out the time stamp which I'm not interested in, leaving the order date which I need. I've modified what I actually need and created a new sample data set which I've attached. You'll notice there are now four sheets, one sheet for each of the two products I need to analyse the sales data on and the corresponding 'cleaned & formatted' data. If someone could help me create this please that would be amazing.

Cheers

**JohnTopley** · 10-12-2022, 03:30 PM

Where does the "Rings" / "Studs" data originate as does not appear to me to be an order. I ask because is not possible to go from orginal to final, rather what appears to an interim?

**dazzys** · 10-12-2022, 03:36 PM

It comes from ebay (Terapeak research data) John. I just copy and paste it into excel then do some simple formatting to it. Here is a small screen grab of the actual data before I copy & paste it into Excel.

**sandy666** · 10-12-2022, 03:48 PM

Buyer	Main Colour	Thickness	Diameter	Price	Qty	Date of order
c***l	Black	1.2mm	6mm	1.89	1	12/10/2022
o***r	Silver	1.2mm	10mm	1.89	1	12/10/2022
k***l	Silver	1.2mm	10mm	1.89	1	12/10/2022
4***1	Silver	1.2mm	12mm	1.89	1	12/10/2022

Please Login or Register  to view this content.

but you have to do the rest yourself

If that takes care of your questions hit Add Reputation at the foot of the posts which have helped you

**dazzys** · 10-12-2022, 04:14 PM

Thanks Sandy but I have no idea what you want me to do with that code example?

**sandy666** · 10-12-2022, 04:38 PM

rings and studs are almost the same (structure is the same) so M-code is almost the same
I did rings but for studs you need to read M-code and try yourself. I cannot do everything instead of you

here is excel file but with one result

**dazzys** · 10-12-2022, 04:43 PM

Thanks Sandy and I really do appreciate all the time you've spent on this for me. I simply have never used this M-Code you speak of and don't know where I open it within the file example you attached...sorry!

**sandy666** · 10-12-2022, 05:10 PM

1. https://learn.microsoft.com/en-us/power-query/
2. Data tab - Show Queries - double click on table in the right side pane - it will open Power Query Editor and then - Home tab - Advanced Editor - you should see the same M-code I posted

**JohnTopley** · 10-13-2022, 02:35 AM

For "rings"

in A2

=IFERROR(INDEX(rings!$A$2:$A$204,(ROWS($1:1)-1)*3+1),"")

in B2

=IFERROR(MID(INDEX(rings!$A$2:$E$204,(ROWS($1:1)-1)*3+COLUMN(A$1),2),FIND(":",INDEX(rings!$A$2:$E$204,(ROWS($1:1)-1)*3+COLUMN(A$1),2),1)+1,255),"")

copy to column d

in E

=IFERROR(INDEX(rings!$A$2:$E$204,(ROWS($1:1)-1)*3+1,COLUMN(C$1)),"")

Copy to G

See "rings" sheet

**dazzys** · 10-13-2022, 11:15 AM

Thanks again Sandy. One thing I noticed though is that the results don't reflect if a buyer made more than one purchase. I tried to add the same buyer name more than once to see the results.

**sandy666** · 10-13-2022, 11:22 AM

solution is tailored to your example and description

**sandy666** · 10-13-2022, 12:39 PM

is that what you want?

Buyer	Product	Price	Qty	Date of order
c***l	Main Colour:*Black	1.89	1	12/10/2022
	Thickness:*1.2mm
	Diameter:*6mm
o***r	Main Colour:*Silver	1.89	1	12/10/2022
	Thickness:*1.2mm
	Diameter:*10mm
k***l	Main Colour:*Silver	1.89	1	12/10/2022
	Thickness:*1.2mm
	Diameter:*10mm
4***1	Main Colour:*Silver	1.89	1	12/10/2022
	Thickness:*1.2mm
	Diameter:*12mm
z***x	Main Colour: Yellow	2.22	2	12/10/2022
	Thickness:*3.2mm
	Diameter: 44mm
xyzvq	Main Colour: Pink	321.00	5	15/12/2023
	Thickness: 555mm
	Diameter: 999mm
xyzvq	Main Colour: Orange	45.00	3	15/12/2023
	Thickness: 505mm
	Diameter: 909mm

Buyer	Main Colour	Thickness	Diameter	Price	Qty	Date of order
c***l	Black	1.2mm	6mm	1.89	1	12/10/2022
o***r	Silver	1.2mm	10mm	1.89	1	12/10/2022
k***l	Silver	1.2mm	10mm	1.89	1	12/10/2022
4***1	Silver	1.2mm	12mm	1.89	1	12/10/2022
z***x	Yellow	3.2mm	44mm	2.22	2	12/10/2022
xyzvq	Pink	555mm	999mm	321	5	15/12/2023
xyzvq	Orange	505mm	909mm	45	3	15/12/2023

Please Login or Register  to view this content.

if you are happy with this solution hit Add Reputation

**dazzys** · 10-14-2022, 04:20 AM

That's great Sandy. Thanks again for all the help. I assume I have to manually resize the table and refresh the data in order for the query to update the numbers? I'll now try and work out the code for the studs!

**sandy666** · 10-14-2022, 04:33 AM

You can resize source vertically (but not horizontally)
just copy "element"

xyzvq	Main Colour: Orange	45.00	3	15/12/2023
	Thickness: 505mm
	Diameter: 909mm

and paste it on the end of the table then change data in it (remember about structure which should the same as previous elements)
but if elements will be exactly the same it will not appear in the result table
next refresh green (result) table, that's all folks

if you are happy with this solution don't forget to hit Add Reputation

**dazzys** · 10-14-2022, 05:17 AM

Thanks Sandy. I posted the raw data into the worksheet and got rid of the time stamp element as before. I then resized the table by dragging the small handle down and refreshed the data. All appears to be well!

**sandy666** · 10-14-2022, 05:21 AM

the method of updating the source table is up to you

How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Re: How do I split data from a column into multiple columns?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] 2 columns data split to more columns based on groups in 2nd column

[SOLVED] PowerQuery - Split column into multiple columns based on another column

Bookmarks

Bookmarks

Posting Permissions