# Comma Separated Array List



## JHusk (Dec 21, 2022)

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 (Dec 21, 2022)

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"
```

Book10ABCD1Column1Column12Master Bedroom, Master Bathroom, Laundry Room, Stairs, etcMaster Bedroom3Master Bathroom4Laundry Room5Stairs6etcSheet2


----------



## Dave Patton (Dec 21, 2022)

ToCol.xlsmABCD1Master Bedroom,Master Bathroom,Laundry Room,StairsMaster Bedroom2Master Bathroom3Laundry Room4Stairs561dCell FormulasRangeFormulaD1:D4D1=TEXTSPLIT(A1,,",")Dynamic array formulas.


----------



## Trebor76 (Dec 22, 2022)

Same solution provided by Dave Patton


----------



## Fluff (Dec 22, 2022)

Another option if you don't have textsplit yet.
	
	
	
	
	
	



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


----------



## JHusk (Dec 22, 2022)

Dave Patton said:


> ToCol.xlsmABCD1Master Bedroom,Master Bathroom,Laundry Room,StairsMaster Bedroom2Master Bathroom3Laundry Room4Stairs561dCell FormulasRangeFormulaD1:D4D1=TEXTSPLIT(A1,,",")Dynamic array formulas.


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


----------



## JHusk (Dec 22, 2022)

@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 (Dec 22, 2022)

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



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


----------



## JHusk (Dec 22, 2022)

Fluff said:


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


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


----------



## Fluff (Dec 22, 2022)

Glad we could help & thanks for the feedback.


----------

