Membersihkan Data Berantakan dengan Rumus Cepat

Nih — kalau kamu sering dapat file spreadsheet dari orang lain dan isinya berantakan (spasi ngasal, angka jadi teks, tanggal acak, nomor HP nggak konsisten), tenang.

Artikel ini bakal ngajarin cara membersihkan data step-by-step pakai rumus cepa di Google Sheets & Excel.

Gaya santai, cocok buat anak remaja, dan lengkap dengan contoh nyata biar kamu bisa copy-paste langsung.

Kita bahas dari hal paling dasar sampai trik pro: TRIM, CLEAN, SUBSTITUTE, REGEX, VALUE, DATEVALUE, SPLIT, TEXTJOIN, UNIQUE, FILTER, Power Query, sampai script sederhana.

Semua contoh nyata + tabel sebelum/ sesudah biar nggak ngebayang doang.

Ringkasan singkat (kalau mau lompat cepat)

• Mulai dengan backup file.

• Gunakan kombinasi: `TRIM + CLEAN + PROPER` untuk nama.

• Pakai `VALUE` / `NUMBERVALUE` / `SUBSTITUTE` untuk ubah teks menjadi angka.

• Konversi tanggal: `DATEVALUE` atau parsing manual.

• Normalisasi nomor telepon: `REGEXREPLACE` atau nested `SUBSTITUTE`.

• Hapus karakter spesial: `REGEXREPLACE(...,"[^0-9A-Za-z ]","")`.

• Gabung kolom: `TEXTJOIN` / `&`.

• Hapus duplikat: `UNIQUE` / Remove Duplicates.

• Otomasi impor & transform: Power Query (Excel) / Apps Script (Sheets).

1. Persiapan sebelum mulai (WAJIB)

Sebelum utak-atik data, lakukan ini:

1. Backup file: `File → Make a copy` atau save as `filename_raw.xlsx`.

2. Buat sheet baru: `raw_data` (asal datangnya), `work` (tempat rumus), `clean` (hasil akhir - paste values).

3. Catat target format setiap kolom: Nama (Text proper), Tanggal (Date), Nomor (Text/Number with leading zeros), Harga (Number), KodeProduk (Text).

4. Jangan edit langsung di `raw_data` — semua transformasi pakai `work` agar gampang rollback.

2. Fungsi dasar yang wajib kamu tahu (1 baris, 1 fungsi)

Ini "toolkit" yang bakal sering dipakai. Tulis rumus di sel B2 misalnya dan tarik ke bawah.

• `TRIM(text)` — hapus spasi ekstra (depan, belakang, ganda).

text
  =TRIM(A2)

• `CLEAN(text)` — hapus karakter non-print (line break dll).

text
  =CLEAN(A2)

• `PROPER(text)` — buat huruf awal tiap kata kapital.

text
  =PROPER(A2)

• `UPPER(text)` / `LOWER(text)` — semua kapital / semua kecil.

text
  =UPPER(A2)
  =LOWER(A2)

• `VALUE(text)` — ubah teks angka jadi number (simple).

text
  =VALUE("123")

• `NUMBERVALUE(text, decimal_separator, group_separator)` — ubah teks angka dengan pemisah lokal.

text
  =NUMBERVALUE("1.234,56", ",", ".")  // Excel

• `DATEVALUE(text)` — ubah tanggal teks jadi serial date.

text
  =DATEVALUE("30/09/2025")

• `LEFT/RIGHT/MID(text, start, length)` — ambil sebagian teks.

text
  =LEFT(A2,3)
  =RIGHT(A2,4)
  =MID(A2,4,2)

3. Kasus 1 — Nama berantakan (spasi, huruf kecil/besar, karakter aneh)

Masalah nyata: Nama datang seperti `" ANDI saputra \n"` atau `"adi-kurniawan"`.

Contoh tabel (raw):

Langkah bersih:

1. Gabung `CLEAN` + `TRIM` untuk buang line break & spasi berlebih.

2. Pakai `PROPER` untuk format kapitalisasi.

