Menghilangkan spasi (trim whitespace) pada cell excel
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…
Leave a reply to Ramdani Aguy Cancel reply
Author
Punya cita-cita jadi dosen atau guru, tapi sampai saat ini belum kesampean.. :)
Domisili saat ini di Jakarta.
Komentar Terbaru
 | Dedy on formula untuk membandingkan fo… |
| Dedy on formula untuk membandingkan fo… |
| Dedy on formula untuk membandingkan fo… |
 | Tek Roy Sangkaen on Mengganti Separator Desimal da… |
 | Rayu Salim on formula untuk membandingkan fo… |
 | Qie on formula untuk membandingkan fo… |
 | krishnashanti on Menghilangkan spasi (trim whit… |
 | Merlina Gunawan on Native 3G videocall pada … |
| Merlina Gunawan on Native 3G videocall pada … |
 | nude on Mengganti Separator Desimal da… |
QuinBlog 
Akhirnya ketemu juga blog yang bahas untuk menghilangkan spasi yang membandel kombinasi trim & subsitute. Thanks a lot, Pak.
sama-sama pak rahmat.. semoga sukses.. 🙂
whaaaa ini baru hebat, dari kemarin browsing gak ada yang sip….. ini baruuuuu makasih banget yaaaa……
Sama2 gan.. seneng rasanya kalo postingan saya bermanfaat bagi orang lain.. 🙂
sya g bisa y pak,, ini contoh datanya no nip: 19550828 198403 1 003
kalo hrus di betulkan manual akan menyita banyak waktu,, mohon bantuannya,,,
Mas 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.. 🙂
Klo ditambahkan lagi rumus Subtitut nya mungkin bisa ya…..
“=SUBSTITUTE(TRIM(SUBSTITUTE(C7,CHAR(160),CHAR(32)));” “;””)”
di saya bisa …
misal ” B 1234 AJ ” jadi “B1234AJ”
terima kasih atas masukannya pak Tri G,
rumus 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. 🙂
Krennn banget infonya, makasih Infonya, sudah di coba dan berhasil. Trimakasih banyak.
sama-sama pak Ramdani (Aguy) 🙂 senang bisa membantu
setelah 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
sebenarnya asal isi di dalam cell tersebut sudah berupa angka seharusnya bisa dieksekusi sebagaimana biasanya angka (disummary, rata-rata, dan sebagainya).
namun 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…
FSS13002574
KALO 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.
sepertinya 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.. 🙂
trims gan
mantab, hatur nuhun banget kang. membantu banget.
ternyata susah brow saya coba berkali kali dengan kombinasi antara trim dan ubstitute juga tidak bisa pada contoh kasus 19700409 199003 1 006 menjadi 1197004091990031006, tolong don brow dibantu, terima kasih
ah masa?:D saya masih kurang ngerti kondisi pertanyaannya nih.. kalau saya coba copy paste “19700409 199003 1 006” ke excel cell A1 kemudian menggunakan formula ini
di cell A2, maka hasilnya sudah sesuai seperti “1197004091990031006”. mungkin bila ada contoh filenya akan lebih bisa menggambarkan kondisinya.. 🙂
maaf pa saya mau tanya: kalau menghilangkan karakter tertentu misalnya tanda koma pada cell, dengan logikanya seperti ini: “jika ada data maka tanda koma disertakan, sedangkan jika data kosong maka tanda komanya dihilangkan” itu gimana pa!…
misalnya:
Cell A1 bertuliskan “Jakarta,”
cell A2 bertuliskan “,”
saya ingin jika tidak ada tulisan jakartanya, maka komanya juga hilang.
mohon pencerahannya. dan terima kasih atas penjelasannya.
maaf, baru lihat ada pertanyaan.. 🙂
mungkin bisa diakali dengan memeriksa panjang dari teks pada cell tersebut
misalnya data ada pada cell A2, kemudian hasil modifikasi mau kita simpan di cell B2, maka pada cell B2 tersebut kita pasang formula sebagai \berikut:
=IF(LEN(A2)>1,A2,””)
yang artinya, bila panjang karakter dari cell A2 lebih dari 1 karakter (bukan cuma tanda koma) maka cell B2 akan terisi nilai yang sama dengan A2,
bila kondisi tersebut tidak terpenuhi (panjang karakter kurang dari atau sama dengan 1, misalnya pada kondisi isian hanya koma “,”) maka cell B2 akan terisi karakter kosong “”.
semoga jawaban saya sesuai dengan maksud pertanyaannya..
Gan, kalo buat spasi karakter misal: AAABBBCCC menjadi AAA BBB CCC dalam satu cell gemana ya… Kalo numeric, di Format cell; number; custom ada. Mohon pencerahan.. Matur nuwun sanget..
maaf @Zen kelewat baca komennya.. 🙂 kalo di format cell kayaknya belum tau caranya, tapi kalo pake formula bisa coba cara ini.
misal data ada di A2->”AAABBBCCC”, maka di cell tujuannya tinggal paste formula:
=CONCATENATE(MID(A2,1,3),” “,MID(A2,4,3),” “,MID(A2,7,3))
nanti hasilnya jadi “AAA BBB CCC”
Hello, you post interesting content on your page, you deserve much more
visits, just type in google for – augo’s tube traffic
Thanks yaa.. membantu bangeeett..
Dah bolak balik cari formula yg pas hilangin spasinya, ternyata karakter spasinya berbeda untuk data dari web, Thanks….
Sama2.. 🙂
Thanks a lot, info yg sangat bermanfaat
BAGUS BANGET!!!! MAKASIH BANGET ILMUNYA!!!!!!!!!!!!!!!!
Trima ksh banyak gan telah byk membantu dgn menu substitute tadi,,,,sukses
Trima ksh banyak gan telah byk membantu dgn menu substitute tadi,,,,sukses
Mantap gan. saya baru aja nemuin masalah yang kompleks di data ane. sudah coba beberapa formula Trim, kombinasi MID dan LEN, LEFT, RIGHT, tidak bisa terpecahkan karna memang karakter spasinya tidak sama di setiap cell.
Terimakasih banyka ya gan.
hmmmm…
Mau tanya, gimana caranya menghapus spasi dibelakang angka
199104172015032001 (ada spasi setelah angka 1 di akhir)
pake formula trim aja, caranya udah di bahas di atas.. 🙂
Mau tanya mas, setelah karakter spasi dibelakang angka berhasil saya hilangkan dengan rumus diatas, kok tidak bisa saya SUM ya datanya. Data saya export dari web. Sudah saya coba pakai yg Subtitute tidak bisa. Terimakasih.