In the Excel table below, column A is the grouping column and column B contains strings separated by ">". Values in column B can be seen as the concatenated detail data:

The task is to split each row into multiple rows. The rule is this: keep values of column A unchanged; split each value in column B into detail data items and concatenate them in a cumulative way:

Use SPL XLL to enter the formula below:

=spl("=?.conj(~(2).split("">"").(~=~[-1] | ~).(~.concat("">"")).([?.~(1),~]))",A2:B4)

The conj()function concatenates the subsets; split() function splits a string into a set; and concat()function concatenates members of a set into a string. ~ represents the current member, ~[-1] references the directly previous members and | calculates union.

Source:https://stackoverflow.com/questions/78382496/split-text-into-smaller-one-and-group-by-each-id