3. Jika ada simbol non-alfabet (mis. `-`), replace dulu.

Rumus contoh (Google Sheets / Excel):

text
=PROPER(TRIM(CLEAN(SUBSTITUTE(A2,"-"," "))))

Penjelasan:

• `SUBSTITUTE(A2,"-"," ")` → ganti `-` jadi spasi.

• `CLEAN()` → hapus line breaks `CHAR(10)` atau `CHAR(13)`.

• `TRIM()` → hilangkan spasi berlebih.

• `PROPER()` → bikin huruf depan tiap kata besar.

Hasil (clean):

4. Kasus 2 — Angka disimpan sebagai teks (contoh: harga, jumlah)

Masalah nyata: `Total` berformat `"1.200.000"` atau `"1,200.00"` tetapi dianggap teks → rumus SUM gagal.

Contoh raw:

Langkah bersih:

• Hilangkan simbol non-digit kecuali desimal.

• Konversi ke number dengan `VALUE` / `NUMBERVALUE`.

Rumus umum (Google Sheets):

text
=VALUE(REGEXREPLACE(A2,"[^0-9,.-]",""))

Kemudian jika desimal pakai koma, ubah `,` ke `.`:

text
=VALUE(SUBSTITUTE(REGEXREPLACE(A2,"[^0-9,.-]",""),".",""))

(Catatan: hati-hati perbedaan regional.)

Rumus Excel (nominal rupiah dengan titik sebagai thousand separator)

text
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"Rp ",""),".",""))

Penjelasan:

• `REGEXREPLACE(A2,"[^0-9,.-]","")` → hapus semua kecuali digit, koma, titik, minus.

• `SUBSTITUTE(...,".","")` → hapus titik ribuan sebelum `VALUE`.

Hasil (clean number):

Tips: Setelah convert, set format Number / Currency di cell.

5. Kasus 3 — Tanggal campuran & teks (format beda-beda)

Masalah nyata: Tanggal bisa `2025-09-30`, `30/09/2025`, `Sep 30 2025`, atau `2025.09.30`.

Contoh raw:

Langkah bersih:

• Cek apakah spreadsheet mengenali format.

• Jika tidak, parse manual gunakan `DATE`, `LEFT`, `MID`, `RIGHT`, atau `DATEVALUE`.

• Gunakan `VALUE`/`DATEVALUE` lalu format Date.

Contoh rumus:

1. Format `DD/MM/YYYY` jadi Date:

text
=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))

2. Jika `YYYYMMDD`:

text
=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))

3. Jika format teks `Sep 05, 2025`, gunakan:

text
=DATE(RIGHT(A2,4), MONTH(DATEVALUE(LEFT(A2,3)&" 1")), MID(A2,5,2))

Gunakan IFERROR untuk fallback:

text
=IFERROR(DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)),
         IFERROR(DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)),
                 DATEVALUE(A2)))

Hasil:

Setelah jadi Date, ubah format tampilan ke `dd-mmm-yyyy` atau sesuai kebutuhan.

6. Kasus 4 — Menghapus karakter tak terlihat (newline, tab, non-breaking space)

Masalah nyata:Sel terlihat kosong atau berantakan karena ada `char(160)` (non-breaking space) atau `char(10)` (line break).

Contoh raw:

Cara bersih:

• Gunakan kombinasi `SUBSTITUTE`, `CLEAN`, `TRIM`.

Rumus:

text
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

• `CHAR(160)` = non-breaking space.

• `CLEAN` hapus karakter non-print.

• `TRIM` buang spasi ekstra.

Catatan:Di Google Sheets, `CHAR(160)` juga tersedia.

7. Kasus 5 — Pisah kolom gabungan (Fullname → First & Last), alamat, CSV dalam satu sel

Masalah nyata: Data import dikemas jadi satu sel: `Andi Saputra;Jakarta;0812345`.

Contoh raw:

Cara bersih:

• Gunakan `SPLIT` (Google Sheets) atau `Text to Columns` (Excel).

• Jika delimiter beda-beda, bisa lakukan `REGEXEXTRACT` atau nested `SUBSTITUTE`.

