Comma Separated Array List

[JHusk]

I've just hit a wall here and my brain refuses to bring this information up.

I have a cell with a series of room names in it, separated by commas: Master Bedroom, Master Bathroom, Laundry Room, Stairs, etc. Call this A1

In "D1" I'd like to place a formula that will take those room names and put them in a dynamic list in D1, D2, D3, D4 D5, etc. Basically creating any array of all everything between the commas.

Thanks in advance...

 

[alansidman]

Since you have O365 you have power query which is called Get and Transform Data and found on the Data Tab

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

Book10
	A	B	C	D
1	Column1			Column1
2	Master Bedroom, Master Bathroom, Laundry Room, Stairs, etc			Master Bedroom
3				Master Bathroom
4				Laundry Room
5				Stairs
6				etc
Sheet2

 

[Dave Patton]

ToCol.xlsm
	A	B	C	D
1	Master Bedroom,Master Bathroom,Laundry Room,Stairs			Master Bedroom
2				Master Bathroom
3				Laundry Room
4				Stairs
5
6
1d

Cell Formulas
Range	Formula
D1:D4	D1	=TEXTSPLIT(A1,,",")
Dynamic array formulas.

 

[Trebor76]

Same solution provided by Dave Patton

 

[Fluff]

Another option if you don't have textsplit yet.

=FILTERXML("<k><m>"&SUBSTITUTE(A1,",","</m><m>")&"</m></k>","//m")

 

[JHusk]

ToCol.xlsm
	A	B	C	D
1	Master Bedroom,Master Bathroom,Laundry Room,Stairs			Master Bedroom
2				Master Bathroom
3				Laundry Room
4				Stairs
5
6
1d

Cell Formulas
Range	Formula
D1:D4	D1	=TEXTSPLIT(A1,,",")
Dynamic array formulas.

This is exactly what I was looking for!! Thank you...

 

[JHusk]

@Dave Patton here's a interesting addition to this (what resulted in a very simply solution lol), what If I wanted to limit it to 15 items between the commas?

Say I have a cell with 25 room names in it, all room separated by commas. but I only want the first 15. Would something like the MAX function work?

 

[Fluff]

As you have textsplit, you also have take, so how about

=TAKE(TEXTSPLIT(A1,,","),15)

 

[JHusk]

As you have textsplit, you also have take, so how about

=TAKE(TEXTSPLIT(A1,,","),15)

Absolutely Perfect @Fluff!! Thank you very much!!!

 

[Fluff]

Glad we could help & thanks for the feedback.