A veces tenemos que trabajar con cadenas de texto, por ejemplo, debemos de separar una cadena que está separada por un delimitador, como puede ser el espacio en blanco.
Imaginemos, que tenemos la siguiente cadena.
Queremos separar cada nombre en una celda.
Lo podemos hacer con la herramienta texto en columnas, dentro de la pestaña de datos.
Pues, hacemos clic en texto en columnas.
Se abre la ventana, asistente para convertir texto en columnas – paso 1 de 3, hacemos clic en siguiente.
Como delimitador marcamos el espacio en la ventana de separadores, si no estuviera nuestro separador, hacemos clic en otro, y, lo ponemos, podemos ver en la ventana de abajo, como va quedando la separación.
Hacemos clic en siguiente.
Clic en finalizar, y, podemos ver como el texto ha sido separado.
También, lo podemos hacer con otras funciones, aunque no vamos a entrar en ello, porque sería una tarea tediosa, pero si lo tuviéramos que hacer, debemos de usar las funciones ENCONTRAR y EXTRAE.
Desde que aparecieron las matrices dinámicas, Microsoft ya incorporo funciones matriciales, como:
· FILTRAR
· ORDENAR
· ORDENARPOR
· SECUEENCIA
Pues, Microsoft ha lanzado 11 nuevas funciones para manipular matrices dinámicas.
Estas funciones están disponibles para el canal beta de la versión 2203, es probable, que algunos de ustedes no dispongan de ellas, beta, quiere decir que se lanza durante un tiempo para garantizar que funcionan sin problemas, es lo que se conoce como el programa insider.
Pero, para extraer texto, Microsoft ha lanzado dos funciones que devuelven lo antes o lo de después del delimitador.
Estas funciones en ingles son:
· TEXTBEFORE
· TEXTAFTER
Y en español:
· TEXTANTES
· TEXTDESPUES
Además, ha lanzado otra función que nos permite dividir un texto en diversas cadenas, como una matriz desbordada.
Esta función es:
· TEXTSPLIT
En español:
· DIVIDIRTEXTO
Vamos a empezar viendo TEXTBEFORE, es decir, TEXTOANTES.
La sintaxis de esta expresión es:
=TEXTOANTES (texto, delimitador ,[número de ocurrencia], [modo de coincidencia], [coincidencia final], [si no se encuentra])
Todos los argumentos que se encuentran entre corchetes son argumentos opcionales.
· Texto, es un argumento obligatorio, es el texto que buscamos. No se permiten caracteres comodines. Si el argumento texto es una cadena vacía, se devuelve texto vacío.
· Delimitador, es un argumento obligatorio, es el punto antes del que deseamos extraer.
· Numero de ocurrencia, argumento opcional, es el número de delimitador por el que queremos extraer el texto. De forma predeterminada, Numero de ocurrencia es igual a 1. Un número negativo empieza a buscar texto desde el final.
· Modo de coincidencia, argumento opcional, podemos decidir si la búsqueda de texto distingue mayúsculas de minúsculas. El valor predeterminado distingue mayúsculas de minúsculas. Podemos escribir una de las siguientes opciones:
§ Distingue mayúsculas de minúsculas.
§ No distingue mayúsculas de minúsculas.
· Coincidencia final, argumento opcional, trata el final del texto como un delimitador. De forma predeterminada, el texto es una coincidencia exacta. Podemos especificar los siguientes valores:
§ No hace coincidir el delimitador con el final del texto.
§ Hace coincidir el delimitador con el final del texto.
· Si no se encuentra, argumento opcional, es el valor devuelto si no se encuentra ninguna coincidencia. De forma predeterminada, se devuelve #N/A.
Tenemos los siguientes nombres en una celda.
Queremos en una celda, extraer el nombre de Jaime, dicho nombre esta antes del delimitador, por lo que podemos usar la función TEXTBEFORE en inglés, o, TEXTOANTES en español.
En mi caso, la usare en español.
Escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
El primer argumento es texto, que es el texto que buscamos, decir, que no se permiten los caracteres comodines, pues en este caso, es la celda A1.
El siguiente argumento es delimitador, es decir, el texto que separa una cadena de otra, en este caso, es el espacio en blanco.
De momento, vamos a usar solo estos dos argumentos, aceptamos, y, vemos como ha extraído Jaime, que es el nombre antes del delimitador.
Ahora, tenemos en una celda tres nombres.
Queremos extraer los nombres que están antes del segundo delimitador.
Pues, bien, como argumento texto señalamos la celda donde está el texto buscado, como argumento delimitador, ponemos el espacio en blanco, y, tiene un tercer argumento, que es número de instancia, que quiere decir a partir de que posición de delimitador he de empezar a extraer, en este caso, a partir del segundo espacio en blanco.
=TEXTOANTES (A2;" ";2)
Aceptamos, y, vemos que nos ha extraído los dos primeros nombres.
Decir que cuando número de ocurrencia es cero, es decir, estamos aplicando la función a una cadena que no contiene delimitador nos devuelve el error VALOR.
Tiene un cuarto argumento, donde podemos decidir si queremos distinguir entre mayúsculas y minúsculas, o, no.
Hemos visto que en el argumento número de ocurrencia, ponemos un número, que es a partir del delimitador que queremos separar, pero también, podemos poner un nombre.
Siguiendo con el ejemplo anterior.
En una celda, tengo el nombre de Claudia.
Bien, pues esa celda con Claudia va a ser el delimitador.
=TEXTOANTES(A5;A6)
Aceptamos, y, vemos que nos devuelve el nombre que esta antes de claudia, que es Jaime.
Y si como delimitador pongo Miguel, pues me devuelve los nombres anteriores a Miguel, que son Jaime y claudia.
Veamos la función TEXTAFTER, o, TEXTODESPUES.
Esta función, al contrario de la anterior, nos extrae el texto después del delimitador.
Siguiendo con el mismo ejemplo.
Si escribimos:
=TEXTODESPUES (A5;" ")
Nos devuelve claudia y miguel, que son los nombres después del primer delimitador.
Si utilizamos el argumento número de instancia, y, ponemos 2, nos devuelve el ultimo nombre.
=TEXTODESPUES (A5;" ";2)
Igual que antes, podemos usar un nombre como delimitador.
Veamos ahora la función TEXTTSPLIT o DIVIDIRTEXTO.
Esta función nos va a dividir un texto separado por un delimitador en cadenas, y, nos devuelve una matriz desbordada.
Sintaxis
=DIVIDIRTEXTO(texto, delimitador_columna,[delimitador_fila],[ignorar_vacias], [modo de coincidencia], [pad_with])
· Texto, argumento obligatorio, es el texto que deseamos dividir.
· Delimitador_columna, argumento obligatorio, es el texto que marca el punto donde distribuir el texto entre las columnas.
· Delimitador_fila, argumento opcional, es el texto que marca el punto donde derramar el texto en las filas.
· Ignorar_vacias, argumento opcional, especificamos TRUE para ignorar los delimitadores consecutivos. El valor predeterminado es FALSO, lo que crea una celda vacía.
· Modo de coincidencia, argumento opcional, especificamos 1 para realizar una coincidencia que no distinga entre mayúsculas y minúsculas. El valor predeterminado es 0, que distingue entre mayúsculas y minúsculas.
· Pad_with, argumento opcional, es el valor con el que se rellena el resultado si no hay coincidencias. El valor predeterminado es #N/A.
Si tenemos mas de un delimitador, debemos de utilizar una constante de matriz.
Por ejemplo, estando con el modelo de ventas de productos en diferentes centros comerciales, y, provincias, vamos a dividir cada centro comercial, podemos ver que el delimitador para centro es el espacio en blanco.
Bien, pues ponemos, el signo igual seguido de la función y abrimos un paréntesis.
=DIVIDIRTEXTO (
El primer argumento es texto, que es el texto que queremos dividir, pues señalamos la celda.
=DIVIDIRTEXTO (C2
Punto y coma, el siguiente es delimitador de columna, que es donde se va a derramar el texto entre columnas, pues ponemos el espacio en blanco, este argumento es opcional.
=DIVIDIRTEXTO (C2;" ")
Aceptamos.
Arrastramos hacia abajo, y, vemos como tenemos una matriz derramada habiendo sido la cadena separada.
Como hemos usado el argumento delimitador de columna, el derrame se ha producido por columna.
Pues bien, vamos a omitir el argumento delimitador de columna, y, vamos a usar el argumento delimitador de fila, y, vemos que, en este caso, el derrame se produce hacia abajo, es decir, en filas.
=DIVIDIRTEXTO (C2;" ")
Pero, que ocurre si tenemos más de un delimitador.
Vemos que tenemos cuatro nombres, cada uno de ellos, separado por un delimitador diferente.
Con la siguiente expresión, donde en el argumento delimitador de columna usamos el espacio en blanco.
=DIVIDIRTEXTO (H7;" ")
Solo me divide Jaime, que es el único nombre que coincide con el delimitador.
Pero, queremos dividir el resto de los valores.
Pues en este caso, debemos de usar una constante de matriz, para crear dicha constante, abrimos unas llaves, entre comillas, ponemos los diferentes delimitadores, y, cerramos las llaves.
=DIVIDIRTEXTO (H7;{" ";"_";"-"})
Vemos como ahora quedan separadas todas las cadenas.
Tiene un cuarto argumento donde podemos decidir si ignoramos celdas vacías o no.
Veamos otro ejemplo, trabajamos con el modelo habitual, pero algo más corto, queremos obtener un reporte, o, informe de aquellos centros que contienen la palabra centro.
El modelo está en formato de tabla, y, se llama ventas.
Podemos usar la función DIVIDIRTEXTO, el problema es que cuando la usamos de forma matricial sobre una columna, solo nos separa una parte, por lo que no nos valdría.
Vamos a usar la función TEXTODESPUES, donde como argumento texto, seleccionamos la columna centro.
=TEXTODESPUES(Ventas[Centro]
Punto y coma, como argumento delimitador, ponemos el espacio en blanco.
Cerramos paréntesis.
=TEXTODESPUES(Ventas[Centro];" ")
Aceptamos, y, tenemos una matriz desbordada en vertical con error donde no hay delimitador, y, la cadena que corresponde después del delimitador.
Nosotros buscamos centro, pero vemos que la única cadena que contiene centro está acompañada de la palabra inglés, por lo que nos debemos de quedar solo con centro, vamos a usar la función TEXTOANTES, que tiene los mismos argumentos que TEXTODESPUES.
=TEXTOANTES(TEXTODESPUES(Ventas[Centro];" ");" ")
Volvemos a tener una matriz desbordada en vertical, igual que antes, con error donde no hay delimitador, y, con la palabra centro.
Lo siguiente es preguntar si no es error el resultado de la matriz desbordada obtenida anteriormente, para ello, usamos la función NO junto con la función ESERROR.
=NO(ESERROR(TEXTOANTES(TEXTODESPUES(Ventas[Centro];" ");" ")))
Ahora vemos FALSO donde no es error, y, VERDADERO donde no lo es.
Preguntamos que, si el resultado de la expresión anterior es igual a corte, entre comillas dobles, en ese caso, que nos devuelva la fila, por ejemplo, de la columna centro, en caso contrario, que devuelva un texto en blanco.
=SI(NO(ESERROR(TEXTOANTES(TEXTODESPUES(Ventas[Centro];" ");" ")="corte"));FILA(Ventas[Centro]);"")
Obtenemos blanco donde es error, y, donde no es error, obtenemos el número de fila.
Vamos a deshacernos de los blancos, donde la expresión anterior debemos de usarla más de una vez, por lo que después del signo igual usamos LET, creamos una variable, y, almacenamos la expresión anterior.
=LET(a;SI(NO(ESERROR(TEXTOANTES(TEXTODESPUES(Ventas[Centro];" ");" ")="corte"));FILA(Ventas[Centro]);"")
Punto y coma, como argumento calculo, filtramos la variable A, siempre que dicha variable, sea diferente a blanco.
Cerramos paréntesis.
;LET(a;SI(NO(ESERROR(TEXTOANTES(TEXTODESPUES(Ventas[Centro];" ");" ")="corte"));FILA(Ventas[Centro]);"");FILTRAR(a;a<>""))
Aceptamos, y, ya tenemos los números de fila a rescatar de cada columna.
Debemos de usar la función INDICE, pero de forma matricial, el argumento número de fila ya lo tenemos que es la expresión anterior, el argumento número de columna, también debe de ser matricial, sabemos que tenemos 7 columnas, por lo que podemos usar SECUENCIA, usar el argumento columnas, y, poner el valor 7, pero, y si los encabezados cambian, bien a más, o, bien a menos, entonces, poner el valor 7 no es válido, entonces, podemos usar SECUENCIA, omitir el argumento filas, como argumento columnas, usamos CONTARA, y, contamos los encabezados, de la siguiente manera:
=CONTARA(Ventas[#Encabezados])
Nos devuelve 7, pero ya es dinámico, pues dicha función es el argumento número de columna de la función INDICE, que quedaría como sigue:
=INDICE(A:G;LET(a;SI(NO(ESERROR(TEXTOANTES(TEXTODESPUES(Ventas[Centro];" ");" ")="corte"));FILA(Ventas[Centro]);"");FILTRAR(a;a<>""));SECUENCIA(;CONTARA(Ventas[#Encabezados])))
Aceptamos, y, ya tenemos nuestro reporte con aquellos centros comerciales que contienen la palabra centro.
Ahora, tenemos el mismo modelo, donde tenemos una columna donde tenemos la cantidad, y, el precio separado por coma, y, no tenemos la columna de total.
Debemos de calcular el total, sin modificar el modelo.
Este modelo no está en formato de tabla.
Usamos TEXTOANTES para traernos la primera cantidad, como argumento texto, seleccionamos desde E2 a E21.
=TEXTOANTES(Ejemplo3!E2:E21
Punto y coma, como argumento delimitador, ponemos coma, y, cerramos paréntesis.
=TEXTOANTES(Ejemplo3!E2:E21;",")
Ponemos el símbolo de asterisco.
=TEXTOANTES(Ejemplo3!E2:E21;",")*
Usamos TEXTODESPUES para traernos la segunda cantidad, donde como argumento texto, sigue siendo el rango E2:E21.
=TEXTOANTES(Ejemplo3!E2:E21;",")*TEXTODESPUES(Ejemplo3!E2:E21
Punto y coma, como argumento delimitador, entre comillas dobles, ponemos coma.
Cerramos paréntesis.
=TEXTOANTES(Ejemplo3!E2:E21;",")*TEXTODESPUES(Ejemplo3!E2:E21;",")
Aceptamos, y, ya tenemos el total.
Para el siguiente ejemplo, tenemos las dos siguientes cadenas.
Ambas cadenas no tienen el mismo orden, el orden de la primera cadena es provincia, producto, centro, y, total, y, de la segunda cadena es producto, total, provincia, y, centro.
Debemos de calcular el total.
Lo primer que vamos a realizar es separar cada cadena, para ello, usamos DIVIDIRTEXTO, donde como argumento texto, seleccionamos la celda B2, donde tenemos la primera cadena.
=DIVIDIRTEXTO(B2
Punto y coma, como argumento delimitador de columnas, entre comillas dobles, ponemos coma.
Cerramos paréntesis.
=DIVIDIRTEXTO(B2;",")
Arrastramos una vez, y, ya tenemos las cadenas separadas.
Si nos fijamos en las cadenas separadas, todas están en formato de texto, vemos que quedan alineadas a la izquierda, de esta manera, no podemos encontrar la cantidad que corresponde al total, pero tenemos una función llamada ABS, que nos devuelve el valor absoluto de un valor, es decir, de un número, pues, después del signo igual ponemos dicha función.
=ABS(DIVIDIRTEXTO(B2;","))
Arrastramos una vez, y, vemos que ya aparece el número alineado a la derecha, y, error donde no es número.
Ahora, debemos de preguntar, si es numero la matriz desbordada obtenida, que nos devuelva dicho número, en caso contrario, que devuelva un texto en blanco.
Igual que antes, la expresión anterior la vamos a usar mas de una vez, por lo que, de nuevo, después del signo igual, ponemos LET, creamos una variable, y, almacenamos la expresión anterior.
=LET(a;ABS(DIVIDIRTEXTO(B2;","))
Punto y coma, como argumento calculo, preguntamos que, si la variable A es número, en ese caso, que devuelva la variable A, en caso contrario, que devuelva un texto en blanco.
Cerramos paréntesis.
=LET(a;ABS(DIVIDIRTEXTO(B2;","));SI(ESNUMERO(a);a;""))
Aceptamos, y, tenemos una matriz desbordada en horizontal, con blanco donde no es número, y, donde lo es, pues, tenemos dicho número.
Vamos a quedarnos solo con el valor, para ello, podemos usar CONCAT.
=CONCAT(LET(a;ABS(DIVIDIRTEXTO(B2;","));SI(ESNUMERO(a);a;"")))
Aceptamos, arrastramos una vez, y, ya lo tenemos.
Para el siguiente ejemplo, tenemos los siguientes nombres:
Vemos que tenemos dos nombres compuestos, uno es Miguel Angel, y, otro es, Jaime Franco, en los dos nombres, el delimitador es el espacio, y, tenemos otro nombre más que está formado por un solo nombre, que es Claudia.
Vamos a usar la función TEXTOANTES, para que nos devuelva el nombre que esta antes del delimitador, para ello, en una celda, escribimos le signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=TEXTOANTES(
Señalamos la celda donde tenemos el nombre a separar.
=TEXTOANTES(H2
Punto y coma, y, entre comillas dobles, ponemos el delimitador.
Cerramos paréntesis, y, aceptamos.
=TEXTOANTES(H2;" ")
Vemos que nos devuelve el primer nombre, cosa que funciona bien, mientras solo haya un delimitador.
Vamos a arrastrar, y, nos devuelve un error, en el nombre de Claudia, porque no hay ningún delimitador.
Una forma de arreglarlo es después de la referencia, poner el símbolo de concatenar, abrir unas comillas dobles, poner un espacio, y, cerrar las comillas dobles, es decir, poner dos veces el delimitador, de la siguiente manera.
=TEXTOANTES(H2&" ";" ")
Vemos como nos aparece el nombre de Claudia.
Si nos fijamos en la sintaxis, vemos que tenemos algunos argumentos más, entre ellos, esta coincidencia final, de forma predeterminada, el valor es cero, este argumento trata el ultimo carácter como delimitador, pero, si lo ponemos en 1, y, aceptamos.
Y volvemos a arrastrar, vemos que tenemos los mismos resultados.
También, podemos usar el argumento si no se encuentra, pues en este caso, voy a decirle que, si no se encuentra, me devuelva la celda H3, es decir, el nombre.
=TEXTOANTES(H3;" ";;;;H3)
Seguimos teniendo los mismos resultados.
El siguiente ejemplo, va a consistir en que tenemos un modelo de venta de productos en diferentes centros comerciales y provincias, pero, la provincia y el centro comercial se encuentran unidos, y, separados por punto y coma.
Nos dicen que calculemos los totales para cada centro comercial en la provincia que seleccionemos, pero que no añadamos columnas nuevas con la separación de provincia y centro comercial, debemos de calcularlo tal cual está el modelo.
El modelo está en formato de tabla, y, se llama Datos2.
Estos cálculos lo vamos a hacer en una hoja nueva.
Lo primero va a hacer crear una lista única de provincias, para ello, debemos de separar la provincia del centro, que lo haremos con la función TEXTOANTES.
=TEXTOANTES(Datos[Provinica/Centro comercial];";")
Con la función UNICOS quitamos duplicados.
=UNICOS (TEXTOANTES (Datos [Provinica/Centro comercial];";"))
Ordenamos, con los argumentos predeterminados.
=ORDENAR (UNICOS (TEXTOANTES (Datos [Provinica/Centro comercial];";")))
Lo siguiente va a ser crear una lista desplegable, a través, de una validación de datos con las provincias, para ello, vamos a la pestaña datos, dentro del grupo nombres definidos, hacemos clic en validación de datos.
Desplegamos permitir, y, elegimos lista, en la ventana origen, seleccionamos la celda J2 seguida del operador de rango derramado (#).
Aceptamos, seleccionamos una provincia.
Lo siguiente es crear otra lista desplegable de centros comerciales únicos, donde dichos centros han vendido en la provincia seleccionada, por lo que primero, vamos a filtrar el modelo por la provincia, y, que nos devuelva los centros comerciales, en este caso, debemos de usar la función TEXTOANTES, y, TEXTODESPUES.
Escribimos el signo igual seguido de la función FILTRAR, y, abrimos un paréntesis.
=FILTRAR (
Como argumento array, debemos de quedarnos con los centros de la columna Provincia/Centro comercial, para ello, usamos TEXTODESPUES, como argumento texto, es la columna Provincia/Centro comercial , y, como argumento delimitador, entre comillas dobles, ponemos punto y coma.
=FILTRAR(TEXTODESPUES(Datos[Provinica/Centro comercial];";")
Como argumento include, con la función TEXTOANTES, extraemos la provincia y la igualamos a la provincia seleccionada.
=FILTRAR(TEXTODESPUES(Datos[Provinica/Centro comercial];";");TEXTOANTES(Datos[Provinica/Centro comercial];";")=B1)
Aceptamos, y, tenemos los centros comerciales, repetidos, donde dicha provincia ha vendido.
Pero, debemos de quedarnos con los valores únicos, pues después del signo igual, ponemos la función UNICOS.
=UNICOS(FILTRAR(TEXTODESPUES(Datos[Provinica/Centro comercial];";");TEXTOANTES(Datos[Provinica/Centro comercial];";")=B1))
Ahora, tenemos que calcular los totales para cada centro de la provincia seleccionada.
Si usamos la función SUMAR.SI.CONJUNTO nos devuelve error, será porque no reconoce estas funciones nuevas, ya que se basan en matrices dinámicas.
Lo vamos a hacer con el condicional SI, pero anidado, ya que tenemos dos condiciones.
Lo primero que debemos tenemos que preguntar es si el valor antes del delimitador es igual a la provincia seleccionada, por lo que debemos de usar la función TEXTOAANTES, este va a ser el argumento prueba lógica del condicional SI.
=SI (TEXTOANTES (Datos [Provinica/Centro comercial];";”) =Hoja2! B1
Punto y coma, y, si se cumple la condición, vamos a preguntar si el centro comercial es igual al primer centro de la lista, en este caso, debemos de usar la función TEXTODESPUES.
=SI (TEXTOANTES (Datos [Provinica/Centro comercial];";”) =Hoja2! B1; SI (TEXTODESPUES (Datos [Provinica/Centro comercial]=Hoja2! A5
Si lo es, que nos devuelva la columna de total, en caso contrario, que ponga un texto en blanco.
=SI(TEXTOANTES(Datos[Provinica/Centro comercial];";")=Hoja2!B1;SI(TEXTODESPUES(Datos[Provinica/Centro comercial];";")=Hoja2!A5;Datos[Total];"");"")
Tenemos una matriz desbordada con los totales para el primer centro y la provincia seleccionada.
Pero, como lo que queremos es el total, después del signo igual, usamos la función SUMA.
=SUMA(SI(TEXTOANTES(Datos[Provinica/Centro comercial];";")=Hoja2!B1;SI(TEXTODESPUES(Datos[Provinica/Centro comercial];";")=Hoja2!A5;Datos[Total];"");""))
Fijamos B1 y arrastramos.
Ya lo tenemos.
Miguel Angel Franco
Comments