[Excel] Text splitten spezial (1)

[shift-del]

Moin

Im MrExcel-Forum gab es eine Anfrage zur Text-Splittung.
Ausgangslage ist ein Text der einen Datensatz mit mehreren Nodes enthält (Überschriften und Daten). Vor und nach dem Datensatz stehen weitere Text-Nodes.
Erhöhte Schwierigkeit: Die Anzahl der Text-Nodes vor und nach dem Datensatz ist nicht einheitlich.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G
1	Bla bla bla bla:::Name:::Rudi Rastlos:::ID:::8524:::Alter:::55:::Blubb blubb blubb
2
3	Name	3
4	ID	5
5	Alter	7
6
7	Name	ID	Alter			Methode	Formel-Länge
8	Rudi Rastlos	8524	55			position() mod 2	163
9	Rudi Rastlos	8524	55			INDEX()	88
10	Rudi Rastlos	8524	55			postition() XVERWEIS()	232
11	Rudi Rastlos	8524	55			following-sibling	106
12
13
14
15	Bla bla bla bla:::Wau wau wau:::Name:::Rudi Rastlos:::ID:::8524:::Alter:::55:::Blubb blubb blubb
16
17	Name	4
18	ID	6
19	Alter	8
20
21	Name	ID	Alter			Methode	Formel-Länge
22	Rudi Rastlos	8524	55			position() mod 2	167
23	Rudi Rastlos	8524	55			INDEX()	91
24	Rudi Rastlos	8524	55			postition() XVERWEIS()	236
25	Rudi Rastlos	8524	55			following-sibling	109
26
27
28
29	Bla bla bla bla:::Name:::Rudi Rastlos:::ID:::8524:::Alter:::55:::Blubb blubb blubb:::Miau Miau Miau:::Grr Grr Grr
30
31	Name	3
32	ID	5
33	Alter	7
34
35	Name	ID	Alter			Methode	Formel-Länge
36	Rudi Rastlos	8524	55			position() mod 2	167
37	Rudi Rastlos	8524	55			INDEX()	91
38	Rudi Rastlos	8524	55			postition() XVERWEIS()	236
39	Rudi Rastlos	8524	55			following-sibling	109

Zelle	Formel
B3	=VERGLEICH(A3;XMLFILTERN("<y><z>"&WECHSELN(A1;":::";"</z><z>")&"</z></y>";"//z");0)+1
A8	=MTRANS(XMLFILTERN("<y><z>"&WECHSELN(A1;":::";"</z><z>")&"</z></y>";"//z[position() mod 2="&--ISTUNGERADE(B3)&" and position()>"&B3-1&" and position()<"&B5+1&"]"))
A9	=MTRANS(INDEX(XMLFILTERN("<y><z>"&WECHSELN(A1;":::";"</z><z>")&"</z></y>";"//z");B3:B5))
A10	=LET( a;XMLFILTERN("<y><z>"&WECHSELN(A1;":::";"</z><z>")&"</z></y>";"//z[position()>0 and position()<8]"); b;XMLFILTERN("<y><z>"&WECHSELN(A1;":::";"</z><z>")&"</z></y>";"//z[position()>1 and position()<9]"); XVERWEIS($A$7:$C$7;a;b))
A11	=XMLFILTERN("<y><z>"&WECHSELN(A1;":::";"</z><z>")&"</z></y>";"//z[.='"&A7:C7&"']/following-sibling::z[1]")
G8	=LÄNGE(FORMELTEXT(A8))

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

Das zweite Beispiel enthält einen weiteren Node vor dem Datensatz.
Das dritte Beispiel enthält zwei weitere Nodes nach dem Datensatz.
Die ersten beiden Formeln scheiterten ursprünglich an diesen Beispielen weil Werte fest in die Formeln codiert wurden. Durch Dynamisierung (goldfarbene Felder) wurde dieses Manko behoben.
Die ausgewiesene Formellänge bezieht sich auf die Formel in der jeweiligen Zeile. Für Formel 1 und Formel 2 müssten noch die Formeln der Hilfszellen (organgefarbene Felder) mit einbezogen werden.
Alle Formeln laufen über. Es wird also Office 365 benötigt - eventuell funktioniert es auch mit dem neuen Office 2021.

Fazit: xpath ist ziemlich mächtig wenn man alle Möglichkeiten kennt.

PS: Formel 1 bis Formel 3 wurden von mir beigetragen, Formel 4 wurde vom User Fluff beigetragen.