Bersihkan Data Kotor Sebelum Dihitung: TRIM, VALUE, SUBSTITUTE (Sering Jadi Penyebab Rumus Error)
Membersihkan data hasil salin-tempel: spasi berlebih, huruf tidak seragam, dan angka yang tersimpan sebagai teks.
Rumus benar, hasil salah? Cek datanya.
Kebanyakan "Excel-ku rusak" sebenarnya data kotor: spasi tersembunyi di akhir nama sehingga VLOOKUP gagal, angka bertulisan "Rp 5.000.000" yang bukan angka, huruf besar-kecil campur aduk. Membersihkan data adalah 60% pekerjaan analisis data.
Gejala & obatnya
| Gejala | Penyebab | Obat |
|---|---|---|
| VLOOKUP/COUNTIF tidak menemukan padahal terlihat sama | Spasi di awal/akhir/ganda | TRIM |
| Nama campur "budi SANTOSO" | Huruf tidak seragam | PROPER, UPPER, LOWER |
| SUM menghasilkan 0 pada kolom angka | Angka tersimpan sebagai teks | VALUE |
| "Rp 5.000.000" tidak bisa dijumlah | Mengandung teks dan titik | SUBSTITUTE + VALUE |
| Karakter aneh tak terlihat | Hasil ekspor sistem | CLEAN |
Resep mengubah "Rp 5.000.000" menjadi angka 5000000
=VALUE( SUBSTITUTE( SUBSTITUTE(B2, "Rp ", ""), ".", "" ) )
langkah 1: SUBSTITUTE(B2,"Rp ","") → "5.000.000"
langkah 2: SUBSTITUTE(...,".","") → "5000000"
langkah 3: VALUE(...) → 5000000 (angka sungguhan)Kerjakan dari fungsi paling dalam ke luar. Pecah menjadi langkah-langkah kecil bila rumus terasa rumit.
Rumus bersarang untuk teks
=PROPER(TRIM(A2)) " siti rahma " → "Siti Rahma"Fitur tanpa rumus
- Remove Duplicates (Data → Remove Duplicates) menghapus baris kembar.
- Text to Columns memecah satu kolom berdasarkan pemisah (koma, spasi, strip).
- Find & Replace (Ctrl+H) mengganti teks massal.
Selalu bersihkan data di kolom bantu, lalu salin hasilnya dengan Paste Special → Values.