CONCATENAR/CONCAT/UNIRCADENAS

Vamos a ver las funciones CONCATENAR y CONCAT.

La función CONCATENAR une dos o más cadenas de texto en una sola.

La sintaxis es texto1, que es un argumento obligatorio, puede ser el valor de una celda, de un rango, o de una matriz.

A partir del argumento texto2, son opcionales.

CONCATENAR sigue estando disponible para garantizar la compatibilidad con versiones anteriores.

Debemos de usar punto y coma, para separar elementos de texto, el texto debe de ir entre comillas dobles, pero no los números.

Podemos usar la función CONCATENAR, o, el símbolo de ampersand (&).

Veamos algunos ejemplos de CONCATENAR.

Tenemos los siguientes datos.

Queremos unirlos en usa sola cadena, para ello, en la celda D2, escribo la función, como argumento texto1, seleccionamos la celda A2.

=CONCATENAR (A2

Punto y coma, como argumento texto2, seleccionamos la celda B2.

=CONCATENAR (A2; B2

Punto y coma, como argumento texto3, seleccionamos la celda C2.

Cerramos paréntesis.

=CONCATENAR (A2; B2; C2)

Aceptamos, y, vemos todas las cadenas salen unidas, o, concatenadas.

Podemos ponerle un espacio en blanco entre cada cadena de la siguiente manera:

=CONCATENAR (A2;" "; B2;" "; C2)

También, podemos hacerlo con el signo de ampersand, de la siguiente manera.

=A2&" " &B2&" "&C2

Vemos que la sintaxis es un poco más corta, pero ya es cuestión de gustos.

También, podemos concatenar seleccionando el rango de celdas.

=CONCATENAR (A2:C2)

En este caso, me devuelve una matriz desbordada, una matriz desbordada, es aquella función que devuelve más de un valor, cosa que veremos detalladamente más adelante, pero realmente no me las ha unido, cada valor sigue estando en una celda independiente.

Que ocurre si en vez de CONCATENAR usamos CONCAT.

=CONCAT(A4;" "; B4;" ";C4)

Pues vemos que nos ha unido todas las celdas correctamente, por lo que este problema que presentaba la función CONCATENAR queda resuelto con CONCAT.

Veamos otro ejemplo, tenemos los siguientes datos.

Podemos ver que hay una celda en blanco entre medio, voy a usar CONCATENAR para unir las tres celdas a ver qué ocurre.

=CONCATENAR(A8:C8)

Nos devuelve una matriz desbordada, incluyendo la celda en blanco, cosa que nos vale para poco.

¿Y si usamos CONCAT?

=CONCAT(A10:C10)

Vemos que las celdas son unidas, pero las celdas que están vacías las ignora, por lo que es otro problema que solventa CONCAT.

Tenemos los siguientes números, queremos concatenarlos, y, dejar un delimitador entre cada cadena.

Si lo hacemos con CONCATENAR o CONCAT sería un trabajo laborioso y pesado, pero tenemos una función que se llama UNIRCADENAS, que nos permite unir cadenas e indicarle de una sola vez cual va a ser el delimitador.

Sintaxis

UNIRCADENAS(delimitador, ignorar_vacío, Texto1, [Texto2],...)

· Delimitador, es un argumento obligatorio, es el separador entre cada cadena.

· Ignorar_vacio, es un argumento obligatorio, decidimos si queremos ignorar, o no, las celdas vacías.

· Texto1, argumento obligatorio, texto que se va a combinar, o, rango a combinar.

A partir del argumento texto2, son opcionales, pudiendo haber un máximo de 252 cadenas de texto, puede ser una cadena, una matriz, o, un texto.

Si la cadena resultante supera los 32767 caracteres, que es el límite de la celda, UNIRCADENAS devuelve el error #¡VALOR!.

Ponemos la función, como argumento delimitador, entre comillas dobles ponemos el guion medio (-).

=UNIRCADENAS("-"

Punto y coma, se abre una ventana, donde seleccionamos ignorar celdas vacías.

=UNIRCADENAS("-";VERDADERO

Punto y coma, como argumento texto1, seleccionamos en rango A11:A15.

Cerramos paréntesis.

=UNIRCADENAS("-";VERDADERO;A14:A18)

Aceptamos, y, vemos todas las celdas unidas separadas por el guion medio.

Y si hemos cambiado de idea, y, queremos que el delimitador sea el guion medio excepto el ultimo guion, que sea una barra invertida.

En este caso, hacemos uso de la función SUSTITUIR de la siguiente manera:

=SUSTITUIR (UNIRCADENAS ("-"; VERDADERO; A11:A15);"-";"/";4)

Le estamos diciendo que del resultado de UNIRCADENAS, me cambie el ultimo guion por la barra invertida.

Para el siguiente ejemplo, tenemos una serie de provincias, con unos encabezados que hacen referencia a centros comerciales, y, debajo tenemos una serie de productos vendidos en diferentes provincias.

Tenemos los productos sin repetir, y, ordenados, el ejemplo va a consistir en que en la celda de al lado de cada producto, debe de aparecer el centro o centros donde ha sido vendido dicho producto.

Si nos fijamos en el modelo podemos ver que el producto impresora HP, se ha vendido en el centro comercial Nervión, y, Metromar, pues es lo que queremos conseguir, que al lado del producto impresora HP, aparezca C.C. Nervión, y, Metromar.

Voy a usar el condicional SI para preguntar que si el valor en el rango B2:B16 es igual al producto que hemos seleccionado, que me devuelve el encabezado, en caso contrario, que devuelva un texto en blanco.

=SI(B2:E16=G2;B1:E1;"")

Obtenemos una matriz desbordada con los centros donde hay coincidencia, y, blanco donde no la hay.

Si seleccionamos la función, excepto el signo igual, y, pulsamos F9, podemos ver el centro o centros donde hay coincidencia, y, texto en blanco donde no la hay.

Pero, queremos que al lado del producto nos aparezcan los centros donde se ha vendido dicho producto.

Podemos usar la función CONCAT que ignora los espacios en blanco.

=CONCAT(SI(B2:E16=K2;B1:E1))

Lo tendríamos por el momento.

Fijamos los rangos de búsquedas, y, arrastramos.

=CONCAT(SI($B$2:$E$16=L1;$B$1:$E$1))

Pero, vemos que donde el producto se ha vendido más de una vez aparecen juntos.

Podría poner punto y coma entre comillas como ultimo argumento de CONCAT.

=CONCAT(SI($B$2:$E$16=G2;$B$1:$E$1;"");",")

Vemos que aunque haya vendido en un solo centro, nos aparece el punto y coma al final, cosa que no queremos.

Si somos suscriptor de office 365, tenemos una función llamada UNIRCADENAS, donde solo tenemos que especificar una vez el delimitador, y, lo pondrá en las cadenas que haya para unir, además, le diremos que ignore las celdas que estén vacías.

Además, esta función admite funciones.

Escribimos el signo igual seguido de la función UNIRCADENAS, y, abrimos un paréntesis.

=UNIRCADENAS(

Como delimitador ponemos, por ejemplo, la coma.

=UNIRCADENAS(",";

Ignoramos celdas vacías.

=UNIRCADENAS(",";VERDADERO;

Y ahora ponemos el SI que hemos usado anteriormente.

=UNIRCADENAS(",";VERDADERO;SI($B$2:$E$16=G2;$B$1:$E$1;""))

Aceptamos.

Arrastramos.

Podemos ver los centros separados por coma, y, donde hay un único centro, no aparece la coma.

Pero ¿Qué ocurre si un producto se repite para el mismo centro comercial?

Pues, que aparece dos veces dicho centro, y, debe de aparecer una sola vez.

Supongamos que no disponemos de la función UNICOS, que lo haría de forma rápida, poniendo esta función antes del condicional SI.

=UNIRCADENAS(",";VERDADERO;UNICOS(SI($B$2:$E$16=G2;$B$1:$E$1;"")))

Puedo usar la función ENCONTRAR de forma matricial, donde como primer argumento selecciono los encabezados, y, como segundo argumento es la primera celda donde hemos obtenidos el primer resultado.

Esta función nos va a devolver donde hay la primera coincidencia, por ejemplo, si busca el centro comercial Metromar, nos devolverá 1, porque compara Metromar con Metromar, y, la primera coincidencia es la letra M.

=ENCONTRAR(B1:E1;H2)

Fijo en rango de encabezados y arrastramos.

=ENCONTRAR($B$1:$E$1;H2)

Vemos para el primer producto, aspiradora, que se ha vendido en el centro comercial Nervión, aparece 1, que es la posición del primer carácter, y, después tenemos errores, porque la función ENCONTRAR solo hallo coincidencia con el primer encabezado.

Si nos fijamos donde impresora HP se ha vendido en el mismo centro comercial, además, de en Metromar, vemos la posición 1, que es donde comienza la C de C.C. Nervión, y,27 que es el carácter donde empieza la M de Metromar.

Si seleccionamos la función, excepto, el signo igual, y, pulsamos F9, veremos los resultados que vemos.

Debemos de quedarnos con los números, porque los errores no nos interesan.

Pues, tenemos una función llamada ESERROR, que solo tiene un argumento, que es la celda o celdas para examinar, y, nos va a devolver verdadero si la expresión devuelve un error, y, falso si no lo hace.

Pues englobemos la expresión anterior en esta.

=ESERROR(ENCONTRAR(B1:E1;I2))

Vemos que nos devuelve para el primer encabezado falso, porque no devuelve ningún error, y, para el resto verdadero, porque si lo devuelve.

Lo siguiente es preguntar que si la función ESERROR devuelve verdadero, o sea, es un error, que ponga un texto en blanco, en caso contrario, que me devuelve el encabezado.

=SI(ESERROR(ENCONTRAR(B1:E1;I2));"";B1:E1)

Tenemos una matriz desbordada en horizontal, en este caso, con un centro comercial.

Vamos a fijar los rangos de búsquedas, y, vamos a arrastrar.

=SI(ESERROR(ENCONTRAR($B$1:$E$1;I2));"";$B$1:$E$1)

Tenemos una matriz desbordada, con los centros donde se han vendido productos, pero nos aparece en la posición donde se encuentra el producto.

Pues, volvemos a usar la función UNIRCADENAS, usando como delimitador la coma, e, ignorando las celdas en blanco.

=UNIRCADENAS(",";VERDADERO;SI(ESERROR(ENCONTRAR($B$1:$E$1;I2));"";$B$1:$E$1))

Arrastramos, y, vemos los centros comerciales únicos, aunque un producto se haya vendido más de una vez en el mismo centro.

Pero ¿Qué ocurre?

La función ENCONTRAR está buscando el valor a comparar con la celda H2, y, queremos una sola función.

Pues vamos a seleccionar y copiar la expresión anterior.

Vamos a la expresión donde estamos trabajando, y, dentro de la función ENCONTRAR, borramos H2, y, pegamos la función.

=UNIRCADENAS(",";VERDADERO;SI(ESERROR(ENCONTRAR($B$1:$E$1;UNIRCADENAS(",";VERDADERO;SI($B$2:$E$16=G2;$B$1:$E$1;""))));"";$B$1:$E$1))

Aceptamos.

Arrastramos, y, tenemos los mismos resultados.

En vez de usar la función ESERROR, también podemos usar la función ESNUMERO.

Para ello sustituimos ESERROR por ESNUMERO, y dentro del condicional SI debemos de cambiar el valor verdadero por el valor falso y al contrario.

=UNIRCADENAS(",";VERDADERO;SI(ESNUMERO(ENCONTRAR($B$1:$E$1;UNIRCADENAS(",";VERDADERO;UNICOS(SI($B$2:$E$16=G2;$B$1:$E$1;"")))));$B$1:$E$1;""))

Aceptamos, y, seguimos teniendo los mismos resultados.

En el siguiente ejemplo, tenemos una serie de películas, junto con su género.

Queremos elegir un género, y, que nos muestre las películas que pertenecen a ficho género.

Lo primero que vamos a hacer, es crear una lista desplegable con los géneros, como solo son tres, lo vamos a escribir, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.

Desplegamos permitir, y, seleccionamos lista, en la ventana origen, escribimos los géneros, separados por punto y coma.

Aceptamos.

Voy a usar el condicional SI, para preguntar si el valor del rango B2:B11, es igual al valor de la celda E1, en ese caso, que me devuelva la misma posición, pero del rango A2:A11, en caso, contrario, que devuelva un texto en blanco.

=SI(B2:B11=E1;A2:A11;"")

Obtenemos una matriz desbordada con los nombres de las películas donde hay coincidencia, y, blanco donde no la hay.

Lo siguiente va a ser usar la función UNIRCADENAS, la cual nos va a unir las cadenas que le indiquemos, indicando una sola vez el delimitador, e, ignorando celdas vacías, si queremos.

Lo bueno de UNIRCADENAS, es que en el argumento referencia, podemos usar una función.

=UNIRCADENAS(",";VERDADERO;SI(B2:B11=E1;A2:A11;""))

Y ya lo tenemos.

Miguel Angel Franco Garcia