Sustituir más de una palabra.

Para el siguiente ejemplo, tenemos una serie de productos.

Debemos de sustituir en cada celda dos palabras, donde en la primera columna tenemos las palabras a sustituir, y, en la segunda columna, tenemos las palabras por las que serán sustituidas.

Por ejemplo, Aspirador debe de ser sustituido por Aspiradora, y, negro por blanca.

Empecemos…

Voy a usar la función DIVIDIRTEXTO para dividir la celda A2, para ello, ponemos la función, como argumento texto es la celda A2.

=DIVIDIRTEXTO(A2

Punto y coma, como argumento delimitador de columna, ponemos el espacio.

Cerramos paréntesis.

=DIVIDIRTEXTO(A2;" ")

Aceptamos y tenemos la celda A2 divida en dos palabras.

Ahora voy a usar la función BUSCARX, donde como argumento valor buscado es la función DIVIDIRTEXTO.

=BUSCARX(DIVIDIRTEXTO(A2;" ")

Punto y coma, como argumento matriz de búsqueda, ponemos desde D2 a D13, y, fijamos para la hora de copiarlo, lo fijamos pulsando F4.

BUSCARX(DIVIDIRTEXTO(A2;" ");DATOS!$D$2:$D$13

Punto y coma, como argumento matriz devuelta, ponemos desde E2 a E13, y, también fijamos.

Cerramos paréntesis.

BUSCARX(DIVIDIRTEXTO(A2;" ");DATOS!$D$2:$D$13;DATOS!$E$2:$E$13)

Aceptamos, y, podemos ver como las dos palabras han sido sustituidas.

Nos queda volver a unir las palabras, para ello, usaremos la función UNIRCADENAS, donde como argumento delimitador, ponemos el espacio.

=UNIRCADENAS(" "

Punto y coma, en la siguiente ventana, ignoramos celdas vacías.

=UNIRCADENAS(" ";VERDADERO

Punto y coma, como argumento texto, es la función BUSCARX.

Cerramos paréntesis.

=UNIRCADENAS(" ";VERDADERO;BUSCARX(DIVIDIRTEXTO(A2;" ");DATOS!$D$2:$D$13;DATOS!$E$2:$E$13))

Aceptamos y ya tenemos la reemplaza y unida separada por un espacio.

Arrastramos y podemos ver que cada palabra ha sido sustituida correctamente.

Pero, que ocurre, si en la tabla tenemos un producto con mas de un espacio, por ejemplo, Barra de sonido 2.0, vemos que nos devuelve un error, porque solo se contempla el primer espacio.

Barra de sonido 2.0, debe de ser sustituido por Home cinema 7.0.

Vamos a ver como arreglarlo.

Debemos de controlar la cantidad de columnas que tiene cada celda, para ello, vamos a usar la función DIVIDIRTEXTO, donde como argumento texto es la celda A2, como argumento delimitador de columna es el espacio, y, cerramos paréntesis.

=DIVIDIRTEXTO(A2;" ")

Ahora, después del signo igual, usamos la función COLUMNAS, para que nos devuelva el número de columnas.

=COLUMNAS(DIVIDIRTEXTO(A2;" "))

Arrastramos, y, tenemos el numero de columnas de cada celda, solo hay una celda que tiene 4 columnas.

Debemos de preguntar cuantas columnas hay en cada celda, si el numero de columnas es igual a 2, debe de ejecutar la expresión para el ejercicio anterior, pero si es mayor a 2, vamos a ver cómo hacerlo.

Lo haremos en una celda aparte, y, vamos a trabajar con la celda A3, que contiene Barra de sonido 2.0, vamos a usar la función SUSTITUIR, para sustituir el espacio por nada.

=SUSTITUIR(A3;" ";"")

Usamos la función LARGO para obtener la longitud de la expresión anterior.

=LARGO(SUSTITUIR(A3;" ";""))

Nos devuelve 16.

Restamos 1.

=LARGO(SUSTITUIR(A3;" ";""))-1

Nos devuelve 15, que son los caracteres para extraer por la izquierda, para ello, usamos la función IZQUIERDA.

=IZQUIERDA(A3;LARGO(SUSTITUIR(A3;" ";""))-1)

Obtenemos Barra de sonido.

Ya tenemos la primera parte del nombre a buscar, ahora, vamos por la segunda parte, en este caso, debemos de extraer por la derecha, pero ¿Cuántos caracteres?

Si al largo de la expresión anterior, sin restar 1, le resto la longitud total de la cadena, sabre los caracteres a extraer por la derecha, la sintaxis seria:

=DERECHA(A3;LARGO(A3)-LARGO(SUSTITUIR(A3;" ";"")))

Vemos que obtenemos 2.0.

Pero, igual que antes, debemos de tener una matriz desbordada en horizontal con dos columnas, donde la primera columna contendrá la primera parte del nombre, y, la segunda columna contendrá la segunda parte, para ello, usaremos la función APILARH (HSTOCK), donde como argumento matriz1, es la función IZQUIERDA, y, como argumento matriz2, es la función DERECHA.

=APILARH(IZQUIERDA(A3;LARGO(SUSTITUIR(A3;" ";""))-1);DERECHA(A3;LARGO(A3)-LARGO(SUSTITUIR(A3;" ";""))))

Ya lo tenemos.

Pues, ya lo tenemos hecho, ahora, igual que antes, usamos BUSCARX para traernos el nombre sustituido.

=BUSCARX(APILARH(IZQUIERDA(A3;LARGO(SUSTITUIR(A3;" ";""))-1);DERECHA(A3;LARGO(A3)-LARGO(SUSTITUIR(A3;" ";""))));$D$2:$D$13;$E$2:$E$13)

Aceptamos, y, vemos el nombre sustituido.

Volvemos a usar UNIRCADENAS, para que quede en una sola celda.

=UNIRCADENAS(" ";VERDADERO;BUSCARX(APILARH(IZQUIERDA(A3;LARGO(SUSTITUIR(A3;" ";""))-1);DERECHA(A3;LARGO(A3)-LARGO(SUSTITUIR(A3;" ";""))));$D$2:$D$13;$E$2:$E$13))

Entonces, como dije anteriormente, debemos de preguntar que si el numero de columnas es igual a 2, debe de usar la expresión del primer ejemplo, en caso contrario, debe de ejecutar la expresión que acabamos de crear, esto haría que la sintaxis sea demasiado larga, por lo que vamos a crear dos nombres de rangos, el primero contendrá la primera expresión, y, el segundo contendrá la segunda expresión.

Esta es la expresión del primer ejercicio:

=UNIRCADENAS(" ";VERDADERO;BUSCARX(DIVIDIRTEXTO(A2;" ");DATOS!$D$2:$D$13;DATOS!$E$2:$E$13))

La seleccionamos, pulsamos CTRL más C para copiar.

Pulsamos Escape.

Nos colocamos en la celda B2.

Vamos a la pestaña de fórmulas, dentro de nombres definidos, hacemos clic en asignar nombre.

Como nombre voy a poner primer_ejecicio, y, en la ventana se refiere a, pegamos la función.

Seleccionamos la segunda función, volvemos a colocarnos en la celda B2, y, creamos otro nombre de rango, y, le voy a llamar segundo_ejercicio.

Ahora preguntamos con el condicional SI que, si el resultado de COLUMNAS es igual a 2, que ejecute el nombre de rango primer_ejercicio, en caso contrario, que ejecute el nombre de rango segundo_ejercicio.

=SI(COLUMNAS(DIVIDIRTEXTO(A2;" "))=2;primer_ejercicio;segundo_ejercicio)

Aceptamos, arrastramos, y, ya lo tenemos.

Miguel Angel Franco Garcia