Algengar gagnahreinsunarformúlur í Excel

skara fram úr formúlum

Í mörg ár hef ég notað útgáfuna sem úrræði til að lýsa ekki bara hvernig á að gera hlutina, heldur til að halda skrá fyrir mig til að fletta upp seinna! Í dag fengum við viðskiptavin sem afhenti okkur gagnaskrá viðskiptavina sem var hörmung. Nánast hvert svið var rangt sniðið og; vegna þess að við gátum ekki flutt gögnin inn. Þó að það séu frábær viðbót fyrir Excel til að gera hreinsunina með Visual Basic, keyrum við Office fyrir Mac sem styður ekki fjölva. Í staðinn leitum við að beinum formúlum til að aðstoða. Ég hélt að ég myndi deila nokkrum þeirra hérna bara svo aðrir gætu notað þá.

Fjarlægðu stafi sem ekki eru tölulegar

Kerfi krefjast þess oft að símanúmer séu sett inn í ákveðna 11 stafa formúlu með landsnúmerinu og engin greinarmerki. Hins vegar slær fólk inn þessum gögnum oft með strikum og tímabilum í staðinn. Hér er frábær uppskrift fyrir fjarlægja alla ótölulegu stafi í Excel. Formúlan fer yfir gögnin í klefi 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ú er hægt að afrita dálkinn sem myndast og nota Breyta> Líma gildi að skrifa yfir gögnin með rétt sniðinni niðurstöðu.

Metið marga reiti með OR

Við hreinsum oft ófullnægjandi skrár frá innflutningi. Notendur gera sér ekki grein fyrir því að þú þarft ekki alltaf að skrifa flóknar stigveldisformúlur og að þú getur skrifað OR staðhæfingu í staðinn. Í þessu dæmi hér að neðan vil ég athuga A2, B2, C2, D2 eða E2 fyrir gögn sem vantar. Ef einhver gögn vantar ætla ég að skila 0, annars 1. sem gerir mér kleift að raða í gögnin og eyða skrám sem eru ófullnægjandi.

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

Snyrta og sameina reiti

Ef gögnin þín eru með fornafni og eftirnafnsreit, en innflutningurinn þinn er með fullt nafnreit, getur þú sameinað reitina snyrtilega með því að nota innbyggða Excel-aðgerð samtengingu, en vertu viss um að nota TRIM til að fjarlægja öll tóm rými fyrir eða eftir texti. Við vöfum allan reitinn með TRIM ef einhver reitanna hefur ekki gögn:

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

Leitaðu eftir gilt netfang

Nokkuð einföld formúla sem leitar bæði að @ og. í netfangi:

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

Dragðu fram fornafn og eftirnafn

Stundum er vandamálið hið gagnstæða. Gögnin þín hafa fullt nafn reit en þú þarft að flokka fornafn og eftirnafn. Þessar formúlur leita að bilinu milli fornafns og eftirnafns og grípa texta þar sem þess er þörf. ÞAÐ annast einnig ef það er ekkert eftirnafn eða það er autt færsla í A2.

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

Og eftirnafnið:

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

Takmarkaðu fjölda persóna og bættu við ...

Vildir þú einhvern tíma hreinsa metalýsingar þínar? Ef þú vildir draga efni í Excel og snyrta síðan innihaldið til notkunar í Meta Description reit (150 til 160 stafir), getur þú gert það með því að nota þessa formúlu frá Bletturinn minn. Það brýtur lýsinguna hreint á rými og bætir síðan við…:

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

Auðvitað, þetta er ekki ætlað að vera yfirgripsmikið ... bara nokkrar fljótar formúlur til að hjálpa þér að byrja! Hvaða aðrar formúlur finnur þú sjálfur? Bættu þeim við í athugasemdunum og ég gef þér kredit þegar ég uppfæra þessa grein.

Hvað finnst þér?

Þessi síða notar Akismet til að draga úr ruslpósti. Lærðu hvernig ummæli þín eru unnin.