Fórmula genérica
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Resumen
Para dividir texto con un delimitador y transformar el resultado en una matriz, puede utilizar la función FILTERXML con la ayuda de las funciones SUSTITUIR y TRANSPONER. En el ejemplo que se muestra, la fórmula en D5 es:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Nota: FILTERXML no está disponible en Excel en Mac ni en Excel Online.
Nota: Aprendí este truco de Bill Jelen en un video de MrExcel.
Explicación
Excel no tiene una función dedicada a dividir texto en una matriz, similar a la función de explosión de PHP o al método de división de Python. Como solución alternativa, puede utilizar la función FILTERXML, después de agregar primero el marcado XML al texto.
En el ejemplo que se muestra, tenemos varias cadenas de texto delimitadas por comas como esta:
"Jim,Brown,33,Seattle,WA"
El objetivo es dividir la información en columnas independientes utilizando la coma como delimitador.
La primera tarea es agregar marcado XML a este texto, de modo que se pueda analizar como XML con la función FILTERXML. Vamos a convertir arbitrariamente cada campo del texto en un elemento, encerrado con un elemento padre. Comenzamos con la función SUSTITUIR aquí:
SUBSTITUTE(B5,",","")
El resultado de SUBSTITUTE es una cadena de texto como esta:
"JimBrown33SeattleWA"
Para garantizar etiquetas XML bien formadas y para envolver todos los elementos en un elemento principal, anteponemos y agregamos más etiquetas XML como esta:
""&SUBSTITUTE(B5,",","")&""
Esto produce una cadena de texto como esta (se agregan saltos de línea para facilitar la lectura)
" Jim Brown 33 Seattle WA "
Este texto se envía directamente a la función FILTERXML como argumento xml, con una expresión XPath de "// y":
FILTERXML("JimBrown33SeattleWA","//y")
XPath es un lenguaje de análisis y "// y" selecciona todos los elementos. El resultado de FILTERXML es una matriz vertical como esta:
("Jim";"Brown";33;"Seattle";"WA")
Debido a que queremos una matriz horizontal en esta instancia, envolvemos la función TRANSPOSE alrededor de FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
El resultado es una matriz horizontal como esta:
("Jim","Brown",33,"Seattle","WA")
que se derrama en el rango D5: H5 en Excel 365.
