Rumus Pembersihan Data Umum di Excel

rumus excel

Selama bertahun-tahun, saya telah menggunakan publikasi sebagai sumber 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. Sebagai gantinya, kami mencari rumus lurus 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 ke dalam formula spesifik 11-digit dengan kode negara dan tanpa tanda baca. Namun, orang sering kali memasukkan data ini dengan tanda hubung dan titik. Ini rumus 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 kompleks dan sebagai gantinya Anda dapat menulis pernyataan ATAU. 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 a 1. Itu akan memungkinkan saya untuk mengurutkan 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 justru sebaliknya. Data Anda memiliki kolom nama lengkap tetapi Anda perlu mengurai nama depan dan belakang. Rumus ini mencari spasi 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 rumus cepat untuk membantu Anda memulai dengan lompatan! Rumus lain apa 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.