Split Text by Deliminator - Dynamic text

[flatterno]

Hi!

I have a table with two columns, headed Name and Jobs range A1:B28 (which is fixed).

Column B contains the jobs that each person in column A will perform separated by a comma. This is a dynamically filled column, with the data pulled from various cells on other cells. Each cell in B contains a formula ='Matrix'!CK33 to ='Cast Matrix'!CK60 etc.

Everyone will have a maximum of 6 jobs each

All I am trying to do is split the jobs up into the following columns (C:H) with some cells empty if people have fewer than 6 jobs).

Text to columns doesn't work as firstly all it sees is the formula in the cell, but also it won't update dynamically.

This table is never updated itself, the updates occur in the worksheet named Matrix, I just want this sheet as a report almost

Any help appreciated

 

[Fluff]

Can you post a sample of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[flatterno]

paul	wash
simon	walk the dog, clean the windows
claire	hoover, put out the bins, ironing
hilary	shopping
trevor	planning food
abigail	rest
ryan	prepare food
william	exercise, mop floors

That's a sample of my original

And this is what I would like to achieve

wash
walk the dog	clean the windows
hoover	put out the bins	ironing
shopping
planning food
rest
prepare food
exercise	mop floors

 

[Fluff]

OK, two options, one will only work on a PC

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1			PC only				PC or Mac
2	paul	wash	wash				wash
3	simon	walk the dog, clean the windows	walk the dog	clean the windows			walk the dog	clean the windows
4	claire	hoover, put out the bins, ironing	hoover	put out the bins	ironing		hoover	put out the bins	ironing
5	hilary	shopping	shopping				shopping
6	trevor	planning food	planning food				planning food
7	abigail	rest	rest				rest
8	ryan	prepare food	prepare food				prepare food
9	william	exercise, mop floors	exercise	mop floors			exercise	mop floors
10
Main

Cell Formulas
Range	Formula
C2,C5:C8,C9:D9,C4:E4,C3:D3	C2	=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(B2,",","</m><m>"),"&","&amp;")&"</m></k>","//m"))
G2,G5:G8,G9:H9,G4:I4,G3:H3	G2	=LET(Txt,","&B2&",",Qty,SEQUENCE((LEN(Txt)-LEN(SUBSTITUTE(Txt,",","")))-1),TRANSPOSE(TRIM(REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt,",","~",Qty+1))-1),1,FIND("~",SUBSTITUTE(Txt,",","~",Qty)),""))))

 

[jtakw]

Hi,

Also try this, drag as far down and across as needed, don't know if it works on Mac, let us know:

Book3.xlsx
	A	B	C	D	E	F
1	paul	wash	wash
2	simon	walk the dog, clean the windows	walk the dog	clean the windows
3	claire	hoover, put out the bins, ironing	hoover	put out the bins	ironing
4	hilary	shopping	shopping
5	trevor	planning food	planning food
6	abigail	rest	rest
7	ryan	prepare food	prepare food
8	william	exercise, mop floors	exercise	mop floors
Sheet873

Cell Formulas
Range	Formula
C1:F8	C1	=TRIM(MID(SUBSTITUTE(","&$B1,",",REPT(" ",100)),COLUMNS($C1:C1)*100,100))

 

[Sufiyan97]

Hi,

Also try this, drag as far down and across as needed, don't know if it works on Mac, let us know:

Book3.xlsx
	A	B	C	D	E	F
1	paul	wash	wash
2	simon	walk the dog, clean the windows	walk the dog	clean the windows
3	claire	hoover, put out the bins, ironing	hoover	put out the bins	ironing
4	hilary	shopping	shopping
5	trevor	planning food	planning food
6	abigail	rest	rest
7	ryan	prepare food	prepare food
8	william	exercise, mop floors	exercise	mop floors
Sheet873

Cell Formulas
Range	Formula
C1:F8	C1	=TRIM(MID(SUBSTITUTE(","&$B1,",",REPT(" ",100)),COLUMNS($C1:C1)*100,100))

Hello jtakw,

I asked this type of question earlier but still in this formula I am not getting it
can you please explain =SUBSTITUTE(","&$B1,",",REPT(" ",100) what is purpose of ","&B1 and not only B1?

 

[jtakw]

Hello jtakw,

can you please explain =SUBSTITUTE(","&$B1,",",REPT(" ",100) what is purpose of ","&B1 and not only B1?

As you probably already know, the MID part of the formula is extracting partial text from the text string, using SUBSTITUTE for the comma (",") for 100 Spaces.
By adding ","&B1, using the COLUMNS, extraction Starts at 100, at 100 intervals per column to the right.

Another way to write the formula Without ","&B1 would be:

=TRIM(MID(SUBSTITUTE($B2,",",REPT(" ",100)),COLUMNS($C2:C2)*100-99,100))

Notice instead of ","&B1, we subtracted 99 from 100, so now the extraction Starts at 1, adding 100 to each interval.

Results are the same, either way.

 

[Sufiyan97]

As you probably already know, the MID part of the formula is extracting partial text from the text string, using SUBSTITUTE for the comma (",") for 100 Spaces.
By adding ","&B1, using the COLUMNS, extraction Starts at 100, at 100 intervals per column to the right.

Another way to write the formula Without ","&B1 would be:

=TRIM(MID(SUBSTITUTE($B2,",",REPT(" ",100)),COLUMNS($C2:C2)*100-99,100))

Notice instead of ","&B1, we subtracted 99 from 100, so now the extraction Starts at 1, adding 100 to each interval.

Results are the same, either way.

Thank you very mcuh jtakw
.