En la rutina diaria de los que trabajamos con bases de datos, nos encontramos siempre con errores de transcripción o grabado de datos; esto es: registrar mal un nombre.
En la mayoría de casos se trata de copypastes mal realizados con espacios sobrantes al final o inicio de cada nombre, luego se concatenan nombres y apellidos y ya tenemos el cristo montado.
Gracias a God, Excel nos provee de un par de opciones que, en la mayoría de casos, nos sacarán las castañas del fuego: SUSTITUIR y ESPACIOS. Tras el salto…
elimina espacios sobrantes con funciones
=SUSTITUIR()
Sustituir es la función del comando Reemplazar de Office, que tenemos dentro del comando de búsqueda (Ctrl+B), donde podemos sustituir una cadena de texto por otra. En nuestro caso le diríamos que nos sustituyera un espacio ” ” por ningún espacio “”. Lo malo es que nos elimina todo, al principio, final, entre palabras.
=ESPACIOS()
(O =RECORTAR() en versiones antiguas de MS Excel)
Esta función es más útil, pues al igual que SUSTITUIR nos elimina los espacios (sólo los espacios, y no otros caracteres), sin embargo sólo nos quita los espacios “sobrantes”, al inicio, final y entre palabras.
Para un usuario medio de Excel estas 2 funciones serán la panacea, y a un usuario avanzado le supondrán un parche temporal y lo sacarán del apuro rápidamente, pero el principal inconveniente que les veo es que nos obliga a añadir una nueva columna con los datos “arreglados”, y luego, dependiendo de lo que queramos, copiar y pegar valores… En fin, un trabajo que nos podemos ahorrar. Cómo? Con Macros, como la élite, que trabaja en las tecnologías de información y está permanentemente actualizada:
elimina espacios sobrantes con MACROS
Tenemos 3 opciones, según lo que queramos eliminar:
- Eliminar espacios al inicio de la cadena de texto
- Eliminar espacios al final de la cadena de texto
- Eliminar espacios intermedios en la cadena de texto
Para el ejemplo anterior que hemos puesto, lo que nos convienen son las 2 primeras opciones, así que vamos a escribirlas en VisualBasic (VB) para poder utilizarlas en nuestra hoja.
En la pestaña “Programador” (clic aquí si no tienes pestaña Programador) pinchamos en el Botón Macros.
Escogemos el nombre de nuestra primera Macro, que será “EliminarFinal”, teniendo en cuenta la normativa para escoger nombres de macros, y pinchamos en el botón Crear.
Ya estamos dentro de VisualBasic, así que toca escribir nuestro código para guardar las macros.
Entre Sub y End Sub es donde tenemos que guardar cada una de las macros. Si os fijáis, el primer Sub define el nombre de la macro, así que después del primer End Sub tenemos que añadir el segundo Sub con la segunda macro, y así sucesivamente… Lo pillais?
La Macro para Eliminar los espacios al inicio de la cadena de texto es esta:
Sub EliminarInicio()
Dim celda As Range
For Each celda In Selection
celda.Value = LTrim(celda.Value)
Next
End Sub
La Macro para Eliminar los espacios al final de la cadena de texto es esta:
Sub EliminarFinal()
Dim celda As Range
For Each celda In Selection
celda.Value = RTrim(celda.Value)
Next
End Sub
La Macro para Eliminar los espacios intermedios de la cadena de texto es esta última:
Sub EliminarIntermedios()
Dim celda As Range
For Each celda In Selection
celda.Value = WorksheetFunction.Trim(celda.Value)
Next
End Sub
Podéis copiar todas en bloque en el mismo cuadro de VB, comprobaréis que están todas guardadas desplegando el cuadrito de arriba a la derecha, donde veréis los nombres de las macros.
Ya podéis cerrar la ventana de VisualBasic, sin miedo.
Volviendo a nuestro ejemplo de Excel, ahora tenemos que probar las macros que hemos creado. Podemos hacerlo a lo “económico” o a lo “previsor”.
En el formato económico, no haremos nada, simplemente “llamaremos” a la macro cuando la necesitemos: simplemente selccionaremos el texto donde queremos recortar y ejecutamos la macro (Programador/Macros/Seleccionar macro/Ejecutar). Es importante que antes de ejecutar la macro, seleccionéis el rango de celdas que queréis limpiar.
Ejecutaremos una tras otra hasta limpiar nuestro texto como queremos.
A los muy vagos (como yo) les resultará muy engorroso tener que ejecutar 3 macros para limpiar un texto… así que vámonos al formato “previsor”, porque en mi caso, es algo que tengo que hacer prácticamente todos los días.
Como ya tenemos creadas las macros, vamos a “vincularlas” a botones que guardaremos en la plantilla que utilizamos para realizar nuestros informes rutinarios.
En la pestaña Programador, tenemos el comando Insertar, que nos permite añadir un botón a nuestra hoja.
Vamos a crear 3 botones, uno para cada Macro. Cuando lo creamos, directament nos pregunta si queremos asignarle una macro, vamos a seleccionar Inicio y lo cerramos.
Repetimos los pasos con otros 2 botones más, pero asignando las otras 2 macros. Pulsando con el botón derecho encima de cada botón, podemos modificar el nombre del mismo, para identificarlos.
Y ya hemos terminado, simplemente tenemos que seleccionar las celdas que queremos limpiar, una a una o en bloque, y pinchar el botón de la macro que queramos ejecutar.
Si estos botones los guardamos en la plantilla que utilizamos rutinariamente, nos ahorrará mucho tiempo y dolores de cabeza.
También podéis crear una macro con las 3 funciones en 1, para ahorrar más tiempo aún:
Sub EliminarTotal()
Dim celda As Range
For Each celda In Selection
celda.Value = LTrim(celda.Value)
celda.Value = RTrim(celda.Value)
celda.Value = WorksheetFunction.Trim(celda.Value)
Next
End Sub
¿Y PORQUÉ NO LO DIJISTE ANTES?
No pain, no gain.
Por cierto, una vez ejecutada la macro, cualquiera de ellas, no existe la opción “deshacer”. Estad muy seguros de hacer las cosas bien 😉
POR FIN UNA PREGUNTA QUE ME RESPONDEN BIEN DETALLADA GRACIAS!!!
TENIA QUE EDITAR UN MONTÓN DE DATOS GRACIAS!!!
Muchas Gracias.Excelente artículo.
Excelente tip!… Me funciona bastante bien.
Traté de usar ésta Macro para Más de 1000 líneas… Y veo que me lleva mucho tiempo…
¿Habrá alguna manera de mejorarla para que responda en un tiempo razonable?
Muchas gracias desde ya por su atención y apoyo!
Prueba con esto: https://www.todoexcel.com/14-formas-de-acelerar-y-optimizar-tus-macros-excel/
Por curiosidad y de tener que resolver un problema, que no faltan, acabo de encontrarme con las soluciones a muchas preguntas y trabas, que solicitan sus lectores, ahora yo soy parte de los que lo leemos y solicitaré que por favor me resolvieran algunos, de antemano el agradecimiento por sus respuestas, cordialmente, jamb.
Excelente artículo. Eres lo máximo.
Tengo varias cadenas de texto que enpiezan por datos diferentes pero todas tienen un texto casi al medio que me interesa sea el primero, de tal manera que se elimine lo anterior a TITULAR: Ejemplo: Casa dos pisos colo Blanco TITULAR indico que se aceercara mañana. Me interese que solo quede: TITULAR indico que se aceercara mañana.
Hola,
Mi consulta no es exactamente lo mismo pero consiste en quitar un salto de línea que creo que es parecido.
Quiero combinar varias celdas en una, conservando el texto. Lo consigo pero me incluye un salto de línea al principio que es el que quiero quitar.
El código que lo he encontrado en la web (excelnegocios.com) es este:
Sub unir_celdas()
celda = “”
For Each Rng In Selection
celda = celda & Chr(10) & Rng.Value
Next Rng
Application.DisplayAlerts = False
With Selection
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.MergeCells = True
.WrapText = True
.Orientation = 0
End With
Application.DisplayAlerts = True
ActiveCell.Value = celda
End Sub
Gracias y saludos
Hola, sabes como eliminar espacios en una celda que contenga un dato numérico, pues no me elimina los espacios con estas funciones, se mantienen los espacios al inicio y al final del número.
Te dejo est formula que a mi me funciono.
=VALOR(ESPACIOS(SUSTITUIR(B2;CARACTER(160);CARACTER(32))))
la celda que tiene el problema es la “B2”.
Perfecto
Excelente! de gran ayuda
Me sirvio para Trabajar con los NPE porque excel me los convertia en Notación Científica!!!
Mil Gracias!!!!!!!!
Me sirvio de una manera que no te imaginas
investigando un poco mas en la web me encontre con una macro que hace todo lo que tu dijiste en este post.
Sub EliminarTotal()
Dim celda As Range
For Each celda In Selection
celda.Value = LTrim(celda.Value)
celda.Value = RTrim(celda.Value)
celda.Value = WorksheetFunction.Trim(celda.Value)
Next
End Sub
pero no explicaban como aplicarla y tu si lo hiciste.
espero les sirva
Buenas tardes, no me funciona con números, he probado todas las funciones como, sustituir, extraer, espacio, izquierda, derecha, funciones anidadas etc., y ahora con esta macro, pero no me elimina los dos espacios finales , al importarlos a excel no me los reconoce como números
ejemplo:
5.990,00
40.000,00
15.900,00
Hola Rafael,
En este caso veo que el espacio en blanco es distinto del que normalmente conocemos que se genera con la barra espaciadora, por esto para reemplazar este espacio que existe en las celdas con número es necesario que edites una de las celdas que contiene el espacio “F2”, seleccionas uno de los espacios “Ctrl+C” lo copias, luego seleccionas el rango de celdas con números que contienen los espacios que deseas que desaparezcan, luego buscas en el menú la opción “buscar y reemplazar”, creo que aparece con el Ctrl+F o Ctrl+H, cuando aparece la ventana pegas en la casilla de buscar el espacio que habías copiado previamente y en la casilla de reemplazar te aseguras que no tenga ningún espacio ni valor y le das clic en reemplazar todo.
Deberá funcionar.
vi mil y un maneras de poder solucionar este tema, y bastaba con un reemplazar… gracias!!!
excelente funciona muy bien como lo describes, muchas gracias.
Muchas gracias, ya lo había hecho antes, pero se me olvido, y ahora rompiéndome la cabeza buscando por varias horas hasta que llegue a ti y recordé que así era.
Excelente ! lo busque por mucho tiempo hasta leer tu comentario.
gracias y saludos.
buen dia como elimino un espacio de un numero a mano izquierda o derecha, ya que al sumar solo hace recuento y no me da resultados en excel.
ejemplo estos, tienen espacios al final:
7.548.689,79
4.359.744,00
Yo hice exactamente lo mismo sólo es copiar la macro tal cual y ejecutarla y ya.
Te dejo esta formula que a mi me funciono.
=VALOR(ESPACIOS(SUSTITUIR(B2;CARACTER(160);CARACTER(32))))
la celda que tiene el problema es la “B2”.
Me has salvado la vida, nunca había manejado macros y me salió bien
Hola!
Como se elimina un espacio intermedio? 982 000413235768
excelente tutorial, magnifica explicación y ayuda.
gracias amigo por tu aporte.
Buenisimo, me gustaría saber como se podría agregar a la macro, las doble comilla, apostrofe, comilla especial, coma, etc, se puede?
EXCELENTE!
Mil gracias lo acabo de poner y me ayudo muchiiiiisimooo
Muchas gracias por tu comentario y por seguirnos Any.
ME SALVASTE, TENIA QUE QUITAR ESPACIOS UNO POR UNO 40000 REGISTROS. GRACIAS
Gracias por seguir educadictos, un saludo
Muchas gracias. Una explicación clara para un problemas con grandes volúmenes de datos.
Buen Aporte y gran detalle. Muchas gracias por compartir tu conocimiento.
Saludos
Super, eres todo un maestro.- Excelente material, gracias por enseñar y compratir, Dios te bendiga.- Una pregunta que se me ocurre: y si el proceso es inverso, es decir en una celda tengo una cadena de caracteres por ejemplo PEDRO PICAPIEDRA y deseo insertar tres espacios en blanco entre cada letra y que quede: P E D R O P I C A P I D R A, cual sería el código de la macro indicada?- Gracias por responder, un saludo desde Colombia Ricardo Sanchez, e-mail: rsancheza54@gmail.com.
Hola Ricardo, puede que te sirva esta información: https://es.extendoffice.com/documents/excel/3465-excel-add-space-between-characters.html
Saludos