Google Sheets:

text
=SPLIT(A2, ";")

Jika ada beberapa delimiter dan kamu ingin normalisasi dulu:

text
=TRIM(SPLIT(REGEXREPLACE(A2, "[;|,]", ";"), ";"))

Excel (Text to Columns):

1. Pilih kolom → `Data` → `Text to Columns`.

2. Pilih `Delimited` → Next → pilih delimiter (`;` atau `,`) → Finish.

Setelah split, bersihkan tiap kolom pakai `TRIM/CLEAN/PROPER`.

8. Kasus 6 — Menggabungkan kolom menjadi satu format rapih (alamat lengkap)

Masalah nyata: Ingin gabung `Street`, `City`, `Postal` jadi `Alamat Lengkap`.

Contoh raw:

Rumus:

text
=TEXTJOIN(", ", TRUE, TRIM(A2), TRIM(B2), TRIM(C2))

Atau:

text
=A2 & ", " & B2 & " " & C2

Catatan: `TEXTJOIN` lebih rapi, `TRUE` akan ignore sel kosong.

9. Kasus 7 — Nomor telepon beda-beda format; standarkan ke format internasional (contoh: 62xxxx)

Masalah nyata:Ada `0812-345-678`, `+62 812 345 678`, `(0812)345678` dll.

Contoh raw:

Langkah bersih:

1. Hapus semua non-digit dengan `REGEXREPLACE` (Sheets) atau nested `SUBSTITUTE` (Excel).

2. Standarkan leading zero: jika mulai `0` → ganti jadi `62` (`Indonesia`).

Google Sheets rumus:

text
=LET(raw, REGEXREPLACE(A2,"[^0-9]",""),
     IF(LEFT(raw,1)="0", "62"&RIGHT(raw, LEN(raw)-1), IF(LEFT(raw,2)="62", raw, raw)))

Kalau `LET` nggak tersedia, pakai:

text
=IF(LEFT(REGEXREPLACE(A2,"[^0-9]",""),1)="0", "62"&RIGHT(REGEXREPLACE(A2,"[^0-9]",""), LEN(REGEXREPLACE(A2,"[^0-9]",""))-1), REGEXREPLACE(A2,"[^0-9]",""))

Excel (tanpa regex):

text
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"(",""),")")

text
=IF(LEFT(B2,1)="0", "62"&RIGHT(B2,LEN(B2)-1), B2)

Hasil Clean:

Tambahan: Jika mau format presentable `+62 812-345-678`, gunakan kombinasi `MID` & `TEXT` atau rumus custom.

10. Kasus 8 — Hapus duplikat & tandai duplikat (duplicate detection)

Masalah nyata: Data digabung dari beberapa sumber → ada baris sama.

Contoh raw:

Cara tanda duplikat:

text
=IF(COUNTIF($A$2:$A$100, A2)>1, "Duplikat", "")

Hapus duplikat (Google Sheets):

• `=UNIQUE(A2:A)` → hasil tanpa duplikat.

Hapus duplikat (Excel):

• Pilih range → `Data` → `Remove Duplicates`.

Catatan: Hapus duplikat per-kolom atau per-multi-kolom (kombinasi kolom). Untuk multi-kolom, buat helper kolom:

text
=A2 & "|" & B2 & "|" & C2

Lalu `Remove Duplicates` berdasarkan helper.

11. Kasus 9 — Normalisasi kode produk / leading zeros (kode '00123' harus tetap '00123')

Masalah nyata: Excel otomatis bikin `00123` jadi `123`.

Solusi:

• Simpan kolom sebagai Text sebelum paste data.

• Atau di formula: tambahkan leading zeros:

text
=TEXT(A2,"00000")   // pastikan 5 digit

atau

text
=RIGHT("00000"&A2,5)

Contoh:

`A2=123` → `=RIGHT("00000"&A2,5)` → `00123`.

12. Kasus 10 — Mengecek apakah kolom berisi angka atau teks (validasi cepat)

Gunakan `ISNUMBER`:

