Formula Excel Kanggo Pembersihan Data Biasa

Formula Pembersihan Data Excel

Mangtaun-taun, kuring nganggo publikasi salaku sumberdaya pikeun henteu ngan ukur ngajelaskeun kumaha carana ngalakukeun hal-hal, tapi ogé nyimpen catetan pikeun diri kuring pikeun milarian engké! Dinten ayeuna, urang ngagaduhan klién anu masihan kami file data palanggan anu musibah. Ampir unggal lapangan disalahgunakeun sareng; hasilna, urang teu tiasa ngimpor data na. Sanaos aya sababaraha tambahan anu hadé pikeun Excel pikeun ngalakukeun pembersih nganggo Visual Basic, kami ngajalankeun Office for Mac anu moal ngadukung makro. Sabalikna, urang milari formula anu lempeng pikeun ngabantosan. Teu sangka kuring bagikeun sababaraha di dieu pikeun anu sanés tiasa ngagunakeunana.

Cabut Karakter Non-Nomer

Sistem sering meryogikeun nomer telepon kanggo dilebetkeun kana formula khusus 11 digit kalayan kode nagara sareng teu aya tanda baca. Nanging, jalma sering ngasupkeun data ieu ku cara dash sareng période tibatan. Ieu formula anu saé pikeun ngaleungitkeun sadaya karakter non-numerik dina Excel. Formula ulasan data dina sél 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))

Ayeuna anjeun tiasa nyalin kolom anu dihasilkeun sareng dianggo Edit> Nempelkeun Nilai pikeun nulis ngalangkungan data kalayan hasil anu leres diformat.

Meunteun Sababaraha Widang ku OR

Kami sering ngabersihan catetan anu henteu lengkep tina impor. Pamaké henteu sadar yén anjeun henteu kedah nyerat formula hierarkis anu rumit sareng anjeun tiasa nyerat pernyataan ATAU. Dina conto ieu di handap, kuring hoyong parios A2, B2, C2, D2, atanapi E2 pikeun data anu leungit. Upami aya data anu teu aya, abdi badé mulihkeun 0, upami sanésna 1. Éta bakal ngamungkinkeun kuring milah-milah mesen data sareng ngahapus rékor anu henteu lengkep.

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

Motong sareng Ladang Konperasi

Upami data anjeun ngagaduhan bidang Nama Pertama sareng Terakhir, tapi impor anjeun ngagaduhan bidang nami lengkep, anjeun tiasa ngagumpukkeun lapangan babarengan kalayan rapih nganggo built in Excel Function Concatenate, tapi pastikeun nganggo TRIM pikeun miceun rohangan kosong sateuacan atanapi saatos téks. Urang bungkus sadayana lapangan ku TRIM upami salah sahiji lapangan teu gaduh data:

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

Pariksa Alamat Email anu Sah

Formula anu cukup saderhana anu katingalina pikeun @ sareng. dina alamat email:

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

Nimba Ngaran Mimiti sareng Panungtung

Sakapeung, masalahna sabalikna. Data anjeun ngagaduhan bidang nami lengkep tapi anjeun kedah ngurai nami mimiti sareng tukang. Formula ieu milari rohangan antara nami mimiti sareng tukang sareng grab téks mana perlu. IT ogé nanganan upami teu aya nami tukang atanapi aya entri kosong dina A2.

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

Sareng nami tukang:

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

Batesan Jumlah Karakter sareng Tambihkeun…

Naha anjeun kantos hoyong ngabersihkeun pedaran meta anjeun? Upami anjeun hoyong narik kontén kana Excel teras motong konténna kanggo dianggo dina kolom Meta Deskripsi (150 dugi ka 160 karakter), anjeun tiasa ngalakukeun éta nganggo rumus ieu ti Spot kuring. Éta bersih ngarecah katerangan dina rohangan teras nambihan…:

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

Tangtosna, ieu sanés hartosna komprehensif ... ngan ukur sababaraha formula gancang pikeun ngabantosan anjeun ngamimitian luncat! Naon rumus sanés anu anjeun nganggo nganggo? Tambihkeun kana koméntar sareng kuring bakal masihan kiridit nalika kuring ngamutahirkeun tulisan ieu.

Naon anu anjeun pikirkeun?

Situs migunakeun Akismet pikeun ngurangan spam. Diajar kumaha komentar data anjeun diolah.