Excel -formler för gemensam datarensning

Formler för datarensning i Excel

I flera år har jag använt publikationen som en resurs för att inte bara beskriva hur man gör saker, utan också för att registrera mig själv för att leta upp senare! Idag hade vi en klient som gav oss en kunddatafil som var en katastrof. Nästan alla fält var felformaterade och; som ett resultat kunde vi inte importera data. Även om det finns några bra tillägg för Excel för att göra saneringen med Visual Basic, kör vi Office för Mac som inte stöder makron. Istället letar vi efter raka formler som hjälper. Jag trodde att jag skulle dela några av dem här bara så att andra kan använda dem.

Ta bort icke-numeriska tecken

System kräver ofta att telefonnummer sätts in i en specifik 11-siffrig formel med landskoden och ingen skiljetecken. Men folk anger ofta denna information med bindestreck och perioder istället. Här är en bra formel för ta bort alla icke-numeriska tecken i Excel. Formeln granskar data i cell 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))

Nu kan du kopiera den resulterande kolumnen och använda Redigera> Klistra in värden att skriva över data med korrekt formaterat resultat.

Utvärdera flera fält med en OR

Vi rensar ofta ofullständiga poster från en import. Användare inser inte att du inte alltid behöver skriva komplexa hierarkiska formler och att du istället kan skriva ett OR-uttalande. I det här exemplet nedan vill jag kontrollera A2, B2, C2, D2 eller E2 för saknade data. Om någon data saknas ska jag returnera en 0, annars en 1. Det gör att jag kan sortera data och ta bort de ofullständiga posterna.

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

Trimma och sammanfoga fält

Om dina data har ett för- och efternamnfält, men din import har ett fullständigt namnfält, kan du sammanfoga fälten på ett snyggt sätt med hjälp av den inbyggda Excel-funktionssammanfogningen, men se till att du använder TRIM för att ta bort tomma utrymmen före eller efter text. Vi slår in hela fältet med TRIM om ett av fälten inte har data:

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

Sök efter giltig e-postadress

En ganska enkel formel som letar efter både @ och. i en e-postadress:

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

Extrahera för- och efternamn

Ibland är problemet tvärtom. Dina uppgifter har ett fullständigt namnfält men du måste analysera för- och efternamn. Dessa formler letar efter mellanslag mellan för- och efternamn och fångar text vid behov. IT hanteras också om det inte finns något efternamn eller om det finns en tom post i A2.

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

Och efternamnet:

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

Begränsa antalet tecken och lägg till ...

Ville du någonsin rensa dina metabeskrivningar? Om du vill hämta innehåll till Excel och sedan trimma innehållet för användning i ett Metabeskrivningsfält (150 till 160 tecken) kan du göra det med den här formeln från Min plats. Den bryter beskrivningen rent i ett utrymme och lägger sedan till ...:

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

Naturligtvis är dessa inte tänkta att vara heltäckande ... bara några snabba formler som hjälper dig att komma igång! Vilka andra formler använder du? Lägg till dem i kommentarerna så ger jag dig kredit när jag uppdaterar den här artikeln.

Vad tror du?

Den här sidan använder Akismet för att minska spam. Läs om hur din kommentardata behandlas.