Forum Discussion
Solved
Splitting Array Elements into Multiple Elements
I am trying to extract an array from a column of a table. The column is text only and some cells have multiple values separated by semicolons. I am trying to extract an array that has a separate item...
if it is a small set you can get away with
=UNIQUE(TEXTSPLIT(TEXTJOIN(";",,GPO[about:config setting equivalent]),,";"))
but I'm guessing that is not the case so you will probably need this:
=UNIQUE(DROP(REDUCE("",GPO[about:config setting equivalent],LAMBDA(p,q,VSTACK(p,TEXTSPLIT(q,,";")))),1))
that said I notice 2 things:
in your equation above you have FALSE,TRUE for the unique which will return only cases where the value is only found 1x but if you want all unique cases that TRUE should be FALSE or just left off
Variants
formula
=LET(
data, GPO[about:config setting equivalent],
fnSplit, LAMBDA(p,q, IF( SEQUENCE(q)=q, TEXTSPLIT( INDEX(data,q,),";"), p)),
TOCOL( REDUCE("", SEQUENCE( ROWS(data) ),fnSplit ), 2)
)OfficeScript
function main(workbook: ExcelScript.Workbook) {
const sheet = workbook.getActiveWorksheet()
const rangeName: string = "GPO[about:config setting equivalent]"
const data = [[rangeName]].concat(
sheet.getRange(rangeName)
.getValues()
.map( x => x.toString().split(";"))
.flat().map( x => [x] )
)
const target = sheet.getRange("H19")
.getResizedRange(data.length - 1, 0)
target.clear()
target.setValues(data)
target.getCell(0,0).getFormat().getFill().setColor("green")
target.getCell(0, 0).getFormat().getFont().setColor("white")
}