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.

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

Solution:

Use SPL XLL:

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

As shown in the picture below:

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.