Formuła ogólna
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Podsumowanie
Aby podzielić tekst za pomocą separatora i przekształcić wynik w tablicę, możesz użyć funkcji FILTERXML z pomocą funkcji SUBSTITUTE i TRANSPOSE. W przedstawionym przykładzie formuła w D5 to:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Uwaga: FILTERXML nie jest dostępny w programie Excel na komputerze Mac ani w aplikacji Excel Online.
Uwaga: nauczyłem się tej sztuczki od Billa Jelena w filmie MrExcel.
Wyjaśnienie
Program Excel nie ma funkcji służącej do dzielenia tekstu na tablicę, podobnie jak funkcja rozbijania PHP lub metoda dzielenia w Pythonie. Aby obejść ten problem, możesz użyć funkcji FILTERXML, po uprzednim dodaniu znaczników XML do tekstu.
W pokazanym przykładzie mamy kilka ciągów tekstowych oddzielonych przecinkami, takich jak ten:
"Jim,Brown,33,Seattle,WA"
Celem jest podzielenie informacji na oddzielne kolumny, używając przecinka jako separatora.
Pierwszym zadaniem jest dodanie znaczników XML do tego tekstu, aby można go było przeanalizować jako XML za pomocą funkcji FILTERXML. Zamierzamy arbitralnie uczynić każde pole w tekście elementem otoczonym elementem nadrzędnym. Zaczynamy od funkcji SUBSTITUTE tutaj:
SUBSTITUTE(B5,",","")
Wynikiem SUBSTITUTE jest ciąg tekstowy podobny do tego:
"JimBrown33SeattleWA"
Aby zapewnić poprawne sformułowanie tagów XML i zawinąć wszystkie elementy w element nadrzędny, dołączamy wcześniej i dołączamy więcej tagów XML, takich jak ten:
""&SUBSTITUTE(B5,",","")&""
Daje to taki ciąg tekstowy (dodane podziały wierszy dla większej czytelności)
" Jim Brown 33 Seattle WA "
Ten tekst jest dostarczany bezpośrednio do funkcji FILTERXML jako argument xml, z wyrażeniem Xpath „// y”:
FILTERXML("JimBrown33SeattleWA","//y")
Xpath jest językiem analizującym, a „// y” wybiera wszystkie elementy. Wynik FILTERXML to tablica pionowa, taka jak ta:
("Jim";"Brown";33;"Seattle";"WA")
Ponieważ w tym przypadku potrzebujemy tablicy poziomej, zawijamy funkcję TRANSPOSE wokół FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Wynikiem jest pozioma tablica, taka jak ta:
("Jim","Brown",33,"Seattle","WA")
który rozlewa się na zakres D5: H5 w Excel 365.
