Forum Discussion
Splitting families into individuals excel formatting
Hi, I'm a newbie with excel. I am not having much luck finding a way to take an address list that is formatted with both spouses as a name, and change it to individuals. I Can split the names, text t...
If you have 365 you might use a LAMBDA.
This LAMBDA simply pulls each row twice.
=LAMBDA(range,LET(data,range,r,ROWS(data),CHOOSEROWS(data,INT(SEQUENCE(r*2,,1,1/2)))))This formula uses SCAN on the Member Name column. With the aid of some extraction, it pulls the name before or after the "and" where needed:
=LAMBDA(range,SCAN("",CHOOSECOLS(PullRows(range),2),LAMBDA(a,v,IF(TEXTBEFORE(v," ")=a,TEXTAFTER(v," and "),TEXTBEFORE(v," ")))))
HSTACK combines the arrays horizontally:
=LET(data,E2:L4,HSTACK(TAKE(PullRows(data),,1),MemberName(data),CHOOSECOLS(PullRows(data),SEQUENCE(,6,3,1))))