En este artículo, aprenderá cómo eliminar caracteres específicos de una cadena de texto y eliminar caracteres no deseados de varias celdas a la vez.
Al importar datos a Excel desde otro lugar, una gran cantidad de caracteres especiales pueden viajar a sus hojas de trabajo. Lo que es aún más frustrante es que algunos caracteres son invisibles, lo que produce espacios en blanco adicionales antes, después o dentro de las cadenas de texto. Este tutorial proporciona soluciones para todos estos problemas, ahorrándole la molestia de tener que revisar los datos celda por celda y eliminar manualmente los caracteres no deseados.
Eliminar carácter especial de la celda de Excel
Para eliminar un carácter específico de una celda, reemplácelo con una cadena vacía usando la función SUSTITUIR en su forma más simple:
SUSTITUIR (celda, carácter, «»)
Por ejemplo, para erradicar un signo de interrogación de A2, la fórmula en B2 es:
=SUBSTITUTE(A2, "?", "")
Para eliminar un carácter que no está presente en su teclado, puede copiarlo/pegarlo en la fórmula desde la celda original.
Por ejemplo, así es como puedes deshacerte de un signo de interrogación invertido:
=SUBSTITUTE(A2, "¿", "")
Pero si un personaje no deseado es invisible o no copia correctamente, ¿cómo lo pones en la fórmula? Simplemente, encuentre su número de código usando la función CÓDIGO.
En nuestro caso, el carácter no deseado («¿») ocupa el último lugar en la celda A2, por lo que estamos usando una combinación de las funciones CÓDIGO y DERECHA para recuperar su valor de código único, que es 191:
=CODE(RIGHT(A2))
Una vez que obtenga el código del personaje, sirva la función CHAR correspondiente a la fórmula genérica anterior. Para nuestro conjunto de datos, la fórmula es la siguiente:
=SUBSTITUTE(A2, CHAR(191),"")
Nota. La función SUSTITUIR es distingue mayúsculas y minúsculas, lo que significa que trata las letras mayúsculas y minúsculas como caracteres diferentes. Tenga esto en cuenta si su carácter no deseado es una letra.
Eliminar varios caracteres de la cadena
En una situación en la que hay dos o más caracteres no deseados en una celda, puede anidar varias funciones SUSTITUIR una en otra para eliminarlas todas de una sola vez:
SUSTITUIR(SUSTITUIR(SUSTITUIR(celda, char1, «»), char2, «»), char3, «»)
Por ejemplo, para erradicar los signos de exclamación e interrogación normales, así como los invertidos, de una cadena de texto en A2, use esta fórmula:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "!", ""), "¡", ""), "?", ""), "¿", "")
Lo mismo se puede hacer con la ayuda de la función CHAR, donde 161 es el código de carácter para «» y 191 es el código de carácter para «¿»:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3, "!", ""), "?", ""), CHAR(161), ""), CHAR(191), "")
Las funciones de SUSTITUCIÓN anidadas funcionan bien para una cantidad razonable de caracteres, pero si tiene docenas de caracteres para eliminar, la fórmula se vuelve demasiado larga y difícil de administrar. El siguiente ejemplo demuestra una solución más compacta y elegante.
Eliminar todos los caracteres no deseados a la vez
La solución solo funciona en Excel para Microsoft 365
Como probablemente sepa, Excel 365 tiene una función especial que le permite crear sus propias funciones, incluidas aquellas que calculan recursivamente. Esta nueva función se llama LAMBDA, y puede encontrar todos los detalles al respecto en el tutorial vinculado anteriormente. A continuación, ilustraré el concepto con un par de ejemplos prácticos.
Una función LAMBDA personalizada para eliminar caracteres no deseados es como sigue:
=LAMBDA(string, chars, IF(chars<>"", RemoveChars(SUBSTITUTE(string, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), string))
Para poder usar esta función en sus hojas de trabajo, primero debe nombrarla. Para esto, presione Ctrl + 3 para abrir el Administrador de nombres y luego defina un Nuevo nombre de esta manera:
- En el cuadro Nombre, ingrese el nombre de la función: RemoveChars.
- Establezca el alcance en Libro de trabajo.
- En el cuadro Se refiere a, pegue la fórmula anterior.
- Opcionalmente, ingrese la descripción de los parámetros en el cuadro Comentarios. Los parámetros se mostrarán cuando escriba una fórmula en una celda.
- Haga clic en Aceptar para guardar su nueva función.
Para obtener instrucciones detalladas, consulte Cómo nombrar una función LAMBDA personalizada.
Una vez que la función recibe un nombre, puede referirse a ella como cualquier fórmula nativa.
Desde el punto de vista del usuario, la sintaxis de nuestra función personalizada es tan simple como esto:
RemoveChars(cadena, caracteres)
Dónde:
- Cadena: es la cadena original o una referencia a la celda/rango que contiene la(s) cadena(s).
- Chars – caracteres para eliminar. Se puede representar mediante una cadena de texto o una referencia de celda.
Por conveniencia, ingresamos caracteres no deseados en alguna celda, digamos D2. Para eliminar esos caracteres de A2, la fórmula es:
=RemoveChars(A2, $D$2)
Para que la fórmula funcione correctamente, tenga en cuenta lo siguiente:
- En D2, los caracteres se enumeran sin espacios, a menos que desee eliminar también los espacios.
- La dirección de la celda que contiene los caracteres especiales está bloqueada con el signo $ ($D$2) para evitar que la referencia cambie al copiar la fórmula en las celdas siguientes.
Y luego, simplemente arrastramos la fórmula hacia abajo y eliminamos todos los caracteres enumerados en D2 de las celdas A2 a A6:
Para limpiar varias celdas con una sola fórmula, proporcione el rango A2:A6 para el primer argumento:
=RemoveChars(A2:A6, D2)
Dado que la fórmula se ingresa solo en la celda superior, no debe preocuparse por bloquear las coordenadas de la celda: una referencia relativa (D2) funciona bien en este caso. Y debido a la compatibilidad con matrices dinámicas, la fórmula se derrama automáticamente en todas las celdas a las que se hace referencia:
Eliminación de un juego de caracteres predefinido
Para eliminar un conjunto predefinido de caracteres de varias celdas, puede crear otro LAMBDA que llame a la función principal RemoveChars y especifique los caracteres no deseados en el segundo parámetro. Por ejemplo:
Borrar caracteres especialeshemos creado una función personalizada llamada RemoveSpecialChars:
=LAMBDA(string, RemoveChars(string, "?¿!¡*%#@^"))
A eliminar números a partir de cadenas de texto, hemos creado una función más llamada RemoveNumbers:
=LAMBDA(string, RemoveChars(string, "0123456789"))
Las dos funciones anteriores son súper fáciles de usar, ya que solo requieren un argumento: la cadena original.
Para eliminar caracteres especiales de A2, la fórmula es:
=RemoveSpecialChars(A2)
Para borrar solo caracteres numéricos:
=RemoveNumbers(A2)
Cómo funciona esta función:
En esencia, la función RemoveChars recorre la lista de caracteres y elimina un carácter a la vez. Antes de cada llamada recursiva, la función IF verifica los caracteres restantes. Si la cadena de caracteres no está vacía (chars<>«»), la función se llama a sí misma. Tan pronto como se haya procesado el último carácter, la fórmula devuelve la cadena en su forma actual y sale.
Para ver el desglose detallado de la fórmula, consulte LAMBDA recursiva para eliminar caracteres no deseados.
Eliminar caracteres especiales con VBA
Las funciones funcionan en todas las versiones de Excel.
Si la función LAMBDA no está disponible en su Excel, nada le impide crear una función similar con VBA. Una función definida por el usuario (UDF) se puede escribir de dos maneras.
Función personalizada para eliminar caracteres especiales recursivo:
Este código emula la lógica de la función LAMBDA discutida anteriormente.
Function RemoveUnwantedChars(str As String, chars As String)
If ("" <> chars) Then
str = Replace(str, Left(chars, 1), "")
chars = Right(chars, Len(chars) - 1)
RemoveUnwantedChars = RemoveUnwantedChars(str, chars)
Else
RemoveUnwantedChars = str
End If
End Function
Función personalizada para eliminar caracteres especiales no recursivo:
Aquí, recorremos los caracteres no deseados de 1 a Len(chars) y reemplazamos los que se encuentran en la cadena original con nada. La función MID extrae los caracteres no deseados uno por uno y los pasa a la función Reemplazar.
Function RemoveUnwantedChars(str As String, chars As String) For index = 1 To Len(chars) str = Replace(str, Mid(chars, index, 1), "") Next RemoveUnwantedChars = str End Function
Inserte uno de los códigos anteriores en su libro de trabajo como se explica en Cómo insertar código VBA en Excel, y su función personalizada estará lista para usar.
Para no confundir nuestra nueva función definida por el usuario con la función definida por Lambda, le hemos dado un nombre diferente:
RemoveUnwantedChars(cadena, caracteres)
Asumiendo que la cadena original está en A2 y los caracteres no deseados en D2, podemos deshacernos de ellos usando esta fórmula:
= RemoveUnwantedChars(A2, $D$2)
Función personalizada con caracteres codificados
Si no desea preocuparse por proporcionar caracteres especiales para cada fórmula, puede especificarlos directamente en el código:
Function RemoveSpecialChars(str As String) As String
Dim chars As String
Dim index As Long
chars = "?¿!¡*%#$(){}[]^&/\~+-"
For index = 1 To Len(chars)
str = Replace(str, Mid(chars, index, 1), "")
Next
RemoveSpecialChars = str
End Function
Tenga en cuenta que el código anterior es para fines de demostración. Para uso práctico, asegúrese de incluir todos los caracteres que desea eliminar en la siguiente línea:
chars = "?¿!¡*%#$(){}[]^&/\~+-"
Esta función personalizada se llama RemoveSpecialChars y solo requiere un argumento: la cadena original:
RemoveSpecialChars(cadena)
Para eliminar los caracteres especiales de nuestro conjunto de datos, la fórmula es:
=RemoveSpecialChars(A2)
Eliminar caracteres no imprimibles en Excel
Microsoft Excel tiene una función especial para eliminar caracteres no imprimibles: la función LIMPIAR. Técnicamente, elimina los primeros 32 caracteres del conjunto ASCII de 7 bits (códigos del 0 al 31).
Por ejemplo, para eliminar caracteres no imprimibles de A2, aquí está la fórmula a usar:
=CLEAN(A2)
Esto eliminará los caracteres no imprimibles, pero permanecerán los espacios antes/después del texto y entre las palabras.
para deshacerse de espacios adicionalesenvuelva la fórmula CLEAN en la función TRIM:
=TRIM(CLEAN(A2))
Ahora, todos los espacios iniciales y finales se eliminan, mientras que los espacios intermedios se reducen a un solo carácter de espacio:
Si desea eliminar absolutamente todos los espacios dentro de una cadena, luego sustituya adicionalmente el carácter de espacio (número de código 32) con una cadena vacía:
=TRIM(CLEAN((SUBSTITUTE(A2, CHAR(32), ""))))
¿Todavía quedan algunos espacios u otros caracteres invisibles en su hoja de trabajo? Eso significa que esos caracteres tienen valores diferentes en el juego de caracteres Unicode.
Por ejemplo, el código de carácter de un espacio irrompible ( ) es 160 y puede purgarlo usando esta fórmula:
=SUBSTITUTE(A2, CHAR(160)," ")
para borrar un carácter no imprimible específico, primero debe encontrar su valor de código. Las instrucciones detalladas y los ejemplos de fórmulas están aquí: Cómo eliminar un carácter no imprimible específico.
Eliminar caracteres especiales con Ultimate Suite
Admite Excel para Microsoft 365, Excel 2019 – 2010
En este último ejemplo, déjame mostrarte la forma más fácil de eliminar caracteres especiales en Excel. Con Ultimate Suite instalado, esto es lo que debe hacer:
- En la pestaña Datos de Ablebits, en el grupo Texto, haga clic en Eliminar > Eliminar personajes.
- En el panel del complemento, elija el rango de origen, seleccione Eliminar conjuntos de caracteres y elija la opción deseada de la lista desplegable (Símbolos y signos de puntuación en este ejemplo).
- Presiona el botón Quitar.
En un momento, obtendrás un resultado perfecto:
Si algo sale mal, no se preocupe: se creará automáticamente una copia de seguridad de su hoja de trabajo, ya que la casilla Hacer una copia de seguridad de esta hoja de trabajo está seleccionada de manera predeterminada.
¿Tiene curiosidad por probar nuestra herramienta Eliminar? Un enlace a la versión de evaluación está justo debajo. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
(archivo .xlsm)
Ultimate Suite – versión de prueba (archivo .zip)