...

List positions of each character

 

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


A

B

C

D

E

F

G

H

I

J

1

p

u

u

u

u

p

p

p

u

p

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

A

B

C

D

E

1

p1

u2345

p678

u9

p10

Use SPL XLL:

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

Picture2png

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.

Source:https://www.reddit.com/r/excel/comments/1ctg0a1/fill_multiple_cells_with_entries_of_list_of/


 
 •  101 View  •