Formula generică
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
rezumat
Pentru a împărți textul cu un delimitator și a transforma rezultatul într-o matrice, puteți utiliza funcția FILTERXML cu ajutorul funcțiilor SUBSTITUTE și TRANSPOSE. În exemplul prezentat, formula din D5 este:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Notă: FILTERXML nu este disponibil în Excel pe Mac sau în Excel Online.
Notă: Am învățat acest truc de la Bill Jelen într-un videoclip MrExcel.
Explicaţie
Excel nu are o funcție dedicată împărțirii textului într-o matrice, similară cu funcția PHP explode sau metoda Python split. Ca soluție, puteți utiliza funcția FILTERXML, după ce ați adăugat mai întâi marcaj XML la text.
În exemplul prezentat, avem mai multe șiruri de text delimitate prin virgulă ca aceasta:
"Jim,Brown,33,Seattle,WA"
Scopul este de a împărți informațiile în coloane separate folosind virgula ca delimitator.
Prima sarcină este să adăugați markup XML la acest text, astfel încât să poată fi analizat ca XML cu funcția FILTERXML. Vom face în mod arbitrar fiecare câmp din text un element, încadrat cu un element părinte. Începem cu funcția SUBSTITUTE aici:
SUBSTITUTE(B5,",","")
Rezultatul din SUBSTITUTE este un șir de text ca acesta:
"JimBrown33SeattleWA"
Pentru a asigura etichete XML bine formate și pentru a înfășura toate elementele într-un element părinte, adăugăm și adăugăm mai multe etichete XML de acest fel:
""&SUBSTITUTE(B5,",","")&""
Astfel se obține un șir de text ca acesta (se adaugă întreruperi de linie pentru a putea fi citite)
" Jim Brown 33 Seattle WA "
Acest text este livrat direct la funcția FILTERXML ca argument xml, cu o expresie Xpath de „// y”:
FILTERXML("JimBrown33SeattleWA","//y")
Xpath este un limbaj de analiză și „// y” selectează toate elementele. Rezultatul de la FILTERXML este o matrice verticală ca aceasta:
("Jim";"Brown";33;"Seattle";"WA")
Deoarece dorim o matrice orizontală în acest caz, înfășurăm funcția TRANSPOSE în jurul FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Rezultatul este un tablou orizontal ca acesta:
("Jim","Brown",33,"Seattle","WA")
care se varsă în intervalul D5: H5 în Excel 365.
