Formula generica
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Sommario
Per dividere il testo con un delimitatore e trasformare il risultato in un array, è possibile utilizzare la funzione FILTERXML con l'aiuto delle funzioni SOSTITUISCI e TRASPORTA. Nell'esempio mostrato, la formula in D5 è:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Nota: FILTERXML non è disponibile in Excel sul Mac o in Excel Online.
Nota: ho imparato questo trucco da Bill Jelen in un video MrExcel.
Spiegazione
Excel non ha una funzione dedicata alla suddivisione del testo in un array, simile alla funzione di esplosione PHP o al metodo di divisione Python. Come soluzione alternativa, puoi utilizzare la funzione FILTERXML, dopo aver prima aggiunto il markup XML al testo.
Nell'esempio mostrato, abbiamo diverse stringhe di testo delimitate da virgole come questa:
"Jim,Brown,33,Seattle,WA"
L'obiettivo è suddividere le informazioni in colonne separate utilizzando la virgola come delimitatore.
La prima attività consiste nell'aggiungere markup XML a questo testo, in modo che possa essere analizzato come XML con la funzione FILTERXML. Renderemo arbitrariamente ogni campo nel testo un elemento, racchiuso da un elemento genitore. Iniziamo qui con la funzione SOSTITUISCI:
SUBSTITUTE(B5,",","")
Il risultato di SUBSTITUTE è una stringa di testo come questa:
"JimBrown33SeattleWA"
Per garantire tag XML ben formati e per racchiudere tutti gli elementi in un elemento genitore, anteponiamo e accodiamo più tag XML come questo:
""&SUBSTITUTE(B5,",","")&""
Questo produce una stringa di testo come questa (interruzioni di riga aggiunte per leggibilità)
" Jim Brown 33 Seattle WA "
Questo testo viene consegnato direttamente alla funzione FILTERXML come argomento xml, con un'espressione Xpath di "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath è un linguaggio di analisi e "// y" seleziona tutti gli elementi. Il risultato di FILTERXML è un array verticale come questo:
("Jim";"Brown";33;"Seattle";"WA")
Poiché in questo caso vogliamo un array orizzontale, avvolgiamo la funzione TRANSPOSE attorno a FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Il risultato è un array orizzontale come questo:
("Jim","Brown",33,"Seattle","WA")
che si riversa nell'intervallo D5: H5 in Excel 365.
