Formule comuni di pulizia dei dati in Excel

eccellere le formule

Per anni, ho usato la pubblicazione come risorsa non solo per descrivere come fare le cose, ma anche per tenere un registro per me stesso per cercare in seguito! Oggi abbiamo avuto un cliente che ci ha consegnato un file di dati del cliente che è stato un disastro. Praticamente ogni campo era formattato in modo errato e; di conseguenza, non siamo stati in grado di importare i dati. Sebbene ci siano alcuni ottimi componenti aggiuntivi per Excel per eseguire la pulizia utilizzando Visual Basic, eseguiamo Office per Mac che non supporta le macro. Invece, cerchiamo formule dirette per aiutare. Ho pensato di condividerne alcuni qui solo in modo che altri possano usarli.

Rimuovi caratteri non numerici

I sistemi spesso richiedono l'inserimento dei numeri di telefono in una formula specifica di 11 cifre con il prefisso internazionale e senza punteggiatura. Tuttavia, le persone spesso inseriscono questi dati con trattini e punti. Ecco un'ottima formula per rimuovendo tutti i caratteri non numerici in Excel. La formula rivede i dati nella cella 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))

Ora puoi copiare la colonna risultante e utilizzare Modifica> Incolla valori per sovrascrivere i dati con il risultato formattato correttamente.

Valuta più campi con un OR

Spesso eliminiamo i record incompleti da un'importazione. Gli utenti non si rendono conto che non devi sempre scrivere formule gerarchiche complesse e che puoi invece scrivere un'istruzione OR. In questo esempio di seguito, voglio controllare A2, B2, C2, D2 o E2 per i dati mancanti. Se mancano dei dati, restituirò uno 0, altrimenti un 1. Ciò mi consentirà di ordinare i dati ed eliminare i record incompleti.

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

Taglia e concatena i campi

Se i tuoi dati hanno un campo Nome e Cognome, ma l'importazione ha un campo nome completo, puoi concatenare i campi insieme in modo ordinato utilizzando la funzione Concatenate di Excel incorporata, ma assicurati di utilizzare TRIM per rimuovere eventuali spazi vuoti prima o dopo il testo. Avvolgiamo l'intero campo con TRIM nel caso in cui uno dei campi non abbia dati:

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

Verificare la presenza di un indirizzo e-mail valido

Una formula piuttosto semplice che cerca sia @ che. in un indirizzo email:

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

Estrai nome e cognome

A volte, il problema è l'opposto. I tuoi dati hanno un campo nome completo ma devi analizzare il nome e il cognome. Queste formule cercano lo spazio tra il nome e il cognome e prendono il testo dove necessario. Gestisce anche se non c'è un cognome o c'è una voce vuota in A2.

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

E il cognome:

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

Limita il numero di caratteri e aggiungi ...

Hai mai voluto ripulire le tue meta descrizioni? Se si desidera estrarre il contenuto in Excel e quindi tagliare il contenuto per utilizzarlo in un campo Meta Description (da 150 a 160 caratteri), è possibile farlo utilizzando questa formula da Il mio posto. Rompe in modo netto la descrizione in uno spazio e quindi aggiunge ...:

=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)

Naturalmente, questi non sono pensati per essere completi ... solo alcune formule veloci per aiutarti a ottenere un buon inizio! Quali altre formule ti ritrovi a usare? Aggiungili nei commenti e ti darò credito mentre aggiorno questo articolo.

Cosa ne pensi?

Questo sito utilizza Akismet per ridurre lo spam. Scopri come vengono elaborati i dati dei tuoi commenti.