Almindelige dataoprydningsformler i Excel

excel formler

I årevis har jeg brugt publikationen som en ressource til ikke bare at beskrive, hvordan man gør ting, men også til at registrere mig selv for at se op senere! I dag havde vi en klient, der gav os en kundedatafil, der var en katastrofe. Næsten hvert felt var forkert formateret og; som et resultat kunne vi ikke importere dataene. Mens der er nogle gode tilføjelser til Excel til at udføre oprydning ved hjælp af Visual Basic, kører vi Office til Mac, som ikke understøtter makroer. I stedet ser vi efter lige formler til at hjælpe. Jeg troede, jeg ville dele nogle af dem her, så andre kan bruge dem.

Fjern ikke-numeriske tegn

Systemer kræver ofte, at telefonnumre indsættes i en bestemt, 11-cifret formel med landekoden og ingen tegnsætning. Folk indtaster dog ofte disse data med bindestreger og perioder i stedet. Her er en god formel til fjerne alle ikke-numeriske tegn i Excel. Formlen gennemgår dataene i celle 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 kopiere den resulterende kolonne og bruge Rediger> Indsæt værdier at skrive over dataene med det korrekt formaterede resultat.

Evaluer flere felter med en ELLER

Vi renser ofte ufuldstændige poster fra en import. Brugere er ikke klar over, at du ikke altid behøver at skrive komplekse hierarkiske formler, og at du i stedet kan skrive en ELLER-sætning. I dette eksempel nedenfor vil jeg kontrollere A2, B2, C2, D2 eller E2 for manglende data. Hvis der mangler data, returnerer jeg en 0, ellers en 1. Det giver mig mulighed for at sortere ordre på dataene og slette de ufuldstændige poster.

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

Trim og sammenkæd felter

Hvis dine data har et for- og efternavn-felt, men din import har et fuldt navnefelt, kan du sammenkæde felterne pænt ved hjælp af den indbyggede Excel-funktion sammenkædning, men sørg for at bruge TRIM til at fjerne tomme mellemrum før eller efter tekst. Vi indpakker hele feltet med TRIM, hvis et af felterne ikke har data:

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

Se efter gyldig e-mail-adresse

En ret simpel formel, der ser efter både @ og. i en e-mail-adresse:

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

Uddrag for- og efternavne

Nogle gange er problemet det modsatte. Dine data har et fuldt navnefelt, men du skal analysere for- og efternavne. Disse formler ser efter mellemrummet mellem for- og efternavn og tag tekst, hvor det er nødvendigt. IT håndterer også, hvis der ikke er noget efternavn, eller hvis der er en tom post i A2.

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

Og efternavnet:

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

Begræns antallet af tegn, og tilføj ...

Har du nogensinde ønsket at rydde op i dine metabeskrivelser? Hvis du vil trække indhold til Excel og derefter trimme indholdet til brug i et Meta Description-felt (150 til 160 tegn), kan du gøre det ved hjælp af denne formel fra Mit sted. Det bryder beskrivelsen rent i et rum og tilføjer derefter ...:

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

Selvfølgelig er disse ikke beregnet til at være omfattende ... bare nogle hurtige formler, der hjælper dig med at komme i gang! Hvilke andre formler finder du, du bruger? Tilføj dem i kommentarerne, så giver jeg dig kredit, når jeg opdaterer denne artikel.

Hvad mener du?

Dette websted bruger Akismet til at reducere spam. Lær, hvordan dine kommentardata behandles.