Fórmulas de limpieza de datos comunes en Excel

fórmulas de Excel

Durante años, he usado la publicación como un recurso no solo para describir cómo hacer las cosas, ¡sino también para llevar un registro para mí mismo para buscarlo más tarde! Hoy, tuvimos un cliente que nos entregó un archivo de datos del cliente que fue un desastre. Prácticamente todos los campos estaban mal formateados y; como resultado, no pudimos importar los datos. Si bien hay algunos complementos excelentes para que Excel haga la limpieza con Visual Basic, ejecutamos Office para Mac, que no admite macros. En cambio, buscamos fórmulas directas para ayudar. Pensé en compartir algunos de esos aquí solo para que otros puedan usarlos.

Eliminar caracteres no numéricos

Los sistemas a menudo requieren que los números de teléfono se inserten en una fórmula específica de 11 dígitos con el código del país y sin puntuación. Sin embargo, las personas a menudo ingresan estos datos con guiones y puntos. Aquí hay una gran fórmula para eliminar todos los caracteres no numéricos en Excel. La fórmula revisa los datos en la celda A2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

Ahora puede copiar la columna resultante y usar Editar> Pegar valores para escribir sobre los datos con el resultado correctamente formateado.

Evaluar varios campos con un OR

A menudo, depuramos registros incompletos de una importación. Los usuarios no se dan cuenta de que no siempre es necesario escribir fórmulas jerárquicas complejas y que, en su lugar, puede escribir una declaración OR. En este ejemplo a continuación, quiero verificar A2, B2, C2, D2 o E2 para ver si faltan datos. Si falta algún dato, devolveré un 0, de lo contrario un 1. Eso me permitirá ordenar los datos y eliminar los registros que están incompletos.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Recortar y concatenar campos

Si sus datos tienen campos de nombre y apellido, pero su importación tiene un campo de nombre completo, puede concatenar los campos de manera ordenada usando la función Concatenar de Excel incorporada, pero asegúrese de usar TRIM para eliminar los espacios vacíos antes o después de la texto. Envolvemos todo el campo con TRIM en caso de que uno de los campos no tenga datos:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Compruebe la dirección de correo electrónico válida

Una fórmula bastante simple que busca tanto @ como. en una dirección de correo electrónico:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Extraer nombres y apellidos

A veces, el problema es el contrario. Sus datos tienen un campo de nombre completo, pero debe analizar el nombre y los apellidos. Estas fórmulas buscan el espacio entre el nombre y el apellido y toman el texto cuando sea necesario. También maneja si no hay apellido o si hay una entrada en blanco en A2.

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

Y el apellido:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Limite el número de caracteres y agregue ...

¿Alguna vez quisiste limpiar tus meta descripciones? Si desea extraer contenido en Excel y luego recortarlo para usarlo en un campo de Meta Descripción (150 a 160 caracteres), puede hacerlo usando esta fórmula de Mi lugar. Rompe limpiamente la descripción en un espacio y luego agrega…:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Por supuesto, estos no están pensados ​​para ser completos ... ¡solo algunas fórmulas rápidas para ayudarlo a comenzar! ¿Qué otras fórmulas te encuentras usando? Agrégalos en los comentarios y te daré crédito mientras actualizo este artículo.

¿Qué piensas?

Este sitio usa Akismet para reducir el correo no deseado. Descubra cómo se procesan los datos de sus comentarios.