Algemiene formule foar gegevensopruiming yn Excel

excel formules

Jierrenlang haw ik de publikaasje brûkt as boarne om net gewoan te beskriuwen hoe dingen te dwaan, mar om ek in rekord te hâlden foar mysels om letter op te sykjen! Hjoed hienen wy in kliïnt dy't ús in klantgegevensbestân oerlevere dat in ramp wie. Praktysk elk fjild waard ferkeard opmakke en; as resultaat koenen wy de gegevens net ymportearje. Wylst d'r in pear geweldige tafoegings binne foar Excel om de oproming te dwaan mei Visual Basic, rinne wy ​​Office foar Mac dy't makro's net stypje. Ynstee sykje wy nei rjochte formules om te helpen. Ik tocht dat ik hjir guon fan soe diele, sadat oaren se kinne brûke.

Net-numerike tekens fuortsmite

Systemen fereaskje faak dat tillefoannûmers moatte wurde ynfoege yn in spesifike 11-sifers formule mei de lânkoade en gjin ynterpunksje. Minsken jouwe dizze gegevens lykwols faak yn mei streepkes en perioaden. Hjir is in geweldige formule foar fuortsmite alle net-numerike tekens yn Excel. De formule beoardielet de gegevens yn sel 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))

No kinne jo de resultearjende kolom kopiearje en brûke Bewurkje> Wearden plakke om de gegevens oer te skriuwen mei it goed opmakke resultaat.

Evaluearje meardere fjilden mei in OR

Wy reinigje faaks ûnfolsleine records fan in ymport. Brûkers beseffe har net dat jo net altyd komplekse hiërargyske formules hoege te skriuwen en dat jo ynstee in OR-ferklearring kinne skriuwe. Yn dit foarbyld hjirûnder wol ik A2, B2, C2, D2, of E2 kontrolearje op ûntbrekkende gegevens. As der gegevens ûntbrekken, sil ik in 0 werombringe, oars in 1. Dat sil my tastean de gegevens te oarderjen en de records dy't net folslein binne te wiskjen.

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

Trimmen en gearfoegje fjilden

As jo ​​gegevens in fjirde- en efternamme-fjilden hawwe, mar jo ymport in fjild mei folsleine namme hat, kinne jo de fjilden mei-inoar ferbine mei de ynboude Excel-funksje Concatenate, mar wês wis dat jo TRIM brûke om lege spaasjes te ferwiderjen foar of nei de tekst. Wy wikkelje it heule fjild yn mei TRIM yn it gefal dat ien fan 'e fjilden gjin gegevens hat:

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

Kontrolearje op jildich e-postadres

In frij simpele formule dy't sawol foar de @ en. yn in e-postadres:

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

Extract Foar- en lêste nammen

Somtiden is it probleem it tsjinoerstelde. Jo gegevens hawwe in fjild mei folsleine namme, mar jo moatte de foar- en lêste nammen analysearje. Dizze formules sykje nei de romte tusken de foar- en efternamme en pakke tekst wêr't it nedich is. IT behannelt ek as d'r gjin efternamme is as d'r in lege yngong yn A2 is.

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

En de efternamme:

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

Beheine it oantal karakters en foegje ta ...

Wolle jo jo meta-beskriuwingen ea opromje? As jo ​​ynhâld yn Excel wolle lûke en dan de ynhâld trimme foar gebrûk yn in fjild Meta-beskriuwing (150 oant 160 tekens), kinne jo dat dwaan mei dizze formule fan My Spot, It brekt de beskriuwing op in romte skjin en foeget dan 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)

Fansels binne dizze net bedoeld om wiidweidich te wêzen ... gewoan wat snelle formules om jo te helpen in start te krijen! Hokker oare formules fine jo sels brûke? Foegje se ta yn 'e opmerkingen en ik jou jo kredyt as ik dit artikel bywurkje.

Wat tinksto?

Dizze side brûkt Akismet om spam te ferleegjen. Learje hoe't jo kommentaargegevens ferwurke wurde.