text
=ISNUMBER(A2)

Jika `FALSE` berarti teks; kalau angka perlu konversi.

Kombinasi untuk flag:

text
=IF(NOT(ISNUMBER(A2)), "Perlu konversi", "")

13. Kasus 11 — Membersihkan kolom alamat e-mail & validasi sederhana

Masalah nyata: Email ada spasi, uppercase, atau invalid.

Bersihkan:

text
=LOWER(TRIM(A2))

Validasi sederhana (regex di Sheets):

text
=REGEXMATCH(A2, "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$")

Jika `TRUE` valid, `FALSE` invalid.

Excel (tanpa regex) pakai kombinasi `FIND` & `ISNUMBER` (lebih rumit) atau gunakan VBA.

14. Kasus 12 — Menghapus baris yang mengandung kata tertentu (mis. "test" atau "dummy")

Gunakan `FILTER` untuk seleksi yang **tidak** mengandung kata tersebut dan hasilkan tabel baru.

Sheets:

text
=FILTER(A2:D, NOT(REGEXMATCH(LOWER(B2:B),"test|dummy")))

Excel 365:

text
=FILTER(A2:D100, NOT(ISNUMBER(SEARCH({"test","dummy"},B2:B100))))

(Butuh sedikit penyesuaian untuk array search.)

15. Workflow Pembersihan Lengkap (contoh step-by-step proyek)

Kita buat workflow nyata: Dataset transaksi messy. Goal: buat laporan bersih dan pivot.

Raw sheet berisi 10.000 baris: `Tanggal, Pembeli, Produk, Qty, Harga, Total, Telepon, Kota`.

Step A: Backup

• Save copy `transactions_raw_2025.xlsx`.

Step B: Normalisasi kolom text

• `NamaClean = PROPER(TRIM(CLEAN(NamaRaw)))`

• `ProdukClean = PROPER(TRIM(ProdukRaw)))`

• `Kota = PROPER(TRIM(KotaRaw))`

Step C: Normalisasi angka

• `QtyClean = VALUE(QtyRaw)` (pakai NUMBERVALUE kalau ada comma)

• `HargaClean = VALUE(REGEXREPLACE(HargaRaw,"[^0-9,.-]",""))`

• `TotalCalc = QtyClean * HargaClean` → bandingkan dengan kolom TotalRaw; jika mismatch → flag

text
=IF(ABS(TotalCalc - VALUE(REGEXREPLACE(TotalRaw,"[^0-9,.-]","")))>1, "Check", "")

Step D: Tanggal

• `DateClean = IFERROR(DATEVALUE(TanggalRaw), parse manual...)`

Step E: Nomor telp

• `PhoneClean = IF(LEFT(REGEXREPLACE(TelpRaw,"[^0-9]",""),1)="0","62"&RIGHT(...,LEN(...)-1), REGEXREPLACE(...))`

Step F: Hapus duplikat

• `Remove Duplicates` berdasarkan `Nama+Tanggal+Total` atau pakai `UNIQUE`.

Step G: Validasi akhir

• Cek `ISNUMBER` untuk angka, cek `LEN(PhoneClean)` antara 10–15, cek email pakai regex.

• Tulis rumus flag untuk tiap baris.

Step H: Finalize & paste values

• Setelah semua OK: copy `work` → Paste Special → Values ke sheet `clean`.

• Buat pivot/summary di sheet `Report`.

16. Tools lanjutan untuk data besar / berulang

Power Query (Excel)

Power Query adalah tool transform yang direkam sebagai step. Cocok kalau data impor reguler.

Alur singkat:

1. `Data` → `From Table/Range` → buka Power Query Editor.

2. Steps: `Trim`, `Clean`, `Replace Values`, `Split Column`, `Change Type`, `Remove Duplicates`.

3. `Close & Load` → data bersih muncul di sheet baru.

4. Tinggal klik `Refresh` kalau data sumber berubah.

Power Query merekam semua step sehingga reproducible.

Google Apps Script (Sheets)

