Formule cumune di pulizia di dati in Excel

formule excel

Dapoi anni, aghju utilizatu a publicazione cum'è una risorsa per ùn solu descrive cume fà e cose, ma ancu per tene un registru per mè di cercà dopu! Oghje, avemu avutu un cliente chì ci hà datu un schedariu di dati di u cliente chì era un disastru. Praticamente ogni campu era misformattatu è; di conseguenza, ùn pudemu micca impurtà i dati. Mentre chì ci sò qualchì grande add-ons per Excel per fà a pulizia cù Visual Basic, eseguemu Office per Mac chì ùn supporterà micca i macros. Invece, circhemu formule dritte per aiutà. Aghju pensatu di sparte alcuni di quelli quì solu per chì l'altri li ponu aduprà.

Eliminà i caratteri non numerichi

I sistemi richiedenu spessu chì i numeri di telefunu sianu inseriti in una formula specifica di 11 cifre cù u codice di paese è senza puntuazione. Tuttavia, a ghjente inserisce spessu questi dati cun trattini è periodi invece. Eccu una grande formula per eliminendu tutti i caratteri non numerichi in Excel. A formula rivede i dati in a 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 pudete copià a colonna resultante è aduprà Modificà> Incolla Valori per scrive nantu à i dati cù u risultatu furmatu currettamente.

Valuta Campi Multipli cun OR

Spurgemu spessu registri incompleti da una impurtazione. L'utenti ùn si rendenu micca contu chì ùn avete micca sempre à scrive formule gerarchiche cumplesse è chì pudete scrive una dichjarazione OR invece. In questu esempiu quì sottu, vogliu verificà A2, B2, C2, D2, o E2 per i dati mancanti. S'ellu manca qualchì dati, vultaraghju un 0, altrimente un 1. Ciò mi permetterà di sorte l'ordine di i dati è di cancellà i registri chì sò incompleti.

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

Trim è Concatenate Campi

Se i vostri dati anu un campu Nome è Cognome, ma a vostra impurtazione hà un campu di nome cumpletu, pudete cuncatenà i campi inseme pulitamente aduprendu a Funzione Excel Concatenata, ma assicuratevi di aduprà TRIM per rimuovere tutti i spazi vuoti prima o dopu a testu. Circundemu tuttu u campu cù TRIM in casu chì unu di i campi ùn abbia micca dati:

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

Verificate l'indirizzu email validu

Una formula abbastanza simplice chì cerca sia @ sia. in un indirizzu email:

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

Estrae i Nomi è i Cognomi

A volte, u prublema hè u cuntrariu. I vostri dati anu un campu di nome cumpletu ma avete bisognu di analisà u nome è i cognomi. Queste formule cercanu u spaziu trà u nome è u cognome è piglianu u testu induve hè necessariu. L'IT gestisce ancu s'ellu ùn ci hè alcun cognome o ci hè una voce in biancu in A2.

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

È u cognome:

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

Limita u Numaru di Caratteri è Aghjunghje ...

Avete mai vulsutu pulisce e vostre meta descrizzioni? Se vulete tirà u cuntenutu in Excel è dopu taglià u cuntenutu per aduprà in un campu Meta Description (150 à 160 caratteri), pudete fà ciò aduprendu sta formula da U mo Spot. Rompi pulitu a descrizzione in un spaziu è poi aghjunghje u ...:

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

Benintesa, queste ùn sò micca destinate à esse cumplette ... solu alcune formule rapide per aiutavvi à principià! Chì altre formule vi truvate aduprendu? Aghjunghjeli in i cumenti è ti daraghju creditu mentre aghjurnu questu articulu.

Chì ne pensi?

Stu situ utilizeghja Akismet per reducisce u puzzicheghju. Sapete ciò chì i dati di i vostri dati è processatu.