Forum Discussion

jklemm1's avatar
jklemm1
Copper Contributor
Oct 17, 2022

Extraction formula help

I need a formula to extract the AC-UG from the text string below and then put them in two separate columns without using the text-to-columns feature - TIA!    EM-AC-UG-Course-ENG-122-Week3-TOT
excel
Formulas and Functions
Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 17, 2022

jklemm1 

- Thanks OliverScheurich for copying/pasting your data and sharing the file

- You did not answer mathetes question re. version of Excel you use (this is important)

 

Attached is another Power Query option:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    SplitInList = Table.AddColumn(Source, "LIST", each
        List.FirstN(List.Skip(Text.Split([Spalte1], "-")), 2)
    ),
    ToTable = Table.FromRows(SplitInList[LIST])
in
    ToTable

 

And a 365 worksheet formula (spills as dynamic array) based on the same Table:

=DROP(
  REDUCE(0,Tabelle1[Spalte1],
    LAMBDA(seed,current,
      VSTACK(seed, TEXTSPLIT(TEXTAFTER(TEXTBEFORE(current,"-",3),"-"),"-"))
    )
  ), 1
)

 

Resources