Rumus Excel Untuk Pembersihan Data Umum

Rumus Pembersihan Data Excel

Selama bertahun-tahun, saya telah menggunakan publikasi sebagai sumber daya untuk tidak hanya menjelaskan bagaimana melakukan sesuatu, tetapi juga menyimpan catatan untuk diri saya sendiri untuk dicari nanti! Hari ini, kami memiliki klien yang memberi kami file data pelanggan yang merupakan bencana. Hampir setiap bidang salah format dan; akibatnya, kami tidak dapat mengimpor data. Meskipun ada beberapa add-on yang bagus untuk Excel untuk melakukan pembersihan menggunakan Visual Basic, kami menjalankan Office untuk Mac yang tidak mendukung makro. Sebaliknya, kami mencari formula langsung untuk membantu. Saya pikir saya akan membagikan beberapa di sini agar orang lain dapat menggunakannya.

Hapus Karakter Non-Numerik

Sistem sering kali mengharuskan nomor telepon dimasukkan dalam formula 11 digit tertentu dengan kode negara dan tanpa tanda baca. Namun, orang sering memasukkan data ini dengan tanda hubung dan titik. Inilah formula yang bagus untuk menghapus semua karakter non-numerik di Excel. Rumusnya meninjau data di 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))

Sekarang Anda dapat menyalin kolom yang dihasilkan dan menggunakan Edit> Tempel Nilai untuk menimpa data dengan hasil yang diformat dengan benar.

Evaluasi Beberapa Bidang dengan ATAU

Kami sering membersihkan catatan yang tidak lengkap dari impor. Pengguna tidak menyadari bahwa Anda tidak selalu harus menulis rumus hierarki yang rumit dan Anda dapat menulis pernyataan OR sebagai gantinya. Dalam contoh di bawah ini, saya ingin memeriksa A2, B2, C2, D2, atau E2 untuk data yang hilang. Jika ada data yang hilang, saya akan mengembalikan 0, jika tidak 1. Itu akan memungkinkan saya untuk mengurutkan urutan data dan menghapus catatan yang tidak lengkap.

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

Pangkas dan Gabungkan Bidang

Jika data Anda memiliki bidang Nama Depan dan Belakang, tetapi impor Anda memiliki bidang nama lengkap, Anda dapat menggabungkan bidang bersama-sama dengan rapi menggunakan Penggabungan Fungsi Excel bawaan, tetapi pastikan untuk menggunakan TRIM untuk menghapus spasi kosong sebelum atau sesudah teks. Kami membungkus seluruh bidang dengan TRIM jika salah satu bidang tidak memiliki data:

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

Periksa Alamat Email yang Valid

Rumus yang cukup sederhana yang mencari @ dan. di alamat email:

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

Ekstrak Nama Depan dan Belakang

Terkadang, masalahnya adalah sebaliknya. Data Anda memiliki bidang nama lengkap tetapi Anda perlu menguraikan nama depan dan belakang. Rumus ini mencari ruang antara nama depan dan belakang dan mengambil teks jika perlu. IT juga menangani jika tidak ada nama belakang atau ada entri kosong di A2.

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

Dan nama terakhir:

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

Batasi Jumlah Karakter dan Tambahkan…

Apakah Anda pernah ingin membersihkan deskripsi meta Anda? Jika Anda ingin menarik konten ke Excel dan kemudian memangkas konten untuk digunakan dalam bidang Deskripsi Meta (150 hingga 160 karakter), Anda dapat melakukannya menggunakan rumus ini dari Tempat saya. Ini dengan rapi memecah deskripsi di suatu tempat dan kemudian menambahkan…:

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

Tentu saja, ini tidak dimaksudkan untuk menjadi komprehensif… hanya beberapa formula cepat untuk membantu Anda memulai! Apa rumus lain yang Anda gunakan? Tambahkan mereka di komentar dan saya akan memberi Anda kredit saat saya memperbarui artikel ini.

Bagaimana menurut Anda?

Situs ini menggunakan Akismet untuk mengurangi spam. Pelajari bagaimana data komentar Anda diproses.