Berbagi Ilmu

Tips Microsoft Excel : Memecah Kata/Kalimat Menjadi Per Kolom


Zhac :

Gabung ya Om..

Ohya.. skalian numpang nanya Om…
Saya punya data (file) berupa formulir yg notabenen dalam bentuk kotak2 (columns)
Dalam 1 file terdiri atas sheet 1(Daftar Nama), sheet 2 Formulir).

Sya kepengen Nama dan identitas lain yang Saya input di sheet “Daftar Nama” secara otomatis terisi pula pada sheet 2 (Formulir)

Saya uda nyoba nyoba berbagai formula spt if, and, left, right dll.. tpi lom membuahkan hasil, dpt dikit tpi gk efisien.. klwat panjang formulanya.

Yg agak ribet.. yakni pada Sheet 2 (Formulir, terdidi atas columns2..

Yg kita input di Daftar Nama hasilnya harus terisis di Formulir yang mana setiap huruf satu columns.

Cth, pd daftar nama ; Ahmad

Hasil di formulir nti huruf A satu columns dstnya…

Mhn solusinya ya Om.

Thx sebelumnya.

Terima kasih atas buat mas Zhac dengan alamat email di gazaliuw@yahoo.com atas pertanyaan yang diajukan. Pertanyaan ini berbarengan dengan niat saya untuk membahas hal tersebut karena terkadang kita mengalami suatu tugas dimana kita disediakan data yang berisi kata atau kalimat dalam satu kolom tapi kita ditugasi untuk memecah kata atau kalimat tersebut menjadi perkolom, atau saya perjelas ilustrasinya seperti ini :

Di Sheet 1 Cell A2 sudah ada nama misalkan Ahmad, sedankan di Sheet 2 Cell A2 kita ingin muncul hanya hurup A, selanjutnya di Cell B2 muncul huruf h dan selanjutnya….

Yuk kita bahas sekarang…

Anggaplah kita punya data seperti ini :

gambar1

gambar1

Kemudian data-data tersebut ingin kita pecah dalam form/kolom yang sudah tersedia seperti dibawah ini :

gambar2

gambar2

Anggaplah dengan mengetikan angka 1 di Sheet 2 (gambar 2) tepatnya di Cell B1 maka data isian akan terisi dengan data sesuai nomor urut angka dari Sheet 1 (gambar 1). Maka rumus yang akan kita bangun yaitu :

Untuk Isian Nama di Cell D3 adalah :

=LEFT(VLOOKUP($B1$,Sheet1!$A$4:$E$5,2,FALSE),1)

Rumus ini untuk mencari nilai atau huruf disebelah kiri sebanyak 1 (satu) huruf dengan membandingkan angka yang tertera di Cell B1  dengan data-data yang ada diurutan angka tersebut di Sheet 1.

Jika di Cell B1 kita ketikkan angka 1 maka yang akan muncul di Cell D4 adalah huruf I.

Selanjutnya kita akan membuat rumus yang akan menampilkan huruf kedua dan selanjutnya dari nama yang kita maksudkan, karena awalan nama Iyan Nurdiansyah sudah muncul yaitu huruf I maka tugas kita adalah memunculkan hufu y di Cell E3, dan rumusnya adalah :

=MID(VLOOKUP($B1$,Sheet1!$A$4:$E$5,2,FALSE),2,1)

Kenapa menggunakan Fungsi MID karena huruf y berada setelah huruf awal, adapun dalam rumusnya terdapat angka 2,1 hal tersebut dimaksudkan adalah untuk memunculkan huruf kedua hanya satu huruf. dan jika benar kita melakukannya maka di Cell E3 akan muncul huruf y.

Rumus kedua ini menjadi kunci untuk rumus mencari huruf selanjutnya karena tugas anda cukup mengcopypaste rumus tersebut di Cell F3, G3 dan seterusnya sampai DI Cell W3 dan selanjutnya anda cukup merubah angka yang terdapat didalam rumus yaitu angka 2,1 menjadi 3,1 (Cell F3), 4,1 (Cell G3) dan seterusnya sampai di Cell W3 (20,1).

Jika kalian kebingungan maka rumus yang muncul di Cell F3 adalah :

=MID(VLOOKUP($B1$,Sheet1!$A$4:$E$5,2,FALSE),3,1)

Cell G3

=MID(VLOOKUP($B1$,Sheet1!$A$4:$E$5,2,FALSE),4,1)

Mengerti sampai sini….. jika rumus tersebut benar maka akan muncul nama seorang blogger yang keren yaitu Iyan Nurdiansyah hehehehehehe.

Selanjutnya untuk memunculkan Data Tempat / Tanggal lahir (tetap perhatikan gambar 2) caranya sama dengan cara pertama, hanya yang berbeda adalah range yang diambil untuk memunculkan data tersebut, jika untuk memunculkan nama range yang dipilih adalah 2 maka untuk memunculkan tempat dan tanggal lahir range yang dipilih adalah 3.

Coba perhatikan perbandingan rumus dibawah ini, dimana rumus pertama sudah kita bahas untuk memunculkan nama (awal huruf) dan huruf selanjutnya :

=LEFT(VLOOKUP($B1$,Sheet1!$A$4:$E$5,2,FALSE),1)

=MID(VLOOKUP($B1$,Sheet1!$A$4:$E$5,2,FALSE),2,1)

Maka untuk memunculkan Data Tempat / Tanggal lahir rumusnya menjadi :

Cell D5 =LEFT(VLOOKUP($B1$,Sheet1!$A$4:$E$5,3,FALSE),1)

Cell E5 =MID(VLOOKUP($B1$,Sheet1!$A$4:$E$5,3,FALSE),2,1)

Untuk rumus selanjutnya sama dengan cara pertama…tapi ingat range yang dipake adalah 3.

Sedangkan rumus untuk memunculkan tanggal lahir adalah dengan menambah Fungsi TEXT, sehingga rumusnya adalah

Untuk memunculkan (angka) tanggal pertama(Cell P5) :

=LEFT(TEXT(VLOOKUP($B$1,Sheet1!$A$4:$E$5,4,FALSE),“DD”),1)

Untuk memunculkan (angka) tanggal kedua (Cell Q5) :

=MID(TEXT(VLOOKUP($B$1,Sheet1!$A$4:$E$5,4,FALSE),“DD”),2,1) atau

=RIGHT(TEXT(VLOOKUP($B$1,Sheet1!$A$4:$E$5,4,FALSE),“DD”),1)

Untuk memunculkan (angka) bulan pertama (Cell S5) :

=LEFT(TEXT(VLOOKUP($B$1,Sheet1!$A$4:$E$5,4,FALSE),”MM“),1)

Untuk memunculkan (angka) bulan kedua (Cell T5) :

=MID(TEXT(VLOOKUP($B$1,Sheet1!$A$4:$E$5,4,FALSE),”MM“),2,1) atau

=RIGHT(TEXT(VLOOKUP($B$1,Sheet1!$A$4:$E$5,4,FALSE),”MM“),1)

Untuk memunculkan (angka) tahun pertama (Cell V5) :

=LEFT(TEXT(VLOOKUP($B$1,Sheet1!$A$4:$E$5,4,FALSE),”YY“),1)

Untuk memunculkan (angka) tahun kedua (Cell W5) :

=MID(TEXT(VLOOKUP($B$1,Sheet1!$A$4:$E$5,4,FALSE),”YY“),2,1) atau

=RIGHT(TEXT(VLOOKUP($B$1,Sheet1!$A$4:$E$5,4,FALSE),”YY“),1)

Nah untuk mencari Status Pendidikan otomatis jadi mudah karena caranya sama dengan cara diatas. Atau saya kasih aja deh rumus untuk mencari huruf pertama dan kedua sedangkan selanjutnya itu tugas anda ya…

Huruf pertama di Cell D7 :

=LEFT(VLOOKUP($B$1,Sheet1!$A$4:$E$5,5,FALSE),1)

Huruf kedua di Cell E7 :

=MID(VLOOKUP($B$1,Sheet1!$A$4:$E$5,5,FALSE),2,1)

Silakan mencoba atau jika ingin memunculkan angka 1 atau angka berikutnya di Sheet 2 Cell B1 dalam bentuk list silakan aja buka postingan ini ya.

Unduh Contoh filenya ada disini RapidshareZiddu.

Unduh tulisan format PDF ada disini RapidshareZiddu.

——————————————-00000000000——————————————-

Sumber bacaan :

  • Mengungkap Kedahsyatan Fungsi IF karangan Johar Arifin, penerbit PT. Elex Media Komputindo.
  • 25 Aplikasi Bisni Excel 2007, penerbit Maxikom.
  • Mempermudah dan Mempercepat Pekerjaan Menggunakan Formula Excel 2007, penerbit
    Media Kita.
  • Microsoft Excel 2007 Membangun Rumus dan Fungsi, penerbit andipublisher.
  • Mengungkap Kedahsyatan Form Controls Excel 2007 Untuk Bisnis Terapan, penerbit PT. Elex Media Komputindo.
  • Word & Excel 2007 Untuk Administrasi Bisnis, penerbit Maxikom.
  • 90 Trik Tersembunyi Excel 2007, penerbit Maxikom.

——————————————-00000000000——————————————-

Jika ingin tahu tips dan trik tentang Excel lainnya silakan klik banner dibawah ini :

——————————————-00000000000——————————————-

Untuk saran dan kritik atau pertanyaan bisa dilayangkan ke :

bunga.liani@yahoo.co.id atau ke blognyaomiyan@gmail.com

——————————————-00000000000——————————————-

Silahkan Meng-Copy paste tulisan ini dengan catatan tetap menampilkan sumber tulisan ini.

Tentang omiyan

Cuman seorang newbie didunia maya, selalu merasa jika berbicara lewat tulisan itu lebih baik daripada kita berbicara tanpa ada jejak dalam sebuah tulisan.

Diskusi

26 pemikiran pada “Tips Microsoft Excel : Memecah Kata/Kalimat Menjadi Per Kolom

  1. Sip Om…
    Kalau saya biasanya sediakan baris ekstra lalu saya isi 1, 2, 3 dst untuk memudahkan pembuatan formula yang hanya berubah pada karakter tertentu.
    Baris ini tentu saja disembunyikan. Tapi ini sekedar trik dari orang malas seperti saya.
    Kalau untuk tutorial ya sebaiknya seperti yg Omiyan uraikan.
    Salam Excel!!!

    Posted by marsudiyanto | 6 Desember 2010, 08:22
  2. Nambah lagi dikit…
    range kunci seperti Sheet1!$A$4:$E$5 kadang lebih praktis kalau dinamai…
    Begitu kan Om? :D

    Posted by marsudiyanto | 6 Desember 2010, 08:24
  3. Mas iyan… saya mau mnt tlg nich kalo buat no. invoice otomatis dari excel gmn..???

    Posted by Ricky | 6 Desember 2010, 08:24
  4. marsudiyanto :

    Betul pak mars…. :D

    Posted by omiyan | 6 Desember 2010, 08:32
  5. Ricky :

    COntohnya kirim aja mas via email…

    thanks

    Posted by omiyan | 6 Desember 2010, 09:00
  6. Asik asik, ternyata caranya begitu… :mrgreen:

    Dulu waktu semester 4 kuliah sempet bertanya-tanya juga gimana cara membuat seperti yang Zhac tanyakan. Akhirnya krn ga bisa, terpaksa manual satu-satu… :(

    Posted by Asop | 7 Desember 2010, 11:37
  7. saya selalu tertarik kaLo ada masaLah tentang formuLa di exceL..
    ini tadi dah saya coba, dan ternyata berhasiL…

    Posted by Hari Mulya | 8 Desember 2010, 02:05
  8. Asop :

    hehehe tadinya saya juga sama…untungnya setelah utak atik eh nemu juga…. ga sesulit yang dibayangin mas

    Posted by omiyan | 8 Desember 2010, 03:35
  9. Hari Mulya :

    Alhamdulillah, dengan begitu formula yang dibikin tidak salah pak…selamat deh…tp ga da hadiahnya nih hehehe

    Posted by omiyan | 8 Desember 2010, 03:36
  10. info bagus kawan

    Posted by Tip Trik Blogger | 8 Desember 2010, 03:37
  11. Urun Rembug pak :

    kalO saya biasanya cukup pakE Fungsi MID ndak usah pakE LEFT ama RIGHT …
    misal cell yang maO dipisah adalah A1 … tarOh pormula berikOt pada B1

    =MID($A1;COLUMN()-1;1)

    … tinggal dikopi deh ke C,D,E ….. dst sampE mentok yang kita pengEn

    buWat yang punya warung linknya udah saya pasang disini
    http://norkuys.wordpress.com/kawan-handai-taulan/
    mohon kiranya kalO boleh untuk bisa numpang nitip link di blog bapak

    terimakasih

    Posted by motorbreath | 8 Desember 2010, 06:51
  12. Meskipun aku baca sampai bolak-balik tetap saja msih gak ngeh. Mending langsung praktek biar gak tambah bingung, ini saja alisku sampai berkerut :roll:
    Salam hangat serta jabat erat selalu dari Tabanan

    Posted by Sugeng | 9 Desember 2010, 06:48
  13. tampaknya saya bisa berguru excel disini, excel cuma saya pake buat bikin tabel doank… T_T

    Posted by Irawan | 9 Desember 2010, 11:33
  14. terima kasih infonyaaa….

    Posted by katakatalina | 9 Desember 2010, 15:01
  15. Manfaat sekali Om. Thanks ya…teruskan berbagi…berkat melimpah

    Posted by ankelistyan | 12 Desember 2010, 01:53
  16. Nice article, thanksfor share
    artis

    Posted by artis | 12 Desember 2010, 14:32
  17. terus..terus.! nulislah lagi tak bacanya kabeh , aku seneng tenan ini !

    Posted by KI_JAGAREKSA | 24 Desember 2010, 13:22
  18. Terima kasih sob….
    ini sangat bermanfaat buat aq………

    : Sama sama mas

    Posted by Manz | 20 Januari 2011, 03:46
  19. Wah wah… Seumur-umur pake excel, baru tau trik yang seperti ini… Thank’s postingannya ya… Jadi nambah ilmu nih .. :cool:

    Posted by Nurdiana Atmanagara | 1 Februari 2011, 08:35
  20. Mas Boleh nanya ya..
    aku baru import tabel dari SQL ke Excel. Ternyata ada kolom yg setiap cell nya berisi puluhan baris. Nah aku pengen memecahnya meenjadi beberapa baris.. Tolongin dong mas.. Bingung ne, ccoz ada ratusan baris yg harus di pecah..

    kira-kira gini mas tabelnya (waktu dibuka di excel)
    __________________________________________________
    KODE POS
    __________________________________________________
    1 Jalan Dr. Wahidin, Yogyakarta
    Jalan Sudirman, Bandung Utara
    Jalan Diponegoro, Ungaran-Semarang
    __________________________________________________
    2 Surabaya
    Solo
    Yogyakarta
    __________________________________________________

    Pengennya dibuat jadi gini mas
    __________________________________________________
    KODE POS
    __________________________________________________
    1 Jalan Dr. Wahidin, Yogyakarta
    __________________________________________________
    1 Jalan Sudirman, Bandung Utara
    __________________________________________________
    1 Jalan Diponegoro, Ungaran-Semarang
    __________________________________________________
    2 Surabaya
    __________________________________________________
    2 Solo
    __________________________________________________
    2 Yogyakarta
    __________________________________________________

    oh iya, pas tabelnya aku buka di notepad, separator kolomnya gini “:”
    tapi separator pindah barisnya di kolom POS tidak dikenali alias kotak-kotak gitu, nah pas dibuka di excel, separator kotak-kotak itu ilang, tapi satu Cell jadi berisi beberapa baris gitu di kolom POS..

    Mohon petunjuknya ya mas nd kalau bisa Kirim ke Emailku ya mas.. Thank’s b4..

    bazzsumbayak@gmail.com

    Posted by Sebastian Saragih | 1 September 2012, 02:01
  21. Mas Boleh nanya ya..
    aku baru import tabel dari SQL ke Excel. Ternyata ada kolom yg setiap cell nya berisi puluhan baris. Nah aku pengen memecahnya meenjadi beberapa baris.. Tolongin dong mas.. Bingung ne, ccoz ada ratusan baris yg harus di pecah..

    kira-kira gini mas tabelnya (waktu dibuka di excel)(tanpa titik)
    __________________________________________________
    KODE……………… POS
    __________________________________________________
    1……………………. Jalan Dr. Wahidin, Yogyakarta
    ……………………….Jalan Sudirman, Bandung Utara
    ……………………….Jalan Diponegoro, Ungaran-Semarang
    __________________________________________ti________
    2 …………………….Surabaya
    ……………………….Solo
    ……………………….Yogyakarta
    __________________________________________________

    Pengennya dibuat jadi gini mas (tanpa titik)
    __________________________________________________
    KODE…………….. POS
    __________________________________________________
    1…………………….Jalan Dr. Wahidin, Yogyakarta
    __________________________________________________
    1…………………….Jalan Sudirman, Bandung Utara
    __________________________________________________
    1…………………….Jalan Diponegoro, Ungaran-Semarang
    __________________________________________________
    2…………………….Surabaya
    __________________________________________________
    2…………………….Solo
    __________________________________________________
    2…………………….Yogyakarta
    __________________________________________________

    oh iya, pas tabelnya aku buka di notepad, separator kolomnya gini “:”
    tapi separator pindah barisnya di kolom POS tidak dikenali alias kotak-kotak gitu, nah pas dibuka di excel, separator kotak-kotak itu ilang, tapi satu Cell jadi berisi beberapa baris gitu di kolom POS..

    Mohon petunjuknya ya mas nd kalau bisa Kirim ke Emailku ya mas.. Thank’s b4..

    bazzsumbayak@gmail.com

    Posted by Sebastian Saragih | 1 September 2012, 02:04
  22. aku pengen tanya
    gimana cara menjadikan 1 cell menjadi 2 kata ,
    saya sudah mencoba pke wrap text tpi setelah kata itu sudah menjdi 2 kta ,saya perlebar kolom nya menjadi ,1 cell dlam 2 kta. g
    itu gmana crany?

    Posted by sahar | 8 September 2012, 20:54
  23. maksud saya 2 kata di jdikan 2 kalimat pke wrap text
    truz saya cba lebarkan kolom ny ,dya kembli menjdi 2 kta dlm 1 kalimat ,
    kan sya mwu ny 2kta menjdi 2 kalmat truz di perlebr klom nya. . .
    hruz ttp 2 kta ,2 klmat dlam 1 cell
    gmana apkh bsa? berikn solusinya

    Posted by sahar | 8 September 2012, 21:13
  24. terimakasih banyak infonya boss..
    trus ..klo mo nge-print semua form itu secara otomatis gimana ya…
    ….God Bless U….

    Posted by arifki merdeka | 22 Desember 2012, 22:36
  25. Terimakasih infonya sangat membantu. Terus berkarya

    Posted by dimas | 21 Maret 2013, 09:19
  26. Sangat bermanfaat dan sangat mudah untuk dipahami.

    Jadi mudah nih membuat form aplikasi untuk pengisian data.

    Thx untuk info nya.

    Salam,
    MarketingAsuransi.com

    Posted by marketingasuransi.com | 15 Juli 2013, 20:52

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Logout / Ubah )

Twitter picture

You are commenting using your Twitter account. Logout / Ubah )

Facebook photo

You are commenting using your Facebook account. Logout / Ubah )

Google+ photo

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

twitter

  • 1,658,221 Sejak 17 Nov 2008

Kategori

Yang Lagi OL

Langganan via Email

Bergabunglah dengan 704 pengikut lainnya.

smadav antivirus indonesia

Ikuti

Get every new post delivered to your Inbox.

Bergabunglah dengan 704 pengikut lainnya.

%d bloggers like this: