martes, 2 de febrero de 2021

Alternativas a TEXTSPLIT en Excel

Hoy toca algo más de formulación... con un objetivo claro, replicar el comportamiento de TEXTSPLIT; esto es, conseguir con una fórmula dividir una cadena de texto en una celda que incluya separadores (como ; , - | etc).
Veremos un par de posibilidades... Una mejor que otra ;-)
Alternativas a TEXTSPLIT

Comencemos con la primera fórmula, válida solo cuando los datos están distribuidos uniformemente en la celda, con igual número de caracteres entre el separador.
Muy limitada, sin duda, pero útil y simple si nos sentimos cómodos con las funciones desbordadas.

Vemos unos datos en una celda separados por la barra vertical |, y que además el número de caracteres entre separadores es siempre igual a tres. En B1:B3:
Q-1|Q-2|Q-3|Q-4|
100|200|321|425|
351|589|654|133|
Así pues en D1 insertamos la siguiente fórmula desbordada:
=EXTRAE(B1;
FILTRAR((EXTRAE(B1;SECUENCIA(1;LARGO(B1));1)="|")*(SECUENCIA(1;LARGO(B1)));
(EXTRAE(B1;SECUENCIA(1;LARGO(B1));1)="|")*(SECUENCIA(1;LARGO(B1)))<>0)-3;
3)

Alternativas a TEXTSPLIT

Fórmula basada, y que mejora, una fórmula expuesta en este blog hace años (ver aquí).
La idea fundamental es que se recorre cada caracter de la celda en busca de las posiciones donde se ubiquen los separadores, lo que se obtiene con la parte:
FILTRAR((EXTRAE(B1;SECUENCIA(1;LARGO(B1));1)="|")*(SECUENCIA(1;LARGO(B1)));
(EXTRAE(B1;SECUENCIA(1;LARGO(B1));1)="|")*(SECUENCIA(1;LARGO(B1)))<>0)-3;

Finalmente, identificadas cuantas posiciones existan del separador, con la función EXTRAE recuperamos los tres caracteres siguientes... es decir, los caracteres que responden a los elementos buscados.
Una ventaja de esta fórmula mejorada frente a la del post comentado es que no estamos limitados a un número finito y determinado de elementos...

Como alternativa a esta, y de éxito seguro, y sin limitaciones de ningún tipo.
Para esta fórmula emplearemos la ya conocida función web XMLFILTRO.
Esta fórmula ha sido bastante comentada ya en diferentes foros y grupos de Excel, sin saber exactamente quién fue su creador... pero sin duda de una aplicación real.
Si lees los artículos del blog previos a este, donde se explica la función XMLFILTRO, y algunas otras aplicaciones, entenderás mucho mejor el uso de hoy.

Disponemos de los siguientes datos en B6:B16:
año|pais|uds|ventas|comentario
2020|ES|47|1575
2024|FR|44|1128
2021|IT|26|2069|pendiente
2024|DE|42|1537
2024|ES|16|4866
2024|FR|20|2477
2021|IT|39|1182|riesgo
2022|DE|38|1509
2021|ES|26|3312|ojo!
2025|FR|14|3117|evaluar
como vemos con distinto número de separadores, distinto número de caracteres entre éstos e incluso podría haber diferentes longitudes en cada celda...
En D6 añadimos nuestra fórmula:
=TRANSPONER(XMLFILTRO("<inicio><fila><dato>"&SUSTITUIR(B6;"|";"</dato><dato>")&"</dato></fila></inicio>";"//fila/dato"))
Alternativas a TEXTSPLIT

Como expliqué en entradas previas del blog, lo que se persigue en esta fórmula en concatenar una cadena de texto que imite el código de una tabla en XML.
No se hace necesario emplear la notación real de una tabla (etiquetas table - tr - td), pueden ser inventadas siempre y cuando se embeban en los niveles adecuados, y se abran y cierren correctamente las etiquetas inventadas.

Si nos fijamos con el primer argumento de XMLFILTRO:
"<inicio><fila><dato>"&SUSTITUIR(B6;"|";"</dato><dato>")&"</dato></fila></inicio>"
conseguiríamos el código XML siguiente:
  
      
         añopaisudsventascomentario
      
  

que sería el equivalente a una especie de tabla con tantos elementos como 'dato' hayamos logrado con la fórmula.
El concatenado se consigue con una sencilla SUSTITUCION del separador por las etiquetas adecuadas...
Finalemente es XMLFILTRO la que se encarga de recuperar cada 'dato':
XMLFILTRO(cód_XML;"//fila/dato")
Empleamos la función TRANSPONER porque XMLFILTRO retorna siempre un vector vertical de los elementos... y en este caso nos interesa el vector horizontal, como se veía en la imagen.

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.