Algemene formules voor het opschonen van gegevens in Excel

Excel-formules

Jarenlang heb ik de publicatie gebruikt als een hulpmiddel om niet alleen te beschrijven hoe dingen moeten worden gedaan, maar ook om een ​​record bij te houden zodat ik het later kan opzoeken! Vandaag hadden we een klant die ons een klantgegevensbestand overhandigde dat een ramp was. Vrijwel elk veld was verkeerd opgemaakt en; Als gevolg hiervan konden we de gegevens niet importeren. Hoewel er een aantal geweldige add-ons voor Excel zijn om het opruimen met Visual Basic uit te voeren, gebruiken we Office voor Mac dat geen macro's ondersteunt. In plaats daarvan zoeken we naar duidelijke formules om te helpen. Ik dacht dat ik er hier een paar zou delen, zodat anderen ze kunnen gebruiken.

Verwijder niet-numerieke tekens

Systemen vereisen vaak dat telefoonnummers worden ingevoegd in een specifieke formule van 11 cijfers met de landcode en zonder interpunctie. Mensen voeren deze gegevens echter vaak in met streepjes en punten. Hier is een geweldige formule voor het verwijderen van alle niet-numerieke tekens in Excel. De formule beoordeelt de gegevens in cel 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 kunt u de resulterende kolom kopiëren en gebruiken Bewerken> Waarden plakken om over de gegevens te schrijven met het correct geformatteerde resultaat.

Evalueer meerdere velden met een OF

We verwijderen vaak onvolledige records uit een import. Gebruikers realiseren zich niet dat u niet altijd complexe hiërarchische formules hoeft te schrijven en dat u in plaats daarvan een OR-instructie kunt schrijven. In dit onderstaande voorbeeld wil ik A2, B2, C2, D2 of E2 controleren op ontbrekende gegevens. Als er gegevens ontbreken, ga ik een 0 retourneren, anders een 1. Op die manier kan ik de gegevens sorteren en de records die onvolledig zijn, verwijderen.

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

Velden bijsnijden en samenvoegen

Als uw gegevens een voor- en achternaam hebben, maar uw import een volledig naamveld heeft, kunt u de velden netjes samenvoegen met de ingebouwde Excel-functie Concatenate, maar zorg ervoor dat u TRIM gebruikt om lege spaties voor of na de tekst. We omwikkelen het hele veld met TRIM voor het geval dat een van de velden geen gegevens bevat:

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

Controleer op een geldig e-mailadres

Een vrij eenvoudige formule die zoekt naar zowel de @ als. in een e-mailadres:

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

Pak voor- en achternaam uit

Soms is het probleem het tegenovergestelde. Uw gegevens hebben een volledig naamveld, maar u moet de voor- en achternaam ontleden. Deze formules zoeken de spatie tussen de voor- en achternaam en pakken waar nodig tekst. IT handelt ook af als er geen achternaam is of als er een lege invoer is in A2.

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

En de achternaam:

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

Beperk het aantal tekens en voeg toe ...

Heb je ooit je metabeschrijvingen willen opschonen? Als u inhoud naar Excel wilt halen en vervolgens de inhoud wilt inkorten voor gebruik in een metabeschrijvingveld (150 tot 160 tekens), kunt u dat doen met deze formule van Mijn plek. Het breekt de beschrijving netjes op een spatie en voegt vervolgens de ...:

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

Deze zijn natuurlijk niet bedoeld om allesomvattend te zijn… slechts enkele snelle formules om u een vliegende start te geven! Welke andere formules gebruikt u? Voeg ze toe in de comments en ik geef je de eer als ik dit artikel update.

Wat denk je?

Deze site gebruikt Akismet om spam te verminderen. Ontdek hoe uw reactiegegevens worden verwerkt.