En el post anterior, 8 formas de limpiar datos en excel (parte 1), vimos las primeras 4 maneras de limpiar datos en excel: revisión ortográfica, quitar filas duplicadas, buscar y reemplazar texto y, por último, cambiar el uso de mayúscula y minúscula de un texto. En esta segunda parte veremos las 4 últimas formas que nos quedaban por ver. ¡Tomad nota!
5. Quitar espacios y caracteres no imprimibles del texto
A veces los valores de texto contienen caracteres de espacio incrustado en la primera parte, al final o en varios sitios.
A menudo, estos caracteres pueden producir resultados inesperados al ordenar, filtrar o buscar. Por ejemplo, en el origen de datos externo, los usuarios pueden cometer errores tipográficos al agregar, sin darse cuenta, caracteres de espacio adicional o datos de texto importado desde orígenes externos que pueden contener caracteres no imprimibles que están incrustados en el texto. Debido a que estos caracteres no se observan fácilmente, puede ser difícil comprender los resultados inesperados. Para quitar estos caracteres no deseados, puedes utilizar una combinación de las funciones ESPACIOS y SUSTITUIR.
A) Función ESPACIOS
Elimina los espacios del texto, excepto el espacio normal que se deja entre palabras. Utiliza ESPACIOS en texto procedente de otras aplicaciones que pueda contener un espaciado irregular.
Sintaxis
ESPACIOS (texto)
La sintaxis de la función ESPACIOS tiene los siguientes argumentos:
- Texto Obligatorio. Es el texto del que deseas quitar espacios.
B) Nuncio SUSTITUIR
Sustituye texto_original por texto_nuevo dentro de una cadena de texto. Utiliza SUSTITUIR para reemplazar texto específico en una cadena de texto.
Sintaxis
SUSTITUIR (texto, texto_original, texto_nuevo, [núm_de_ocurrencia])
La sintaxis de la función SUSTITUIR tiene los siguientes argumentos:
- Texto Obligatorio. Es el texto o la referencia a una celda que contiene el texto en el que deseas sustituir caracteres.
- Texto_original Obligatorio. Es el texto que deseas sustituir.
- Texto_nuevo Obligatorio. Es el texto por el que deseas reemplazar el texto_original.
- Núm_de_ocurrencia Opcional. Especifica la instancia de texto_original que se desea reemplazar por texto_nuevo. Si especifica el argumento núm_de_ocurrencia, solo se remplaza esa instancia de texto_original. De lo contrario, todas las instancias de texto_original en texto se sustituirán por texto_nuevo.
6. Corrige los valores de fecha
Debido a que hay distintos formatos de fecha y que estos formatos pueden confundirse con códigos de elemento numerado u otras cadenas que contienen barras diagonales o guiones, a menudo es necesario convertir y volver a dar formato a los valores de fecha.
- Convertir fechas almacenadas como texto en fechas
A veces, las fechas pueden adquirir formato de texto y almacenarse como texto en las celdas. Por ejemplo, es posible que hayas escrito una fecha en una celda con formato de texto o que los datos se hayan importado o pegado desde un origen de datos externo como texto.
Las fechas con formato de texto se alinean en una celda a la izquierda (en lugar de a la derecha). Cuando la comprobación de errores está habilitada, las fechas de texto con años de dos dígitos también se pueden marcar con un indicador de error: .
Puesto que la comprobación de errores en Excel puede detectar las fechas con formato de texto con años de dos dígitos, puedes utilizar las opciones automáticas de corrección para convertirlas en fechas con formato de fecha. Puedes usar la función FECHANUMERO para convertir la mayoría de los tipos de fechas de texto en fechas.
- Función FECHANUMERO:
Para convertir una fecha de texto en una celda en un número de serie, utiliza la función FECHANUMERO. A continuación, copia la fórmula, selecciona las celdas que contienen las fechas de texto y usa Pegado especial para aplicarles un formato de fecha.
Sigue estos pasos:
- Selecciona una celda en blanco y comprueba que el formato de número es General.
- En la celda en blanco:
- Escribe = FECHANUMERO (
- Haz clic en la celda que contiene la fecha con formato de texto que desees convertir.
- Enter )
- Presiona enter y la función FECHANUMERO devuelve el número de serie de la fecha representada por la fecha de texto.
¿Qué es un número de serie de Excel?
Excel almacena las fechas como números de serie secuenciales para que se puedan usar en cálculos. De forma predeterminada, el 1 de enero de 1900 es el número de serie 1 y el 1 de enero de 2008, que es el número de serie 39448 porque es 39.448 días después del 1 de enero 1900. Para copiar la fórmula de conversión en un rango de celdas contiguas, selecciona la celda que contiene la fórmula introducida y, a continuación, arrastra el controlador de relleno un rango de celdas vacías que coincida en tamaño con el rango de celdas que contiene las fechas de texto.
- Después de arrastrar el controlador de relleno, deberías tener un rango de celdas con números de serie que se corresponda con el rango de celdas que contiene las fechas de texto.
- Selecciona la celda o el rango de celdas que contiene los números de serie y, a continuación, haga clic en botón derecho y seleccione copiar.
- Selecciona la celda o rango de celdas que contiene las fechas de texto y, haz clic en botón derecho y selecciona Pegado especial.
- En el cuadro de diálogo Pegado especial, en Pegar, selecciona Valores y haz clic en Aceptar.
- En la pestaña Inicio, haz clic en el selector de la ventana emergente junto a número.
- En el cuadro Categoría, haz clic en Fecha y, en la lista Tipo, haz clic en el formato de fecha que desees.
- Para eliminar los números de serie después de que todas las fechas se hayan convertido correctamente, selecciona las celdas que los contienen y, a continuación, presiona la tecla SUPRIMIR.
7. Combinar y dividir columnas
Una tarea común después de importar los datos desde un origen de datos externos es combinar dos o más columnas en una, o dividir una columna en dos o más columnas. Por ejemplo, podrías dividir una columna que contiene un nombre completo en un nombre y apellido. O bien, podrías dividir una columna que contiene un campo de dirección en columnas independientes de calle, ciudad, región y código postal. También puede ocurrir a la inversa. Te podría interesar combinar una columna de nombre y apellido en una columna de nombre completo o combinar columnas de dirección independientes en una sola columna.
Combinar datos de diferentes celdas, por ejemplo nombres y apellidos.
- Combinar datos con el símbolo (&)
- Selecciona la celda en la que quieras poner los datos combinados.
- Escribe = y, a continuación, selecciona la primera celda que desees combinar.
- Escribe & y entrecomilla un espacio en blanco.
- Selecciona la celda siguiente que desees combinar y presiona Entrar. Una fórmula de ejemplo podría ser =A2&” “&B2.
ó utilizando la formula CONCATENAR
- Combinar datos con la función CONCAT
- Selecciona la celda en la que quieras poner los datos combinados.
- Escribe =CONCAT(
- Selecciona las celdas que quieras combinar primero.
Utiliza puntos y coma para separar las celdas que quieras combinar y usa comillas para agregar espacios, comas o texto adicional. - Cierra la fórmula con un paréntesis y presiona Entrar.
Dividir texto en diferentes columnas con el Asistente para convertir texto en columnas
Puedes colocar el texto en una o más celdas y dividirlo en varias celdas con el Asistente para convertir texto en columnas.
- Selecciona la celda o columna que contiene el texto que quieras dividir.
- Selecciona Datos > Texto en columnas.
- En el Asistente para convertir texto en columnas, selecciona Delimitado > Siguiente.
- Selecciona los Delimitadores para sus datos. Por ejemplo, Coma y Espacio. Puedes ver una vista previa de los datos en la ventana Vista previa de datos.
- Selecciona Siguiente.
- Selecciona el Formato de datos de la columna o usa la sugerencia de Excel.
- Selecciona el Destino, que es donde quieres que aparezcan los datos divididos en la hoja de cálculo.
- Selecciona Finalizar.
8. Transformar y reorganizar columnas y filas
La mayoría de los análisis y las características de formato en Office Excel suponen que los datos existen en una única tabla plana bidimensional. A veces, puede que quieras hacer que las filas se conviertan en columnas y que las columnas se conviertan en filas. Otras veces, los datos no están siquiera estructurados en un formato tabular y necesitas poder transformarlos de un formato no tabular a uno tabular.
En ocasiones necesitarás cambiar o girar celdas. Para ello, puedes copiar, pegar especial y usar la opción de transposición. Pero al hacerlo se crean datos duplicados. Para evitarlo, puedes escribir una fórmula en lugar de utilizar la función TRANSPONER. Por ejemplo, en la siguiente imagen, la fórmula =TRANSPONER(A1:B4) toma las celdas A1 a B4 y las organiza horizontalmente.
La clave para conseguir que TRANSPONER funcione: Asegúrate de presionar CTRL+MAYÚS+ENTRAR después de escribir la fórmula. Si nunca antes has escrito este tipo de fórmula, los siguientes pasos te guiarán a través del proceso.
Paso 1: Seleccionar celdas en blanco
En primer lugar selecciona varias celdas en blanco. Pero asegúrete de seleccionar el mismo número de celdas que en el conjunto de celdas original, pero en la dirección contraria. Por ejemplo, aquí hay 8 celdas organizadas verticalmente:
Por lo tanto, tenemos que seleccionar ocho celdas horizontales, de esta forma:
Aquí es donde acabarán las nuevas celdas tras la transposición.
Paso 2: Escribir =TRANSPONER(
Con las mismas celdas en blanco seleccionadas, escribe: =TRANSPONER(
Excel tendrá un aspecto similar al siguiente:
Observa que las ocho celdas siguen seleccionadas aunque hemos empezado a escribir una fórmula.
Paso 3: Escribir el rango de las celdas originales.
Ahora, escribe el rango de las celdas que desees transponer. En este ejemplo, queremos transponer las celdas de A1 a B4. La fórmula de este ejemplo sería entonces: =TRANSPONER(A1:B4); pero no presiones ENTRAR todavía. Simplemente deja de escribir y ve al siguiente paso.
Excel tendrá un aspecto similar al siguiente:
Paso 4: Para finalizar, presiona CTRL+MAYÚS+ENTRAR.
Ahora presiona CTRL+MAYÚS+ENTRAR. ¿Por qué? Porque la función TRANSPONER solo se utiliza en fórmulas de matriz y esta es la forma de terminar una fórmula de matriz. En resumen, una fórmula de matriz es una fórmula que se aplica a más de una celda. Como has seleccionado más de una celda en el paso 1, la fórmula se aplicará a más de una celda. Este es el resultado después de presionar CTRL+MAYÚS +ENTRAR:
Y hasta aquí las últimas 4 maneras de limpiar datos en excel. Recuerda que este post se divide en 2 partes y que vimos las primeras 4 formas en el post anterior. Si se te ha pasado por alto, te recomendamos que le eches un vistazo.
EXCELente material…éxitos