Belajar Excel: Fungsi Teks untuk Membersihkan Data (TRIM, PROPER, LEFT, MID)
Dalam manajemen data profesional, kita mengenal prinsip 5R (Ringkas, Rapi, Resik, Rawat, Rajin). Sebelum data bisa diolah menjadi Pivot Table atau Dashboard yang akurat, data harus melewati “Data Pipeline”. Tahap pertama yang paling krusial adalah Resik.
Masalah terbesar saat belajar excel untuk mengolah data bukan pada rumus yang rumit, melainkan pada data yang “kotor”. Nama pelanggan dengan spasi berlebih, NIK yang tercampur dengan teks, atau data yang harus digabungkan dari beberapa kolom. Fungsi teks adalah “pisau bedah” Anda untuk merapikannya dalam hitungan detik.
🎯 Masalah Nyata
Ibu Ani memiliki data pelanggan dari marketplace: " agus setiawan " (spasi berlebih) dan "BUDI WIBOWO" (huruf kapital semua). Ia ingin semua nama menjadi rapi: "Agus Setiawan". Selain itu, Pak Agus perlu mengambil tanggal lahir dari NIK 16 digit (contoh: 3041102219860503).
📊 Contoh Sebelum & Sesudah
😵 SEBELUM (Data Kotor)
| A | |
|---|---|
| 1 | Data Kotor |
| 2 | agus setiawan |
| 3 | BUDI WIBOWO |
| 4 | sITI nUR |
| 5 | ahmad fauzi |
Masalah: Spasi berlebih, huruf tidak konsisten. Sulit untuk analisis atau pencetakan label.
✅ SESUDAH (Data Bersih)
| A | B | |
|---|---|---|
| 1 | Data Kotor | Hasil Bersih |
| 2 | agus setiawan | Agus Setiawan |
| 3 | BUDI WIBOWO | Budi Wibowo |
| 4 | sITI nUR | Siti Nur |
| 5 | ahmad fauzi | Ahmad Fauzi |
Hasil: TRIM menghapus spasi berlebih, PROPER mengubah huruf pertama menjadi kapital. Data siap pakai!
🛠️ Langkah 1: TRIM & PROPER (Membersihkan Nama)
Ini adalah kombinasi paling dasar dan paling sering digunakan untuk membersihkan data teks.
- Di sel B2, ketik rumus:
=PROPER(TRIM(A2)) - Tekan Enter.
- Copy rumus ke bawah (drag fill handle atau double-click sudut kanan bawah sel).
TRIM(A2)= hapus semua spasi di awal, akhir, dan spasi berlebih di tengah teks.PROPER(...)= ubah huruf pertama setiap kata menjadi kapital, sisanya huruf kecil.- Urutan penting: TRIM dulu, baru PROPER. Jika dibalik, hasilnya tidak optimal.
👁️ FASE TIRU: Bedah Bawang (Membaca dari Dalam ke Luar)
Tabel di atas menunjukkan hasil akhirnya. Sekarang mari kita Tiru proses Excel membaca rumusnya lapis demi lapis.
Lapis 1 (Dalam): Mengusir Spasi
| ␣ | ␣ | a | g | u | s | ␣ | ␣ | ␣ | s | e | t | i | a | w | a | n | ␣ | ␣ |
| a | g | u | s | ␣ | s | e | t | i | a | w | a | n |
Spasi tepi dibuang · spasi ganda di tengah dirapatkan menjadi satu (hijau).
Lapis 2 (Luar): Memakaikan Pakaian Resmi
| a | g | u | s | ␣ | s | e | t | i | a | w | a | n |
| A | g | u | s | ␣ | S | e | t | i | a | w | a | n |
Hanya huruf pertama tiap kata yang dikapitalkan — sisanya dirapikan kecil semua.
🧠 FASE MODIFIKASI: Jebakan Batman (Spasi Hantu)
Di dunia nyata, data copy-paste dari website sering membawa spasi hantu (Kode ASCII 160). Secara visual ia terlihat seperti spasi biasa, tetapi TRIM gagal menghapusnya!
| A | B | |
|---|---|---|
| 2 | ␣␣agus setiawan␣␣ | Agus Setiawan |
💡 Modifikasi: Tambahkan lapis SUBSTITUTE untuk menghancurkan hantu sebelum di-TRIM.
🛠️ Langkah 2: LEFT, MID, RIGHT (Mengambil Sebagian Teks)
Fungsi ini sangat berguna untuk mengambil bagian tertentu dari teks panjang, seperti NIK, nomor telepon, atau kode produk.
Contoh: Mengambil Data dari NIK 16 Digit
| A | B | C | |
|---|---|---|---|
| 1 | NIK | Tanggal Lahir | 4 Digit Terakhir |
| 2 | 3041102219860503 | 19860503 | 0503 |
| 3 | 1120855419920110 | 19920110 | 0110 |
Rumus: Kolom B: =MID(A2,7,8) (ambil 8 karakter dari posisi ke-7). Kolom C: =RIGHT(A2,4) (ambil 4 karakter terakhir).
👁️ FASE TIRU: Bedah Bawang NIK
Mari kita Tiru cara Excel mengekstrak potongan NIK di atas menggunakan Formula Bar.
Mengambil 8 Karakter di Tengah (MID)
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 |
| 3 | 0 | 4 | 1 | 1 | 0 | 2 | 2 | 1 | 9 | 8 | 6 | 0 | 5 | 0 | 3 |
🔵 =LEFT(A2,6) kode wilayah · 🟢 =MID(A2,7,8) tanggal lahir · 🟠 =RIGHT(A2,4) nomor unik.
🧠 FASE MODIFIKASI 1: Jebakan Batman (Angka Berkedok Teks)
Perhatikan hasil MID di atas. Meskipun isinya angka, Excel menganggapnya sebagai Teks (cirinya: rata kiri). Jika data ini Anda VLOOKUP ke tabel angka, Excel akan menolaknya.
| A | B | |
|---|---|---|
| 2 | 3041102219860503 | 22198605 |
💡 Modifikasi: Bungkus dengan VALUE() (atau kalikan *1) agar teks dipaksa menjadi Angka Murni (rata kanan).
🧠 FASE MODIFIKASI 2: Kode Rahasia Jenis Kelamin di NIK
Pada NIK 16 digit, posisi 7–12 memang tanggal lahir (DDMMYY) — tetapi khusus perempuan, tanggal lahir ditambah 40. Artinya, bila dua digit tanggal diawali angka 4, 5, atau 6, pemilik KTP adalah perempuan, dan tanggal lahir aslinya adalah nilai tersebut dikurangi 40.
| A (NIK) | B (Tanggal Asli) | |
|---|---|---|
| 2 | 3507152205860003 | 22 |
| 3 | 3507156205860004 | 22 (62−40) |
💡 Rumus MOD(...,40) adalah jurus ringkasnya: sisa pembagian 40 otomatis mengembalikan 62 → 22, sementara 22 tetap 22. Satu rumus untuk dua gender.
Jika Anda menggunakan Excel versi terbaru, Anda tidak perlu lagi pusing menghitung posisi karakter dengan MID atau LEFT. Gunakan fungsi ajaib =TEXTSPLIT(A2, "-"). Excel akan otomatis memisahkan teks ke beberapa kolom berdasarkan tanda hubung (fitur spilling). Jauh lebih praktis dan anti-salah hitung!
👁️ FASE TIRU: Keajaiban TEXTSPLIT (Excel 365/2021)
Lihat bagaimana satu rumus langsung “tumpah” (spill) ke tiga kolom secara otomatis, tanpa perlu menghitung posisi karakter satu per satu.
| A (Data Asli) | B (Kolom 1) | C (Kolom 2) | D (Kolom 3) | |
|---|---|---|---|---|
| 2 | JKT-089-SALES | JKT | 089 | SALES |
💡 Cukup tentukan pemisahnya (“-“), Excel akan otomatis memisahkannya ke sel sebelahnya. Anti-salah hitung!
🪄 Senjata Rahasia: Flash Fill (Ctrl + E)
Kadang Anda tidak perlu rumus sama sekali. Excel memiliki kecerdasan buatan sederhana yang bisa menebak pola yang Anda inginkan. Ini adalah cara tercepat untuk tahap Resik.
Contoh: Memisahkan Nama dan Gelar
| A | B | |
|---|---|---|
| 1 | Data Asli | Nama Saja (Ketik Manual) |
| 2 | Dr. Budi Wibowo, Sp.A | Budi Wibowo |
| 3 | Prof. Siti Nur, M.Sc | (Tekan Ctrl+E di sini) |
| 4 | dr. Ahmad Fauzi |
Caranya: Ketik manual “Budi Wibowo” di B2, tekan Enter. Lalu klik B3 dan tekan Ctrl + E. Excel akan otomatis mengisi sisa baris mengikuti pola yang Anda contohkan!
👁️ FASE TIRU: Cara Kerja Flash Fill
Excel tidak menggunakan rumus di sini, melainkan mengenali pola yang Anda ketik secara manual.
| A (Data Asli) | B (Nama Saja) | |
|---|---|---|
| 1 | Dr. Budi Wibowo, Sp.A | Budi Wibowo (Ketik Manual) |
| 2 | Prof. Siti Nur, M.Sc | Siti Nur (Tekan Ctrl+E) |
| 3 | dr. Ahmad Fauzi | Ahmad Fauzi |
💡 Flash Fill sangat cepat, tapi ingat: hasilnya adalah teks statis. Jika data di kolom A berubah, kolom B tidak akan otomatis ikut berubah.
🛠️ Langkah 3: TEXTJOIN (Menggabungkan Data)
Jika Anda memiliki nama depan, tengah, dan belakang di kolom terpisah, TEXTJOIN adalah cara modern untuk menggabungkannya, menggantikan fungsi CONCATENATE yang kuno.
Contoh: Menggabungkan Nama Depan, Tengah, Belakang
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Depan | Tengah | Belakang | Nama Lengkap |
| 2 | Andi | Santoso | Andi Santoso | |
| 3 | Dewi | Pratiwi | Kusuma | Dewi Pratiwi Kusuma |
Rumus di D2: =TEXTJOIN(" ", TRUE, A2:C2). Perhatikan: sel kosong (B2) otomatis diabaikan!
- Parameter 1 (” “) = pemisah (spasi). Bisa diganti dengan koma (“, “), strip (” – “), dll.
- Parameter 2 (TRUE) = abaikan sel kosong. Jika FALSE, sel kosong tetap dihitung (hasilnya ada spasi ganda).
- Parameter 3 (A2:C2) = range yang akan digabungkan.
👁️ FASE TIRU: Keunggulan TEXTJOIN vs CONCATENATE
Perhatikan bagaimana TEXTJOIN secara otomatis mengabaikan sel kosong (B2) sehingga tidak menghasilkan spasi ganda yang aneh (“Andi Santoso”).
| A (Depan) | B (Tengah) | C (Belakang) | D (Hasil) | |
|---|---|---|---|---|
| 2 | Andi | (Kosong) | Santoso | Andi Santoso |
| 3 | Dewi | Pratiwi | Kusuma | Dewi Pratiwi Kusuma |
💡 Argumen TRUE pada TEXTJOIN adalah kunci agar spasi di antara “Andi” dan “Santoso” tetap rapi dan tunggal.
🚀 Langkah Selanjutnya dalam Data Pipeline
Data Anda sekarang sudah Resik dari spasi liar dan format yang berantakan. Namun, agar Excel bisa mengenali data ini sebagai database yang dinamis (sehingga rumus dan Pivot Table tidak perlu di-setting ulang setiap ada data baru), langkah berikutnya adalah membuatnya Rapi dengan mengubahnya menjadi Format as Table (Ctrl + T).