Pernah mengalami cell yang ada di excell berisi banyak sekali spasi kosong yang mendahului angka disebelahnya? Tidak bisa melakukan formulasi terhadap angka yang terjebak spasi ini? Frustasi? Stres? Jangan dulu... masih banyak jalan menuju surga.. :) misalnya dengan ngetik ulang semua angka-angka yang ada, supaya dikenali sebagai angka yang normal..
Tapi gimana kalo angkanya ada beberapa puluh atau beberapa ratus cell? Mau nyari tembok buat disundul? Nanti dulu gan.. excel masih punya beberapa jurus untuk mengatasi hal ini.. tau fungsi trim kan? Nah ternyata fungsi ini juga ada dan berlaku pada excel..!! trim adalah fungsi untuk menghilangkan spasi kosong berlebih diawal, tengah, dan akhir teks. Fungsi ini menghapus semua spasi diawal dan akhir teks, sedangkan untuk spasi yang berada ditengah teks, hanya akan dihapus duplikatnya saja dan akan menyisakan satu spasi untuk antar kata. Jadi dengan menggunakan fungsi ini, kita bisa membuang spasi kosong tidak berguna tadi.
Misalnya kita memiliki nilai “ 0.75” di cell C7, maka kita bisa mengetikkan formula ini di cell kosong lain: “TRIM(C7)” maka nilai cell baru ini akan menjadi “0.75”.
Lalu bagaimana dengan angka “1 000 000”? dengan menggunakan perintah “TRIM(C7)” permasalahan ini tidak bisa begitu saja diselesaikan. Alternatif untuk masalah ini adalah dengan menggunakan fungsi substitute: “=SUBSTITUTE(C7;" ";"")”. Dengan ini spasi kosong tadi tidak akan menjadi mimpi buruk lagi.
Namun terkadang spasi ini membandel dan tidak mau hilang begitu saja, terutama untuk file excel yang kita dapat dari web dengan format contentnya lebih menyerupai HTML tag daripada kolom pada excel. Percaya atau tidak, spasi yan digunakan disini berbeda dengan spasi yang kita kenal. Pada format HTML, spasi dikenal dengan karakter “ ”. Karakter ini tidak bisa dihilangkan dengan fungsi TRIM saja, melainkan benar-benar harus kita ganti menggunakan fungsi SUBSTITUTE. Untuk lebih amannya fungsi SUBSTITUTE ini akan kita kombinasikan dengan TRIM. Karakter spasi dengan kode HTML “ ” ini memiliki kode ASCII “160” dan akan kita ganti dengan spasi biasa yang memiliki kode ASCII “32”, setelah itu barulah kita TRIM. Karena sudah berurusan dengan kode ASCII, maka kita juga akan menggunakan sedikit fungsi CHAR untuk mengkonversi angka kode ASCII sebagai karakter yang sebenarnya. Contohnya adalah sebagai berikut: “=TRIM(SUBSTITUTE(C7,CHAR(160),CHAR(32)))”.
Dengan fungsi ini, kita jadi bisa menghemat waktu dan tenaga untuk mengetik ulang semua angka yang ada, dan kitapun jadi bisa tidur lebih nyenyak.. :) oke, semoga bermanfaat...
Akhirnya ketemu juga blog yang bahas untuk menghilangkan spasi yang membandel kombinasi trim & subsitute. Thanks a lot, Pak.
ReplyDeletesama-sama pak rahmat.. semoga sukses.. :)
ReplyDeletewhaaaa ini baru hebat, dari kemarin browsing gak ada yang sip..... ini baruuuuu makasih banget yaaaa......
ReplyDeletesya g bisa y pak,, ini contoh datanya no nip: 19550828 198403 1 003
ReplyDeletekalo hrus di betulkan manual akan menyita banyak waktu,, mohon bantuannya,,,
Sama2 gan.. seneng rasanya kalo postingan saya bermanfaat bagi orang lain.. :)
ReplyDeleteMas ade, saya agak belum jelas ga bisanya kenapa, kemungkinan karena format spasi kosong itu tidak sama, sudah dicoba dengan replace, sesuai beberapa langkah di artikel? Mungkin kalau ada contoh file yg ada record kayak gitu bisa dilampirkan.. maaf ya kalau terlambat responnya.. belakangan ini terlalu sibuk dengan kantor sampe ga sempat ngecek blog.. :)
ReplyDeleteKlo ditambahkan lagi rumus Subtitut nya mungkin bisa ya.....
ReplyDelete“=SUBSTITUTE(TRIM(SUBSTITUTE(C7,CHAR(160),CHAR(32)));” “;”")”
di saya bisa ...
misal " B 1234 AJ " jadi "B1234AJ"
Krennn banget infonya, makasih Infonya, sudah di coba dan berhasil. Trimakasih banyak.
ReplyDeletesetelah menggunakan rumus tersebut jika hasilnya di copy paste value, hasil copy paste value tsb akan ada tanda hijaunya di sudut kiri atas tiap sel, kemudian data tersebut belum bisa dijumlahkan sebelum setiap sel harus di klik satu persatu. untuk masalah ini jalan keluarnya bagaimana ya pak, biar tidak perlu klik sel satu persatu
ReplyDeleteterima kasih atas masukannya pak Tri G,
ReplyDeleterumus diatas ini kalau saya lihat sepertinya gabungan dari ketiga rumus sebelumnya dimana pada fungsi tersebut akan mengkonversi char(160)->“ ” menjadi char(32)->" ", lalu melakukan trim whitespase awal dan akhir, kemudian mengkonversi " " menjadi "".
memang pada tulisan saya memecah fungsi tersebut jadi tiga, dan digunakan secara terpisah untuk mempermudah pembaca dalam "membaca" perintah didalamnya.
namun sepertinya cara ini memang efektif bila tidak ingin mengetikkan 3 fungsi sebelumnya secara satu-persatu. :)
sama-sama pak Ramdani (Aguy) :) senang bisa membantu
ReplyDeletesebenarnya asal isi di dalam cell tersebut sudah berupa angka seharusnya bisa dieksekusi sebagaimana biasanya angka (disummary, rata-rata, dan sebagainya).
ReplyDeletenamun bila masih belum bisa, coba diblok dulu semua cell yang bermasalah ini, nanti akan muncul tanda seru disebelah kiri atas cell yang diblok tadi, arahkan mouse kesana (jangan diklik dulu), lalu klik tanda panah kecil (yang menghadap ke bawah) yang muncul disebelah tanda seru tadi, nanti akan keluar menu kecil, pilih "Convert to number".
semoga bisa membantu :)
Betul sekali... mantap ilmunya...
ReplyDeleteFSS13002574
ReplyDeleteKALO INI GIMANA GAN?
Thx
kurang paham nih kondisinya gimana? kalo mau menghilangkan (trim) spasi aja kan cukup dengan perintah2 di atas. dan ini memang khusus untuk menghilangkan spasi agar angkanya bisa dikalkulasi. tapi kalau contoh kasus di atas kan sepertinya agak beda.
ReplyDeletesepertinya kombinasi string seperti itu memang bukan untuk dikalkukasi. dan kalaupun hanya untuk menghilangkan karakter tertentu kan bisa pakai fasilitas "find and replace". atau kalau mau menghilangkan karakter pada posisi tertentu bisa juga pakai fungsi LEFT() atau RIGHT(). ya dicoba saja tergantung kebutuhan, semoga membantu.. :)