Excel separate a cell value per row

I have a spreadsheet with rows of data like this.

I need to run a formula, so it looks like this.

Basically, taking the group serial numbers and then breaking them out 1 per line.

So far I have only come up with doing it by hand.
worked up the formula to remove the beginning and ending parenthesizes.

=LEFT(RIGHT(E141,LEN(E141)-1),LEN(RIGHT(E141,LEN(E141)-1))-1)

but am stuck trying to figure out how to break out the numbers for each line?

You can use either <kbd>EXCEL FORMULAS</kbd> or <kbd>POWER QUERY</kbd> here to accomplish this task. Assuming no Excel version constraints as per the tags listed in the question:

Using <kbd>TEXTBEFORE( )</kbd> & <kbd>TEXTAFTER( )</kbd>

• Formula used in cell <kbd>E2</kbd>

=LET(
     a,A2:A8,
     b,B2:B8,
     c,C2:C8,
     d,TEXTAFTER(","&b,{",","(",")"},SEQUENCE(,MAX(LEN(b)))),
     e,TEXTBEFORE(d&",",{",",")","("}),
     f,DROP(UNIQUE(TOCOL(e,3)),1),
     HSTACK(a,f,c))

Using <kbd>REDUCE( )</kbd> <kbd>TEXTSPLIT( )</kbd>

• Formula used in cell <kbd>E2</kbd>

=LET(
     a,A2:C8,
     b,INDEX(a,,2),
     c,DROP(REDUCE("",b,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,",",{"(",")"},1)))),1),
     HSTACK(INDEX(a,,1),TOCOL(UNIQUE(c),3),INDEX(a,,3)))

Using <kbd>POWER QUERY</kbd> will be the easiest approach.

To perform the task using <kbd>POWER QUERY</kbd>, follow the steps:

• First convert the source range into a table and name it accordingly, for this example I have named it as Table1

• Next, open a blank query from <kbd>Data</kbd> Tab --> <kbd>Get & Transform Data</kbd> --> <kbd>Get Data</kbd> --> <kbd>From Other Sources</kbd> --> <kbd>Blank Query</kbd>

• The above lets the <kbd>Power Query</kbd> window opens, now from <kbd>Home</kbd> Tab --> <kbd>Advanced Editor</kbd> --> And paste the following <kbd>M-Code</kbd> by removing whatever you see, and press <kbd>Done</kbd>

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Text Between Delimiters" = Table.TransformColumns(Source, {{"serial numbers", each Text.BetweenDelimiters(_, "(", ")"), type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted Text Between Delimiters", {{"serial numbers", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "serial numbers"),
    #"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter", {"serial numbers"})
in
    #"Removed Duplicates"

• Lastly, to import it back to <kbd>Excel</kbd> --> Click on <kbd>Close & Load</kbd> or <kbd>Close & Load To</kbd> --> The first one which clicked shall create a <kbd>New Sheet</kbd> with the required output while the latter will prompt a window asking you where to place the result.

One more alternative approach :

• Formula used in cell <kbd>E2</kbd>

=DROP(
      REDUCE("",TOROW(UNIQUE(TEXTBEFORE(TEXTAFTER(B2:B8,{"(",","}),")"))),
      LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,,",")))),1)

To get all the data merged together:

=LET(
     α,DROP(REDUCE("",TOROW(UNIQUE(TEXTBEFORE(TEXTAFTER(B2:B8,{"(",","}),")"))),
            LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,,",")))),1),
     HSTACK(A2:A8,α,C2:C8))

One other way, but if it crosses the character limitations because of <kbd>CONCAT( )</kbd> Function, then it might not work.

• Formula used in cell <kbd>F2</kbd>

=HSTACK(A2:A8,
        DROP(TEXTSPLIT(CONCAT(TOROW(
             UNIQUE(TEXTBEFORE(TEXTAFTER(B2:B8,{"(",","}),")")))&","),,","),-1),
        C2:C8)