List the Positions of Each Character

Judith-Excel-Sharing - Jul 2 - - Dev Community

Problem description & analysis:

Below is a row of letters. The letters in certain positions are continuous.

original table

We need to arrange them according to the format of “letter+positions”, as shown below:

desired table

Solution:

Use SPL XLL:

=spl("=[(d=E@1(?)).group@op(~).(d(~1) / ~.concat())]",A1:J1)
Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

result table with code entered

Explanation:

E@1 function converts a multilayer sequence to a single-layer one. group@op groups members without sorting members before and returns sequence numbers of members. ~1 represents the first sub-member of the current member.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .