Home > Office > Menghilangkan spasi (trim whitespace) pada cell excel

Menghilangkan spasi (trim whitespace) pada cell excel


tabel

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…

  1. Rahmat A
    April 5, 2013 at 9:33 am

    Akhirnya ketemu juga blog yang bahas untuk menghilangkan spasi yang membandel kombinasi trim & subsitute. Thanks a lot, Pak.

    • April 18, 2013 at 8:19 am

      sama-sama pak rahmat.. semoga sukses.. πŸ™‚

  2. June 28, 2013 at 2:05 pm

    whaaaa ini baru hebat, dari kemarin browsing gak ada yang sip….. ini baruuuuu makasih banget yaaaa……

    • August 17, 2013 at 9:57 am

      Sama2 gan.. seneng rasanya kalo postingan saya bermanfaat bagi orang lain.. πŸ™‚

  3. ade
    July 5, 2013 at 4:43 pm

    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,,,

    • August 17, 2013 at 10:07 am

      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.. πŸ™‚

  4. Tri G
    September 12, 2013 at 3:18 pm

    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”

    • September 26, 2013 at 4:20 am

      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. πŸ™‚

  5. Ramdani (Aguy)
    September 12, 2013 at 6:01 pm

    Krennn banget infonya, makasih Infonya, sudah di coba dan berhasil. Trimakasih banyak.

    • September 26, 2013 at 4:21 am

      sama-sama pak Ramdani (Aguy) πŸ™‚ senang bisa membantu

  6. isak
    September 25, 2013 at 9:47 am

    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

    • September 26, 2013 at 4:28 am

      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 πŸ™‚

  7. January 1, 2014 at 2:29 pm

    Betul sekali… mantap ilmunya…

  8. RINGO
    January 28, 2014 at 8:58 pm

    Β FSS13002574
    KALO INI GIMANA GAN?
    Thx

    • January 28, 2014 at 10:10 pm

      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.. πŸ™‚

  9. April 24, 2014 at 10:51 pm

    trims gan

  10. waya
    April 26, 2014 at 3:46 pm

    mantab, hatur nuhun banget kang. membantu banget.

  11. riyanto
    June 29, 2014 at 12:29 pm

    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

    • June 29, 2014 at 6:07 pm

      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

      =SUBSTITUTE(A1,” “,””)

      di cell A2, maka hasilnya sudah sesuai seperti “1197004091990031006”. mungkin bila ada contoh filenya akan lebih bisa menggambarkan kondisinya.. πŸ™‚

  12. July 24, 2014 at 8:22 pm

    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.

    • August 28, 2014 at 3:43 pm

      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..

  13. Zen
    December 3, 2014 at 12:47 am

    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..

    • May 12, 2015 at 3:05 pm

      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”

  14. January 6, 2015 at 5:56 am

    Hello, you post interesting content on your page, you deserve much more
    visits, just type in google for – augo’s tube traffic

  15. evi
    April 14, 2015 at 11:50 am

    Thanks yaa.. membantu bangeeett..

  16. Ade
    May 12, 2015 at 2:25 pm

    Dah bolak balik cari formula yg pas hilangin spasinya, ternyata karakter spasinya berbeda untuk data dari web, Thanks….

  17. Veronica
    June 11, 2015 at 2:00 pm

    Thanks a lot, info yg sangat bermanfaat

  18. Rio Ali
    July 14, 2015 at 10:58 am

    BAGUS BANGET!!!! MAKASIH BANGET ILMUNYA!!!!!!!!!!!!!!!!

  19. naeny
    August 15, 2015 at 4:58 pm

    Trima ksh banyak gan telah byk membantu dgn menu substitute tadi,,,,sukses

  20. kurnain
    August 15, 2015 at 5:00 pm

    Trima ksh banyak gan telah byk membantu dgn menu substitute tadi,,,,sukses

  21. ToropMa
    August 24, 2015 at 5:54 pm

    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.

  22. Kusut
    March 16, 2016 at 12:37 pm

    hmmmm…

  23. January 25, 2017 at 11:38 am

    Mau tanya, gimana caranya menghapus spasi dibelakang angka
    199104172015032001 (ada spasi setelah angka 1 di akhir)

    • January 25, 2017 at 3:55 pm

      pake formula trim aja, caranya udah di bahas di atas.. πŸ™‚

  24. krishnashanti
    February 27, 2020 at 9:49 am

    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.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: