Bagaimana memisahkan kolom data Excel menjadi dua kolom. Cara mengurai data di Excel.
Menonton video
- Metode pertama Bill menggunakan Text to Columns (ditemukan di tab Data).
- Pada langkah 1, pilih dibatasi. Pada langkah 2, pilih spasi. Lewati langkah 3 dengan mengklik selesai.
- Teks akan terpecah di setiap ruang, jadi apa pun yang berisi tiga kata akan berakhir di 3 sel. Satukan kembali dengan
=TEXTJOIN(" ",True,B2:E2)atau - dengan
=B2&" "&C2&" "&D2 - Metode pertama Mike menggunakan Power Query. Power Query adalah Dapatkan & Transformasi pada tahun 2016 atau unduhan gratis untuk tahun 2010 atau 2013.
- Pertama, konversikan data Anda ke tabel menggunakan Ctrl + T. Lalu, di Power Query, dari Tabel. Pisahkan Kolom, dengan Pembatas. Pilih Spasi lalu di pembatas paling kiri.
- Anda dapat menamai ulang kolom dengan mengklik dua kali!
- Tutup & Muat Ke… dan pilih tempat baru di lembar kerja.
- Metode kedua Bill adalah dengan menggunakan Isi Cepat. Ketik tajuk baru di A, B & C. Isi Kilat tidak akan berfungsi jika Anda tidak memiliki tajuk! Ketikkan pola untuk dua baris pertama.
- Pergi ke sel kosong pertama di B dan tekan Ctrl + E. Ulangi untuk kolom C.
- Metode kedua Mike adalah menggunakan rumus ini:
- Untuk bagian pertama, gunakan
=LEFT(A2,SEARCH(" ",A2)-1) - Untuk bagian kedua, gunakan
=SUBSTITUTE(A2,B2&" ","")
Transkrip Video
(Musik)
Bill Jelen: Halo, selamat datang kembali, saatnya untuk Podcast Dueling Excel lainnya. Saya Bill Jelen dari. Saya akan bergabung dengan Mike Girvin dari Excel Is Fun. Ini milik kita
Episode 182: Pisahkan Data dari Satu Sel menjadi Muncul dalam Dua Sel.
Baiklah, pertanyaan hari ini dikirim oleh Tom. Adakah cara untuk dengan mudah membagi data dalam satu sel agar data muncul dalam dua sel? Misalnya, Jalan Utama 123, dia ingin 123 di satu sel dan Jalan Utama di sel lain; atau, Howard dan Howard dan kemudian End. Saya telah menghabiskan waktu berjam-jam untuk memisahkan jenis data ini. Saya akan senang mendengar dari perusahaan Anda sementara ada banyak cara berbeda untuk melakukan ini.
Hal pertama yang akan saya lakukan adalah memilih semua Data, Ctrl + Shift + Panah Bawah dan kemudian Data, Teks ke Kolom. Teks ke Kolom di Langkah 1, data Dibatasi. Itu dibatasi oleh Spasi dan kemudian klik Selesai. Sekarang, kerumitan dengan metode ini adalah jika Anda memiliki 123 Main Street, ini akan berakhir di 3 sel, bukan 2 sel. Oh, Power Query akan membuat ini jauh lebih mudah tapi di sinilah kita. Baiklah, jadi yang akan saya lakukan adalah saya akan keluar jauh di sebelah kanan Data di mana saya tahu bahwa di luar tempat segala sesuatu dibangun. Jika saya menggunakan Office 365, saya akan menggunakan TEXTJOIN. TEXTJOIN, hal yang luar biasa itu, pembatas adalah Spasi. Abaikan sel kosong True dan kemudian sel yang ingin saya gabungkan seperti itu, dan saya salin semuanya ke bawah, Ctrl + V. Saya akan menyalin Ctrl + C lalu Beranda, Tempel,Tempel sebagai Nilai dan pada titik ini, saya dapat menghapus 3 kolom tambahan ini.
Ahh, tapi tidak ada yang punya Office 365, kan? Jadi, jika Anda tidak memiliki Office 365, Anda harus melakukan = ini & "" & itu, lalu jika ada lebih banyak "" & itu, dan jika ada lebih banyak, lanjutkan. Dalam hal ini tidak ada gunanya karena tidak ada yang berakhir di D tetapi Anda mendapatkan idenya. Ctrl + C, salin ke baris data terakhir, Ctrl + V lalu Ctrl + C, Alt + ESV untuk membuat nilai B. Dan di sanalah kita, baiklah. Mike, mari kita lihat apa yang Anda miliki.
Mike Girvin: Terima kasih ,. Hei, Anda menganggap saya mudah di sini karena Anda sudah menyebutkan Get & Transform Power Query, Teks lama ke Kolom hanya memungkinkan Anda mengatakan spasi di setiap karakter, bukan? Nah, jika kita menggunakan Power Query, kita bisa menggunakan Pemisah itu dan berkata, "Hai, pisahkan saja saat kejadian pertama."
Sekarang, untuk memasukkan Data ini ke Editor Kueri, kita harus mengubahnya menjadi tabel Excel. Jadi saya naik ke Sisipkan, Tabel atau saya menggunakan Ctrl + T. Tabel saya memiliki header, tombol OK disorot sehingga saya dapat mengkliknya dengan mouse saya atau cukup tekan Enter. Sekarang saya ingin memberi nama Tabel ini jadi saya akan ke sini, OriginalData dan Enter. Sekarang, ini adalah tabel Excel, kita bisa sampai ke Data dan itu dari Tabel. Itu akan membawanya dari Excel ke Editor. Kolom yang dipilih: Tab Pita Beranda, kita dapat mengatakan Pisahkan Kolom dengan Pembatas atau ke sini dan klik kanan, Pisahkan Kolom dengan Pembatas. Dari tarik-turun, kita dapat mengatakan, hai, gunakan Spasi dan lihat ini Di pembatas paling kiri. Saat saya mengklik OK, BOOM! Itu ada. Sekarang, saya akan memberi nama pada kedua kolom ini: klik dua kali Bagian 1 Enter, klik dua kali Bagian 2 dan Enter. Sekarang,Saya dapat datang ke sini atau Tutup & Muat, Tutup & Muat Ke dan saya dapat memilih di mana akan meletakkan ini. Saya pasti ingin membuangnya sebagai Tabel, Lembar kerja baru, Lembar kerja yang sudah ada. Sorot ini, klik tombol ciutkan. Saya akan mengatakan D1, klik OK lalu klik Load. Dan begitulah, Output Power Query kami.
Baiklah, kembalikan ke.
Bill Jelen: Oh, Mike, Power Query luar biasa! Ya, itu cara yang bagus untuk pergi. Ini satu lagi yang mungkin berfungsi jika Anda memiliki Excel 2013 atau yang lebih baru.
Dan yang akan kita lakukan adalah keluar ke sini dan mengatakan Bagian Pertama lalu Bagian Kedua. Pastikan untuk meletakkan heading ini bahwa jika Anda tidak meletakkan heading tersebut, mereka tidak harus seperti itu tetapi mereka harus memiliki heading atau tidak akan berfungsi. Saya akan menempatkan 123 dan Main Street dan kemudian kita akan menempatkan Howard dan End, seperti itu. Sekarang kita memiliki pola kecil yang bagus di sana, keluar di sini di Tab Data dan Isi Flash yaitu Ctrl + E, tekan Ctrl + E di sana dan kemudian tekan Ctrl + E di sana. Yang menarik adalah, kita tidak harus menggabungkan data bersama seperti dalam contoh saya. Baiklah, Mike, kembali padamu.
Mike Girvin: Ding-ding-ding. Itu adalah pemenangnya tanpa diragukan lagi. Isi Cepat adalah cara untuk pergi ke sana. Perhatikan, kami tidak perlu mengubahnya menjadi tabel atau membuka kotak dialog apa pun; hanya mengetik beberapa contoh lalu Ctrl + E.
Baiklah, kita bisa melakukannya dengan rumus meskipun Isi Cepat mungkin akan lebih cepat. Nah lihat ini, pola seperti sel daftar ini yang digunakan di Flash Fill adalah segalanya sebelum spasi pertama dan kemudian semuanya setelahnya. Jadi, hei, saya akan menggunakan fungsi LEFT, Teksnya ada di sana dan berapa banyak karakter dari kiri? Baiklah, saya akan mencari spasi itu - 1 2 3 4 menggunakan fungsi SEARCH, Find Text, space dan "", di dalamnya. Sekarang, perhatikan bahwa Penelusuran akan menghitung dengan jari-jarinya 1 2 3 4 dan itu akan mencapai ruang yang saya inginkan, ruang itu jadi saya -1) Ctrl + Enter, klik dua kali dan kirimkan ke bawah. Jadi, itu selalu mendapatkan segalanya sebelum ruang pertama.
Sekarang, perhatikan bahwa kita sudah memiliki teks di sini sehingga saya dapat menggunakan fungsi SUBSTITUTE. Teks yang akan saya lihat adalah Data Lengkap, Koma, Teks Lama yang ingin saya cari dan kemudian GANTI. Tidak ada yang hampir 1 2 3. Saya sebenarnya ingin menambahkan Spasi yang baru saja saya keluarkan di rumus sebelumnya, kembali masuk. Sekarang, akan mencari 1 2 3, Spasi lalu Howard, Spasi dan seterusnya, Koma lalu teks baru yang ingin saya gantikan. Nah, untuk memberi tahu SUBSTITUTE bahwa Anda ingin menggantinya dengan apa-apa, Anda mengatakan "" tidak ada spasi di antaranya, Tutup Tanda kurung dan itu akan berhasil. Ctrl + Enter, klik dua kali dan kirim ke bawah. Baik? Buang saja kembali ke.
Bill Jelen: Hey! Alright, Mike, both of your methods were awesome. Let's do a quick wrap-up here. My first method using Text to Columns: Step 1, choose Delimited; Step 2, choose a space, and then click Finish. The problem is that if you have multiple spaces it’s going to end up in multiple cells. I have to put those back together. Office 365 TEXTJOIN or the old B2&“ ”&C2 and so on.
Mike used Power Query, it's known as getting transforming Excel 2016 or in earlier versions 10 or 13, you download it and use the Power Query Tab. I even learned something here, but first you converted data using Ctrl+ T then from Table, Split Column, by Delimiter, choose Delimiter Space and then, at once, at the left-most delimiter. I didn't know you could rename a column by double- clicking. I've been right-clicking and renaming all this time and being a little annoyed of that. That will save me a lot of time. And then not Close & Load but Close & Load 2 and choose a new spot on the worksheet.
My second method was Flash Field. Now that is great if you have Excel 2013 or newer. Just type the headings, it won't work without the headings. Type a pattern for the first two rows. Go to the first blank cell and press Ctrl+E in each column.
And then, Mike's method. Well, sure that was longer. It is a must if you have something before Excel 2013 because you can't use Flash Fill. Maybe in 2010 you can just Power Query, just add some new columns over there at the LEFT of A2 and then SEARCH, look for the space, and -1 to get rid of that space.
For the second part, SUBSTITUTE, I was going to use equal mid or something like that but this is even better because you already know what you want to take out. You want to take out B2 and the Space and replace it with nothing. That was awesome.
Baiklah, saya ingin berterima kasih kepada semua orang yang telah mampir. Sampai jumpa di lain waktu untuk Dueling Excel Podcast dari dan Excel itu Menyenangkan.
Unduh berkas
Unduh file sampel di sini: Duel182.xlsm
