Formule Excel pentru curățarea comună a datelor

Formule de curățare a datelor Excel

De ani de zile, am folosit publicația ca resursă nu doar pentru a descrie cum să fac lucrurile, ci și pentru a păstra o evidență pentru a căuta mai târziu! Astăzi am avut un client care ne-a înmânat un fișier de date despre clienți care a fost un dezastru. Practic fiecare câmp a fost formatat greșit și; ca urmare, nu am putut importa datele. Deși există câteva suplimente excelente pentru Excel pentru a face curățarea folosind Visual Basic, rulăm Office pentru Mac, care nu acceptă macrocomenzi. În schimb, căutăm formule corecte care să vă ajute. M-am gândit să le împărtășesc pe unele dintre acestea doar pentru ca alții să le poată folosi.

Eliminați caractere nenumerice

Sistemele necesită adesea introducerea numerelor de telefon într-o formulă specifică de 11 cifre, cu codul țării și fără punctuație. Cu toate acestea, oamenii introduc adesea aceste date cu liniuțe și puncte. Iată o formulă excelentă pentru eliminarea tuturor caracterelor nenumerice în Excel. Formula analizează datele din celula 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))

Acum puteți copia și utiliza coloana rezultată Editați> Inserați valori pentru a scrie peste date cu rezultatul formatat corespunzător.

Evaluați câmpuri multiple cu un OR

De multe ori eliminăm înregistrările incomplete de la un import. Utilizatorii nu realizează că nu trebuie întotdeauna să scrieți formule ierarhice complexe și că puteți scrie o declarație SAU. În acest exemplu de mai jos, doresc să verific A2, B2, C2, D2 sau E2 pentru datele lipsă. Dacă lipsesc date, voi returna un 0, altfel un 1. Asta îmi va permite să ordonez datele și să șterg înregistrările incomplete.

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

Tăiați și concatenați câmpurile

Dacă datele dvs. au un câmp Numele și prenumele, dar importul dvs. are un câmp cu numele complet, puteți concatena câmpurile împreună, folosind funcția Excel concatenată încorporată, dar asigurați-vă că utilizați TRIM pentru a elimina orice spații goale înainte sau după text. Înfășurăm întregul câmp cu TRIM în cazul în care unul dintre câmpuri nu are date:

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

Verificați dacă există o adresă de e-mail validă

O formulă destul de simplă care caută atât @, cât și. într-o adresă de e-mail:

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

Extrageți numele și prenumele

Uneori, problema este opusă. Datele dvs. au un câmp complet de nume, dar trebuie să analizați numele și prenumele. Aceste formule caută spațiul dintre prenume și prenume și preiau text acolo unde este necesar. IT se ocupă și dacă nu există un nume de familie sau dacă există o intrare goală în A2.

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

Și numele de familie:

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

Limitați numărul de caractere și adăugați ...

Ați dorit vreodată să vă curățați meta descrierile? Dacă doriți să trageți conținut în Excel și apoi să tăiați conținutul pentru a fi utilizat într-un câmp Meta Description (150 până la 160 de caractere), puteți face acest lucru folosind această formulă din Locul meu. Se rupe în mod curat descrierea într-un spațiu și apoi adaugă ...:

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

Bineînțeles, acestea nu sunt menite să fie cuprinzătoare ... doar câteva formule rapide care să vă ajute să începeți cu salt! Ce alte formule te folosești? Adăugați-le în comentarii și vă voi da credit pe măsură ce actualizez acest articol.

Ce părere ai?

Acest site folosește Akismet pentru a reduce spamul. Aflați cum sunt procesate datele despre comentarii.