Forum Discussion

dr-numbers's avatar
dr-numbers
Copper Contributor
Jun 30, 2025
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...
excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Jun 30, 2025

    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

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 30, 2025

I do not fully understand either the data or the required output but the attached file contains some helper functions that I published which address Microsoft's array of array shortcomings.

= MAPĪ»(GPO[about:config setting equivalent],
    LAMBDA(element, TEXTSPLIT(element, ";"))
  )

https://gist.github.com/pbartxl/a14b250985da31be843ce9ff35d888fc

Resources