Excel -formler for opprydding av vanlige data

Formler for dataopprydding i Excel

I årevis har jeg brukt publikasjonen som en ressurs for ikke bare å beskrive hvordan jeg gjør ting, men også å føre en oversikt over meg selv for å se opp senere! I dag hadde vi en klient som ga oss en kundedatafil som var en katastrofe. Så å si hvert felt var feilformatert og; som et resultat kunne vi ikke importere dataene. Selv om det er noen gode tillegg for Excel for å gjøre oppryddingen ved hjelp av Visual Basic, kjører vi Office for Mac som ikke støtter makroer. I stedet ser vi etter rette formler for å hjelpe. Jeg trodde jeg skulle dele noen av dem her bare slik at andre kan bruke dem.

Fjern ikke-numeriske tegn

Systemer krever ofte at telefonnumre settes inn i en spesifikk 11-sifret formel med landskoden og ingen tegnsetting. Imidlertid skriver folk ofte inn disse dataene med bindestreker og perioder i stedet. Her er en flott formel for fjerne alle ikke-numeriske tegn i Excel. Formelen gjennomgå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))

Nå kan du kopiere den resulterende kolonnen og bruke Rediger> Lim inn verdier å skrive over dataene med riktig formatert resultat.

Evaluer flere felt med en OR

Vi renser ofte ufullstendige poster fra en import. Brukere skjønner ikke at du ikke alltid trenger å skrive komplekse hierarkiske formler, og at du kan skrive en ELLER-setning i stedet. I dette eksemplet nedenfor vil jeg sjekke A2, B2, C2, D2 eller E2 for manglende data. Hvis noen data mangler, skal jeg returnere en 0, ellers en 1. Som vil tillate meg å sortere for dataene og slette oppføringene som er ufullstendige.

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

Trim og sammenkjøringsfelt

Hvis dataene dine har et for- og etternavn-felt, men importen din har et fullt navnefelt, kan du sammenkoble feltene pent ved hjelp av den innebygde Excel-funksjonen, men sørg for å bruke TRIM til å fjerne tomme mellomrom før eller etter tekst. Vi pakker hele feltet med TRIM i tilfelle et av feltene ikke har data:

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

Se etter gyldig e-postadresse

En ganske enkel formel som ser etter både @ og. i en e-postadresse:

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

Pakk ut for- og etternavn

Noen ganger er problemet det motsatte. Dataene dine har et fullt navnefelt, men du må analysere for- og etternavn. Disse formlene ser etter mellomrom mellom for- og etternavn og henter tekst der det er nødvendig. Det håndteres også hvis det ikke er noe etternavn, eller hvis det er en tom oppføring i A2.

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

Og etternavnet:

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

Begrens antall tegn og legg til ...

Har du noen gang ønsket å rydde opp i metabeskrivelsene dine? Hvis du ønsket å hente innhold til Excel og deretter trimme innholdet for bruk i et Meta Description-felt (150 til 160 tegn), kan du gjøre det ved å bruke denne formelen fra Mitt sted. Den bryter beskrivelsen rent i et rom og legger deretter til ...:

=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 ment å være omfattende ... bare noen raske formler som hjelper deg med å komme i gang! Hvilke andre formler bruker du? Legg til dem i kommentarene, så gir jeg deg kreditt når jeg oppdaterer denne artikkelen.

Hva tror du?

Dette nettstedet bruker Akismet for å redusere spam. Lær hvordan kommentaren din behandles.