Obecný vzorec
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
souhrn
Chcete-li rozdělit text pomocí oddělovače a transformovat výsledek do pole, můžete použít funkci FILTERXML s pomocí funkcí SUBSTITUTE a TRANSPOSE. V zobrazeném příkladu je vzorec v D5:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Poznámka: FILTERXML není k dispozici v Excelu na Macu ani v Excel Online.
Poznámka: Tento trik jsem se naučil od Billa Jelena ve videu MrExcel.
Vysvětlení
Excel nemá funkci vyhrazenou pro rozdělení textu na pole, podobnou funkci explodování PHP nebo metodě rozdělení Pythonu. Jako řešení můžete použít funkci FILTERXML po prvním přidání značek XML do textu.
V ukázaném příkladu máme několik textových řetězců oddělených čárkami, jako je tento:
"Jim,Brown,33,Seattle,WA"
Cílem je rozdělit informace do samostatných sloupců pomocí čárky jako oddělovače.
Prvním úkolem je přidat do tohoto textu označení XML, aby jej bylo možné analyzovat jako XML pomocí funkce FILTERXML. Z každého pole v textu libovolně uděláme prvek uzavřený s nadřazeným prvkem. Začínáme s funkcí SUBSTITUTE zde:
SUBSTITUTE(B5,",","")
Výsledkem SUBSTITUTE je textový řetězec, jako je tento:
"JimBrown33SeattleWA"
Abychom zajistili dobře vytvořené značky XML a zabalili všechny prvky do nadřazeného prvku, připravíme a připojíme další značky XML, jako je tento:
""&SUBSTITUTE(B5,",","")&""
Tím se získá textový řetězec, jako je tento (za účelem čitelnosti jsou přidány konce řádků)
" Jim Brown 33 Seattle WA "
Tento text je dodáván přímo do funkce FILTERXML jako argument xml s výrazem Xpath "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath je jazyk pro analýzu a "// y" vybírá všechny prvky. Výsledkem z FILTERXML je vertikální pole takto:
("Jim";"Brown";33;"Seattle";"WA")
Protože v tomto případě chceme horizontální pole, zabalíme funkci TRANSPOSE kolem FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Výsledkem je horizontální pole, jako je toto:
("Jim","Brown",33,"Seattle","WA")
který se v aplikaci Excel 365 rozlévá do rozsahu D5: H5.
