Split multiple text in cell into multiple rows

[ConfusedMum]

Hi all,

This should be really easy, I am sure, but I am totally stuck! I know about Text to Columns, but that doesn't seem to work if you have multiple text to split and I am not sure where to go next with this.

This is an example of what I have got at the moment:

What I am trying to get is this:

So I can split the postcodes into alphabetical order, with the sales person linked to that postcode, into individual rows; then I can add extra details like region, admin contact, supervisor, etc...

Any ideas wonderful Excel guru people please, please, please?

Thank you SO much,
Cathy

 

[shaowu459]

Please update your account details to indicate which version of Office you are using. Additionally, please upload your sample data using XL2BB.

 

[JamesCanale]

If you happen to have 365, you can try:

Book7
	A	B	C	D	E	F
1	Salesperson	Postcodes Served			Postcode	Person
2	Person 1	P1;P2;P3			C4	Person 3
3	Person 2	P4;P5;P6;P7			C5	Person 3
4	Person 3	C4;C5;C6			C6	Person 3
5	Person 4	P11;P12;P13			M44	Person 5
6	Person 5	M44;M45;M46;M47			M45	Person 5
7					M46	Person 5
8					M47	Person 5
9					P1	Person 4
10					P11	Person 4
11					P12	Person 4
12					P13	Person 4
13					P2	Person 1
14					P3	Person 1
15					P4	Person 2
16					P5	Person 2
17					P6	Person 2
18					P7	Person 2
Sheet1

Cell Formulas
Range	Formula
E2:E18	E2	=SORT(TEXTSPLIT(TEXTJOIN(";",TRUE,Table1[Postcodes Served]),,";"))
F2:F18	F2	=INDEX(Table1[Salesperson],MAX(IFERROR(SIGN(SEARCH(E2,Table1[Postcodes Served]))*SEQUENCE(ROWS(Table1[Postcodes Served])),0)))
Dynamic array formulas.

 

[shaowu459]

工作簿1
	A	B	C	D	E	F	G
1	Salesperson	Postcodes Served			Postcode	Person
2	Person 1	P1;P2;P3			P1	Person 1
3	Person 2	P4;P5;P6;P7			P2	Person 1
4	Person 3	C4;C5;C6			P3	Person 1
5	Person 4	P11;P12;P13			P4	Person 2
6	Person 5	M44;M45;M46;M47			P5	Person 2
7					P6	Person 2
8					P7	Person 2
9					C4	Person 3
10					C5	Person 3
11					C6	Person 3
12					P11	Person 4
13					P12	Person 4
14					P13	Person 4
15					M44	Person 5
16					M45	Person 5
17					M46	Person 5
18					M47	Person 5
19
Sheet1

Cell Formulas
Range	Formula
E1:F18	E1	=REDUCE({"Postcode","Person"},A2:A6,LAMBDA(x,y,VSTACK(x,IF({1,0},TEXTSPLIT(OFFSET(y,,1),,";"),y))))
Dynamic array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ConfusedMum]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

SORRY, I have done that now.
I am using Office 365 on a Windows 10 PC.

Thanks loads (again!)
Cathy

 

[Fluff]

Thanks for that . Do either of the solutions do what you need?

 

[alansidman]

Here is an alternative solution with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Postcodes Served", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Postcodes Served")
in
    #"Split Column by Delimiter"

Book5
	A	B	C	D	E
1	Salesperson	Postcodes Served		Salesperson	Postcodes Served
2	Person 1	P1;P2;P3		Person 1	P1
3	Person 2	P4;P5;P6;P7		Person 1	P2
4	Person 3	C4;C5;C6		Person 1	P3
5	Person 4	P11;P12;P13		Person 2	P4
6	Person 5	M44;M45;M46;M47		Person 2	P5
7				Person 2	P6
8				Person 2	P7
9				Person 3	C4
10				Person 3	C5
11				Person 3	C6
12				Person 4	P11
13				Person 4	P12
14				Person 4	P13
15				Person 5	M44
16				Person 5	M45
17				Person 5	M46
18				Person 5	M47
Sheet1