Üldine valem
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Kokkuvõte
Teksti jagamiseks eraldajaga ja tulemuse massiiviks teisendamiseks võite kasutada funktsiooni FILTERXML funktsioonide SUBSTITUTE ja TRANSPOSE abiga. Näidatud näites on valem D5-s:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Märkus. FILTERXML pole Maci Excelis ega Excel Online'is saadaval.
Märkus: õppisin selle triki Bill Jelenelt MrExceli videost.
Selgitus
Excelis pole funktsiooni, mis on pühendatud massiiviks teksti jagamiseks, sarnaselt PHP explode funktsioonile või Pythoni jagamismeetodile. Lahendusena saate kasutada funktsiooni FILTERXML pärast XML-i märgistuse esmast lisamist tekstile.
Näidatud näites on meil mitu komaga eraldatud tekstistringi:
"Jim,Brown,33,Seattle,WA"
Eesmärk on jagada teave eraldi veergudeks, kasutades eraldajana koma.
Esimene ülesanne on lisada sellele tekstile XML-i märgistus, et seda saaks funktsiooniga FILTERXML XML-vormingus sõeluda. Teeme meelevaldselt igast tekstiväljast elemendi, mis on ümbritsetud vanemelemendiga. Alustame funktsiooniga SUBSTITUT siin:
SUBSTITUTE(B5,",","")
SUBSTITUTE tulemuseks on selline tekstistring:
"JimBrown33SeattleWA"
Hästi vormistatud XML-siltide tagamiseks ja kõigi elementide mähkimiseks vanemelementi, lisame ja lisame veel XML-märgendeid:
""&SUBSTITUTE(B5,",","")&""
Nii saadakse selline tekstistring (loetavuse jaoks lisatakse reavahed)
" Jim Brown 33 Seattle WA "
See tekst edastatakse otse funktsioonile FILTERXML xml-argumendina, Xpath-väljendiga "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath on sõeluv keel ja "// y" valib kõik elemendid. FILTERXML-i tulemus on selline vertikaalne massiiv:
("Jim";"Brown";33;"Seattle";"WA")
Kuna me tahame antud juhul horisontaalset massiivi, keerame funktsiooni TRANSPOSE ümber FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Tulemuseks on selline horisontaalne massiiv:
("Jim","Brown",33,"Seattle","WA")
mis voolab Excel 365 vahemikku D5: H5.
