How to split a cells data into multiple rows?

[RahulNa]

Hi,

I have an excel data where in one column the information is coming like below

Cell F2- AWS; Nvidia; Atos; Azure

There are few cells like that. Is it possible to split them in one below all like on the above F2 be AWS, F3 Nvidia, F4 Atos likewise while retaining the rest of information in another columns as it is.

Name	Region	Amount	Revenue	Level	Partner
Alpha	APAC	100,000	100,000	1	AWS
Gamma	EMEA	250,000	100,000	2	Nvidia; Atos
Beta	APAC	350,000	100,000	3	AWS; Nvidia; Atos

So IF the Partner cell is split

Then I would like the all information spilled like below

Name	Region	Amount	Revenue	Level	Partner
Beta	APAC	350,000	100,000	3	AWS
Beta	APAC	350,000	100,000	3	Nvidia
Beta	APAC	350,000	100,000	3	Atos

 

[Cubist]

Here's one option but it has a character limit. If your dataset is large, might want to consider VBA.

Book1
	A	B	C	D	E	F
1	Name	Region	Amount	Revenue	Level	Partner
2	Alpha	APAC	100,000	100,000	1	AWS
3	Gamma	EMEA	250,000	100,000	2	Nvidia; Atos
4	Beta	APAC	350,000	100,000	3	AWS; Nvidia; Atos
5
6
7
8	Alpha	APAC	100000	100000	1	AWS
9	Gamma	EMEA	250000	100000	2	Nvidia
10	Gamma	EMEA	250000	100000	2	Atos
11	Beta	APAC	350000	100000	3	AWS
12	Beta	APAC	350000	100000	3	Nvidia
13	Beta	APAC	350000	100000	3	Atos
Sheet9

Cell Formulas
Range	Formula
A8:F13	A8	=TEXTSPLIT(TEXTJOIN(",",,TEXTJOIN(",",,BYROW(A2:F4,LAMBDA(br,TEXTJOIN(",",,TEXTJOIN("|",,DROP(br,0,-1))&"|"&TEXTSPLIT(TAKE(br,1,-1),,"; ")))))),"|",",")
Dynamic array formulas.

 

[alansidman]

Here is an alternative solution with Power Query aka Get and Transform and found on the data tab.

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