En los últimos artículos, hemos analizado diferentes formas de eliminar caracteres de cadenas en Excel. Hoy, investigaremos un caso de uso más: cómo eliminar todo antes o después de un carácter determinado.
Eliminar texto usando fórmulas
En Microsoft Excel, muchas manipulaciones de datos realizadas con funciones integradas también se pueden realizar con una fórmula. A diferencia de los métodos anteriores, las fórmulas no cambian los datos originales y le brindan más control sobre los resultados.
Cómo eliminar todo después de cierto carácter
Para eliminar texto después de un carácter específico, la fórmula genérica es:
IZQUIERDA(celúlaBUSCAR(«Chara«, celúla) -1)
Aquí, usamos la función BUSCAR para obtener la posición del carácter y pasarla a la función IZQUIERDA, para que extraiga el número correspondiente de caracteres desde el principio de la cadena. Resta un carácter del número devuelto por SEARCH para excluir el delimitador de los resultados.
Por ejemplo, para eliminar parte de una cadena después de una coma, ingrese la siguiente fórmula en B2 y arrástrela hacia abajo hasta B7:
=LEFT(A2, SEARCH(",", A2) -1)
Cómo quitar todo antes de cierto personaje
Para eliminar parte de una cadena de texto antes de un carácter determinado, la fórmula genérica es:
BIEN(celúlaLARGO(celúla) – BUSCAR(«Chara«, celúla))
Aquí, volvemos a calcular la posición del carácter de destino utilizando BUSCAR, la restamos de la longitud total de la cadena devuelta por LEN y pasamos la diferencia a la función DERECHA para que extraiga esa cantidad de caracteres del final de la cadena.
Por ejemplo, para eliminar el texto antes de la coma, la fórmula es:
=RIGHT(A2, LEN(A2) - SEARCH(",", A2))
En nuestro caso, la coma va seguida de un espacio. Para evitar espacios iniciales en los resultados, incluimos la fórmula básica en la función TRIM:
=TRIM(RIGHT(A2, LEN(A2) - SEARCH(",", A2)))
Notas:
- Ambos ejemplos anteriores asumen que sólo hay solo un caso del delimitador en la cadena original. Si hay varias apariciones, se eliminará el texto antes/después el primer tribunal.
- La función de BÚSQUEDA es no es sensible a mayúsculas y minúsculas, lo que significa que no distingue entre mayúsculas y minúsculas. Si su carácter específico es una letra y desea distinguir entre mayúsculas y minúsculas, utilice distingue mayúsculas y minúsculas función ENCONTRAR en lugar de BUSCAR.
Cómo eliminar texto después de una aparición Na de un carácter
En la situación en la que una cadena de origen contiene varias instancias del delimitador, es posible que deba eliminar el texto después de una instancia en particular. Para ello, utiliza la siguiente fórmula:
IZQUIERDA(celúlaENCONTRAR(«#», SUSTITUIR(celúla«Chara«, «#», norte)) -1)
Dónde norte es la apariencia del carácter después del cual se elimina el texto.
La lógica interna de esta fórmula requiere el uso de un carácter que no está presente en ninguna parte de los datos de origen, un símbolo de almohadilla (#) en nuestro caso. Si este carácter aparece en su conjunto de datos, entonces use algo más en lugar de «#».
Por ejemplo, para eliminar todo después de 2cuando coma en A2 (y la coma misma), la fórmula es:
=LEFT(A2, FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)
Cómo funciona esta fórmula:
La parte clave de la fórmula es la función FIND que calcula la posición del n-ésimo delimitador (coma en nuestro caso). Así es cómo:
Reemplazamos 2cuando coma en A2 con un símbolo hash (o cualquier otro carácter que no exista en sus datos) usando SUSTITUCIÓN:
SUBSTITUTE(A2, ",", "#", 2)
La cadena resultante va a 2cuando el argumento FIND, por lo que encuentra la posición «#» en esa cadena:
FIND("#", "Emma, Design# (102) 123-4568")
FIND nos dice que «#» es el número 13el carácter de la cadena. Para saber el número de caracteres que le preceden, resta 1 y obtendrás 12 como resultado:
FIND("#", SUBSTITUTE(A2, ",", "#", 2)) - 1
Este número va directamente a cual_num el argumento IZQUIERDO pidiéndole que extraiga los primeros 12 caracteres de A2:
=LEFT(A2, 12)
¡Esto es!
Cómo eliminar texto antes de que ocurra el Na de un carácter
La fórmula genérica para eliminar una subcadena antes de cierto carácter es:
DERECHO(REEMPLAZADO(celúla«Chara«, «#», norte), LARGO(celúla) – ENCONTRAR(«#», SUSTITUIR(celúla«Chara«, «#», norte)) -1)
Por ejemplo, para eliminar texto antes de 2cuando coma en A2, la fórmula es:
=RIGHT(SUBSTITUTE(A2, ",", "#", 2), LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)
Para eliminar un espacio inicial, nuevamente usamos la función TRIM como contenedor:
=TRIM(RIGHT(SUBSTITUTE(A2, ",", "#", 2), LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", 2))))
Cómo funciona esta fórmula:
En resumen, encontramos cuántos caracteres hay después del n-ésimo delimitador y extraemos una subcadena de la longitud adecuada desde la derecha. A continuación se desglosa la fórmula:
Primero, reemplazamos 2cuando coma en A2 con un símbolo hash:
SUBSTITUTE(A2, ",", "#", 2)
La cadena resultante va a texto el argumento del DERECHO:
RIGHT("Emma, Design# (102) 123-4568", …
A continuación, debemos definir cuántos caracteres extraer del final de la cadena. Para esto, encontramos la posición del símbolo hash en la cadena de arriba (que es 13):
FIND("#", SUBSTITUTE(A2, ",", "#", 2))
Y restarlo de la longitud total de la cadena (que es igual a 28):
LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", 2))
La diferencia (15) va al segundo argumento de la DERECHA, diciéndole que extraiga los últimos 15 caracteres de la cadena del primer argumento:
RIGHT("Emma, Design# (102) 123-4568", 15)
El resultado es una subcadena «(102) 123-4568», que está muy cerca del resultado deseado excepto por un espacio inicial. Así que usamos la función TRIM para deshacernos de él.
Cómo eliminar texto después de la última aparición de un carácter
Si sus valores están separados por un número variable de delimitadores, es posible que desee eliminar todo después de la última instancia de ese delimitador. Esto se puede hacer con la siguiente fórmula:
IZQUIERDA(celúlaENCONTRAR(«#», SUSTITUIR(celúla«Chara«, «#», LARGO(celúla) – LARGO(REEMPLAZADO(celúla«Chara «, «»)))) -1)
Supongamos que la columna A contiene información diversa sobre los empleados, pero el valor después de la última coma siempre es un número de teléfono. Su objetivo es eliminar los números de teléfono y conservar todos los demás detalles.
Para lograr el objetivo, puede eliminar el texto después de la última coma en A2 con esta fórmula:
=LEFT(A2, FIND("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",","")))) -1)
Copie la fórmula en la columna y obtendrá este resultado:
Cómo funciona esta fórmula:
La esencia de la fórmula es que determinamos la posición del último delimitador (coma) en la cadena y dibujamos una subcadena desde la izquierda hasta el delimitador. Obtener la posición del delimitador es la parte más difícil, y así es como lo manejamos:
Primero, averiguamos cuántas comas hay en la cadena original. Para hacer esto, reemplazamos cada coma con nada («») y servimos la cadena resultante a la función LEN:
LEN(SUBSTITUTE(A2, ",",""))
Para A2, el resultado es 35, que es el número de caracteres en A2 sin comas.
Reste el número anterior de la longitud total de la cadena (38 caracteres):
LEN(A2) - LEN(SUBSTITUTE(A2, ",",""))
… y obtienes 3, que es el número total de comas en A2 (y también el número ordinal de la última coma).
Luego, use la combinación ya familiar de las funciones ENCONTRAR y SUSTITUIR para obtener la posición de la última coma en la cadena. Número de juzgado (3calle coma en nuestro caso) es proporcionado por la fórmula LEN SUBSTITUTE mencionada anteriormente:
FIND("#", SUBSTITUTE(A2, ",", "#", 3))
parece que 3calle el punto decimal es 23calle carácter en A2, lo que significa que necesitamos extraer 22 caracteres que lo preceden. Entonces, ponemos la fórmula anterior menos 1 en cual_num Argumento de LEFT:
LEFT(A2, 23-1)
Cómo eliminar texto antes de la última aparición de un carácter
Para eliminar todo antes de la última instancia de un carácter determinado, la fórmula genérica es:
BIEN(celúlaLARGO(celúla) – ENCONTRAR(«#», SUSTITUIR(celúla«Chara«, «#», LARGO(celúla) – LARGO(REEMPLAZADO(celúla«Chara«, «»))))))
En nuestra tabla de ejemplo, para erradicar el texto antes de la última coma, la fórmula toma la siguiente forma:
=RIGHT(A2, LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",","")))))
Como nota final, lo envolvemos en la función TRIM para eliminar los espacios iniciales:
=TRIM(RIGHT(A2, LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",",""))))))
Cómo funciona esta fórmula:
En resumen, obtenemos la posición de la última coma como se explicó en el ejemplo anterior y la restamos de la longitud total de la cadena:
LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",",""))))
Como resultado, obtenemos el número de caracteres después de la última coma y lo pasamos a la función DERECHA para que busque esa cantidad de caracteres desde el final de la cadena.
Función personalizada para eliminar texto de ambos lados de un carácter
Como ha visto en los ejemplos anteriores, puede resolver casi cualquier caso de uso utilizando las funciones nativas de Excel en varias combinaciones. El problema es que tienes que recordar algunas fórmulas complicadas. Hmm, ¿qué pasa si escribimos nuestra propia función para cubrir todos los escenarios? Es una buena idea. Por lo tanto, agregue el siguiente código VBA a su libro de trabajo (los pasos detallados para insertar VBA en Excel están aquí):
Función QuitarTexto(cadena Como filaencerrado Como filaapariencia Como Enteroes despues Como booleano)
Oscuro número_delimitador, número_de_inicio, largo_delimitador Como Entero
Oscuro str_resultado Como fila
delimiter_num = 0 start_num = 1 str_result = «»
delimitador_len = Largo(delimitador)
Para yo = 1 a ocurrencia delimiter_num = InStr(start_num, str, delimiter, vbTextCompare)
Si 0 < número_delimitador Entonces
núm_inicio = núm_delimitador + longitud_delimitador
El fin Si
Próximo y
Si 0 < número_delimitador Entonces
Si Verdadero = es_después Entonces
str_result = Mid(str, 1, start_num – delimiter_len – 1)
De lo contrario
str_result = Mid(str, start_num)
El fin Si
El fin Si
QuitarTexto = str_result
El fin Función
Nuestra función se llama Quitar texto y tiene la siguiente sintaxis:
RemoveText(cadena, delimitador, ocurrencia, is_after)
Dónde:
fila – es la cadena de texto original. Se puede representar mediante una referencia de celda.
encerrado – el carácter antes/después para eliminar el texto.
apariencia – la instancia del delimitador.
es_después – un valor booleano que indica de qué lado del delimitador debe eliminarse el texto. Puede ser un solo carácter o una secuencia de caracteres.
- VERDADERO: elimina todo lo que está después del delimitador (incluido el propio delimitador).
- FALSO: elimina todo antes del delimitador (incluido el propio delimitador).
Una vez que el código de la función se inserta en el libro de trabajo, puede eliminar las subcadenas de las celdas mediante fórmulas compactas y elegantes.
Por ejemplo, para eliminar todo después de 1Calle coma en A2, la fórmula en B2 es:
=RemoveText(A3, ", ", 1, TRUE)
Para borrar todo antes del 1Calle coma en A2, la fórmula en C2 es:
=RemoveText(A3, ", ", 1, FALSE)
Debido a que nuestra función personalizada admite un cadena delimitadoraponemos una coma y un espacio («,») en 2cuando argumento para ahorrarse la molestia de cortar los espacios iniciales más adelante.
Nuestra función personalizada funciona muy bien, ¿no? Pero si crees que esa es la solución integral, aún no has visto el siguiente ejemplo 🙂
Para probar otras características útiles incluidas en esta herramienta todo en uno, lo animo a descargar una versión de evaluación al final de esta publicación. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
