Forum Discussion

Copper Contributor

Nov 15, 2022

Solved

Text join + filter data

i'm trying to join text that are equal in coloumn A but values start in some text cell and end on other text cell but it has to be in the line taht is equal in coloumn A =UNIRTEXTO(", ";VERDADEIRO...

excel

office 365

OliverScheurich
Nov 15, 2022
Pedro_Duarte
=LET(filter,TEXTJOIN(",",,FILTER(Folha2!B2:AB5,Folha2!A2:A5=Folha1!B1)),after,LEFT(filter,FIND(B2,filter)-1),textafter,TEXTAFTER(filter,after),before,FIND(B3,filter),textbefore,RIGHT(filter,LEN(filter)-before-LEN(B3)+1),TEXTBEFORE(textafter,textbefore))
You can try this formula.

Folha1:

Folha2:

=TEXTVOR(TEXTNACH(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));LINKS(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));FINDEN(B2;TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1)))-1));RECHTS(TEXTNACH(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));LINKS(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));FINDEN(B2;TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1)))-1));LÄNGE(TEXTNACH(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));LINKS(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));FINDEN(B2;TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1)))-1)))-FINDEN(B3;TEXTNACH(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));LINKS(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));FINDEN(B2;TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1)))-1)))-LÄNGE(B3)+1))

Alternatively you can try this formula which is in cell B7 of sheet "Folha1". I didn't translate this formula because of it's lenght.
Paredes.xlsx11 KB

OliverScheurich

Gold Contributor

Nov 15, 2022

Pedro_Duarte

=LET(filter,TEXTJOIN(",",,FILTER(Folha2!B2:AB5,Folha2!A2:A5=Folha1!B1)),after,LEFT(filter,FIND(B2,filter)-1),textafter,TEXTAFTER(filter,after),before,FIND(B3,filter),textbefore,RIGHT(filter,LEN(filter)-before-LEN(B3)+1),TEXTBEFORE(textafter,textbefore))

You can try this formula.

Folha1:

Folha2:

=TEXTVOR(TEXTNACH(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));LINKS(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));FINDEN(B2;TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1)))-1));RECHTS(TEXTNACH(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));LINKS(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));FINDEN(B2;TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1)))-1));LÄNGE(TEXTNACH(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));LINKS(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));FINDEN(B2;TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1)))-1)))-FINDEN(B3;TEXTNACH(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));LINKS(TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1));FINDEN(B2;TEXTVERKETTEN(",";;FILTER(Folha2!B2:AB5;Folha2!A2:A5=Folha1!B1)))-1)))-LÄNGE(B3)+1))

Alternatively you can try this formula which is in cell B7 of sheet "Folha1". I didn't translate this formula because of it's lenght.

Paredes.xlsx11 KB

Pedro_Duarte

Copper Contributor

Nov 15, 2022

OliverScheurich

Thank you, i had to adjust some cells (bold) but it worked like a charm.

=LET(filter;UNIRTEXTO(",";;FILTRAR(Folha2!B:AB;Folha2!A:A=Folha1!C4));after;ESQUERDA(filter;LOCALIZAR(F3;filter)-1);textafter;TEXTODEPOIS(filter;after);before;LOCALIZAR(F4;filter);textbefore;DIREITA(filter;NÚM.CARAT(filter)-before-NÚM.CARAT(F4)+1);TEXTOANTES(textafter;textbefore))