Jika pembersihan butuh automasi kompleks (mis. replace thousands of rows, normalisasi phone, auto-run on import), buat script:

Contoh sederhana (bersihkan phone):

javascript
function cleanPhones() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("raw");
  var values = sheet.getRange("A2:A" + sheet.getLastRow()).getValues();
  for (var i=0;i

VBA (Excel)

Gunakan VBA untuk validasi otomatis saat save atau edit. Contoh: loop rows, apply `Trim`, `Replace` dan simpan hasil ke kolom baru.

17. Contoh Proyek Lengkap (Import CSV & Clean)

Skenario: Kamu dapat CSV ekspor dari marketplace. Kolom raw: `order_id, date, buyerName, buyerPhone, product, qty, price, total`.

Langkah implementasi (Sheets):

1. `File → Import` → masukkan CSV ke sheet `raw`.

2. `work!A2`:

text
=ARRAYFORMULA(IF(ROW(raw!A2:A)=1,"order_id_clean", / else /
  / kolom-kolom transform, mis. /
  PROPER(TRIM(CLEAN(raw!C2:C))) ))

3. Gunakan `ARRAYFORMULA` untuk apply rumus ke seluruh column (sangat hemat waktu).

4. Setelah semua kolom transform selesai, buat `=UNIQUE(FILTER(...))` atau pivot.

Catatan: `ARRAYFORMULA` mempermudah transform ribuan baris tanpa drag rumus.

18. Checklist Validasi Akhir (sebelum report)

Pastikan:

• [ ] Semua kolom penting tidak kosong (ISBLANK checks).

• [ ] Semua angka benar tipe number (ISNUMBER).

• [ ] Tanggal valid (ISDATE atau check via `DATEVALUE`).

• [ ] Nomor telepon 10-15 digit sesuai aturan.

• [ ] Email valid lewat regex.

• [ ] Duplikat sudah ditangani (UNIQUE / Remove Duplicates).

• [ ] Semua rumus sudah di-convert ke values bila perlu dibagikan.

Rumus contoh flag untuk banyak hal:

text
=IF(OR(ISBLANK(A2), NOT(ISNUMBER(B2)), LEN(C2)<10), "Periksa", "OK")

19. Kesalahan umum & solusinya (quick debug)

• Angka masih teks setelah VALUE?→ mungkin ada karakter tersembunyi, pakai `REGEXREPLACE` dulu.

• DATEVALUE error→ cek format; coba parse manual.

• REGEXREPLACE tidak tersedia (Excel) → gunakan nested `SUBSTITUTE` untuk karakter spesifik.

• Hasil rumus tumpah (spill) menimpa data → pindahkan rumus ke sheet kosong.

• Performance lemot (sheet besar + banyak regex) → gunakan Power Query / script untuk batch processing.

20. Latihan Mandiri (praktik 3 proyek)

1. Daftar Kontak — normalisasi nomor telepon ke format `62xxxxxxxx`, hapus duplikat, simpan sebagai `contacts_clean`.

2. Transaksi Marketplace — ubah `Price` dari `"Rp 1.200.000"` jadi angka, hitung `Total = Qty*Price`, tandai baris mismatch.

3. Daftar Siswa — bersihkan nama, pisah full name ke first/last, konversi tanggal lahir, tandai nilai < KK.

21. Penutup & motivasi singkat

Membersihkan data kadang ngebosenin, tapi sesungguhnya ini skill super useful.

Sekali kamu ngerti trik TRIM, SUBSTITUTE, REGEX, VALUE, DATEVALUE, dan workflow yang rapi (raw → work → clean), kamu bakal ngerjain tugas yang dulunya makan jam jadi cuma beberapa menit.

Mulai dari dataset kecil dulu; praktekkan setiap kasus di atas. Biar nempel, ulangi sampai kamu bisa buat template pembersihan sendiri.

Nanti kalau dapat file acak lagi, kamu tinggal klik, paste, dan... voila: data rapi siap dipakai!

Selamat praktek — dan semoga spreadsheet-mu jadi jauh lebih bersih, konsisten, dan siap buat analisis.