Hari ini adalah soalan gila. Anda mempunyai lajur nombor bahagian. Terdapat di antara 4 hingga 7 tanda sempang pada nombor bahagian. Anda hanya ingin mengekstrak bahagian nombor bahagian selepas tanda hubung pertama dan hingga tetapi tidak termasuk tanda hubung terakhir. Ini adalah episod duel Excel.
Tonton Video
- Matlamatnya adalah untuk mencari titik pertama & terakhir dan menyimpan semuanya di antara
- Bahagian yang sukar di sini adalah mencari titik terakhir
- Kaedah Bil 1: Isi Kilat
- Isi secara manual beberapa yang pertama (termasuk beberapa dengan bilangan tanda sempang yang berbeza)
- Pilih sel kosong di bawahnya
- Ctrl + E ke Isi Kilat
- Kaedah Mike 2:
- Gunakan Kueri Kuasa
- Di Excel 2016, Kuasa Kuasa berada dalam kumpulan Get & Transform di Excel 2016
- Dalam Excel 2010 & 2013, muat turun Kueri Kuasa dari Microsoft. Ia membuat tab Kuasa Kuasa baru di Pita
- Tukarkan data anda ke jadual menggunakan Ctrl + T
- Gunakan Split Data dalam Power Query - pertama untuk berpisah di dasbor paling kiri, kemudian untuk berpisah di dasbor paling kanan
- Kaedah Bil 3:
- Fungsi VBA yang berulang dari hujung sel ke belakang untuk mencari tanda hubung terakhir
- Kaedah Mike 4:
- Gunakan SUBSTITUT untuk mencari lokasi tanda sempang ke-N
- SUBSTITUTE adalah satu-satunya fungsi teks yang membolehkan anda menentukan nombor Instance
- Untuk mencari nombor contoh mana, gunakan
=LEN(A2)-LEN(SUBSTITUTE)
Transkrip Video
Bill: Hei. Selamat kembali. Sudah tiba masanya untuk podcast Dueling Excel yang lain. Saya Bill Jelen dari MrExcel. (Saya akan disertai oleh Mike Girvin dari ExcelIsFun. Ini adalah - 00:03) episod 185 kami: ekstrak dari yang pertama - hingga yang terakhir -.
Baiklah. Soalan hari ini dihantar oleh Anvar di YouTube. Bagaimana saya boleh mengekstrak semuanya dari yang pertama - hingga yang terakhir -, dan melihat data yang ada di sini. Terdapat sebilangan besar tanda pisah, di mana sahaja dari 3, 5, 6, 7 tanda pisah, okey?
Jadi, pemikiran pertama saya adalah, baiklah, sangat mudah untuk mencari yang pertama - bukan? = kiri atau = MID dari PENCARIAN A2 dan kemudian -, +1 baik-baik saja, tetapi untuk mendapatkan yang terakhir -, itu akan membuat kepala saya sakit, betul, kerana, berapa banyak tanda sempang yang kita ada Kita dapat mengambil SUBSTITUT A2, menggantikan tanda sempang, dan membandingkan panjangnya, panjang asalnya. Itu memberitahu saya bilangan tanda hubung, tetapi sekarang saya tahu yang mana - untuk mencari, ke-2, ke-3, ke-4, ke-5, tetapi adakah saya menggunakan FIND?
Saya sudah bersedia untuk pergi ke VBA, bukan? Itu reaksi lutut saya. Saya berkata, tunggu sebentar. Saya berkata, Anvar, versi Excel mana yang anda ada? Dia berkata, saya berada di Excel 2016. Saya berkata, itu indah. Sekiranya anda berada di Excel 2013 atau yang lebih baru, kami boleh menggunakan ciri baru yang hebat ini yang dipanggil flash fill. Dengan pengisian kilat, kita hanya perlu memberikan corak, dan saya akan memberikan corak yang cukup sehingga bukan hanya saya mengambil satu dengan dua tanda hubung dan melakukannya beberapa kali. Saya ingin memastikan bahawa saya mempunyai beberapa tanda sempang dengan cara itu. Chad di pasukan Excel tahu apa yang saya cari. Chad adalah lelaki yang menulis logik untuk mengisi kilat. Oleh itu, saya mendapat kira-kira 3 daripadanya di sana dan kemudian CONTROL + E adalah jalan pintas untuk menggunakan DATA dan kemudian FLASH FILL, dan, tentu saja, nampaknya ia melakukan perkara yang betul. Baiklah, Mike.Mari lihat apa yang anda ada.
Mike: Terima kasih, MrExcel. Yeah. Pengisian kilat menang. Ciri itu di sana, flash flash, adalah salah satu alat Excel moden yang sangat mengagumkan. Sekiranya ini adalah perjanjian satu kali dan anda mempunyai corak yang konsisten, hei, begitulah cara saya melakukannya.
Hei, mari kita pergi ke helaian seterusnya. Sekarang, daripada menggunakan flash fill, kita sebenarnya boleh menggunakan kueri kuasa. Sekarang, saya menggunakan Excel 2016 jadi saya mempunyai kumpulan GET & TRANSFORM. Itulah pertanyaan kuasa. Pada versi sebelumnya, 2013 (hingga 10 - 2:30), anda sebenarnya harus memuat turun add-in query kuasa percuma.
Sekarang, untuk membuat kueri kuasa berfungsi, ini harus ditukar ke jadual Excel. Sekarang, sekali lagi, saya akan menggunakan pengisian kilat jika ini adalah perjanjian satu kali. Bilakah anda akan menggunakan pertanyaan kuasa? Sekiranya anda mempunyai data yang sangat besar atau berasal dari sumber luaran, ini adalah cara untuk pergi, atau anda mungkin lebih suka ini daripada menaip 3 atau 4 contoh untuk mengisi flash kerana, dengan pertanyaan kuasa, kita dapat secara khusus katakan cari yang pertama - dan cari yang terakhir -.
Sekarang, saya akan menukarnya ke jadual Excel. Saya mempunyai satu sel terpilih, sel kosong sepanjang masa. Saya pergi ke INSERT, TABLE, atau anda menggunakan papan kekunci, CONTROL + T. Saya boleh klik OK atau ENTER. Saya mahu menamakan jadual ini, jadi saya akan menggunakan ALAT JADUAL, REKA BENTUK, menjadi HARTA. Saya akan menyebutnya STARTKEYTABLE dan ENTER. Sekarang saya dapat kembali ke DATA, membawanya ke pertanyaan kuasa menggunakan butang FROM TABLE. Ada ruangan saya. Ada namanya. Saya tidak mahu menyimpan nama ini kerana output akan dieksport ke Excel dan saya mahu memberikannya nama yang berbeza. Oleh itu, saya akan menyebutnya CLEANEDKEYTABLE. Saya tidak memerlukan JENIS BERUBAH itu. Saya hanya melihat sumbernya. Sekarang saya boleh klik pada lajur dan, di HOME, ada butang SPLIT. Saya boleh mengatakan SPLIT, OLEH DELIMITER. Nampaknya sudah meneka. Saya 'Saya akan mengatakan KIRI-PALING. Klik OK.
Sekarang, jika saya melihat ke sini saya melihat JENIS YANG BERUBAH. Saya tidak memerlukannya, jadi saya akan menyingkirkan langkah itu. Saya hanya mempunyai SPLIT COLUMN BY DELIMITER. Sekarang, saya akan melakukan ini sekali lagi tetapi, daripada menggunakan butang SPLIT di sini, klik kanan ke SPLIT COLUMN, BY DELIMITER, dan lihatlah. Kita boleh memilih untuk membaginya dengan DELIMITER PALING BENAR. Klik OK. Sekarang, saya tidak memerlukan dua lajur ini, jadi saya akan klik kanan pada lajur yang ingin saya simpan, HAPUSKAN KOLUM LAIN. Saya sebenarnya akan mengeluarkan X JENIS BERUBAH ini. Ini akan mengatakan APAKAH ANDA PASTI INGIN MENGHAPUSKAN INI? Saya akan katakan, ya, HAPUS. Ada data bersih saya.
Sekarang saya boleh datang untuk MENUTUP & LOAD. TUTUP & LOAD KE. Ini adalah kotak dialog IMPORT baru. Dulu kata LOAD TO tetapi saya mahu memuatkannya ke meja, di LEMBARAN KERJA YANG Sedia Ada. Klik butang runtuh. Saya akan memilih C1, buka kunci, klik OK, dan di sana kita pergi. Pertanyaan kuasa untuk membersihkan data kami dan mendapatkan data yang kami mahukan. Baiklah. Saya akan membuangnya kembali.
Bil: Ada intinya di sana, DELIMITER PALING KANAN di SPLIT COLUMN BY DELIMITER, salah satu ciri menarik dalam pertanyaan kuasa. Itu hebat.
Baiklah. Reaksi lutut saya - VBA UDF (tidak difahami - 05:34) sangat mudah dilakukan VBA. Tukar ke ALT + F11. MASUKKAN MODUL. Dalam modul itu, taipkan kod ini. Saya akan (buat - 05:43) fungsi baru, saya akan menyebutnya MIDPART, dan saya akan menyampaikannya beberapa teks, dan kemudian apa yang akan saya lakukan ialah saya akan pergi dari watak terakhir dalam sel itu dari panjang MYTEXT hingga 1, LANGKAH -1 dan melihat watak itu. Jadi, MID MYTEXT, pemboleh ubah i, memberitahu kita watak mana yang kita cari panjangnya 1. Adakah itu -? Sebaik sahaja saya menjumpai -, saya akan mengambil KIRI MYTEXT bermula dari watak i - 1, jadi saya menyingkirkan semua yang terakhir - sepanjang jalan keluar, dan kemudian, pastikan saya tidak pergi terus mencari lebih banyak tanda sempang, KECUALI UNTUK akan mengeluarkan saya dari gelung ini (tidak dapat difahami - 06:17),dan dari sana ada bahagian yang mudah. Kita hanya akan mengambil MYTEXT, bermula pada MID MYTEXT, (di mana saya menggunakan - 06:26) gunakan fungsi FIND untuk mencari yang pertama -, pergi 1 lebih dari itu, dan kembalikan itu kembali.
So, let's go back, ALT+Q, to return to Excel. = MIDPART tab of that, and it looks like it's working. Copy that down. Mike, do you have another one? (=MIDPart(A2))
Mike: Well, I do have another one,, but it's going to be one long formula -- not as short as that UDF. Alright, let's go over to the next sheet. Now, if we're going to do a formula and we have some text and there are always a different number of delimiters, somehow, I need to get the position of that last delimiter.
Now, this is going to take a few steps but I'm going to start with the SUBSTITUTE function. I'm going to look through that text, , the old text I want to find is in ”, that -, , and what do I want to put in its place or substitute? “”. That will put nothing in. Now, if I ) and CONTROL+ENTER, what is that going to do? (=SUBSTITUTE(A2,“-”,“”))
Well, now I can take the length of this and subtract it from the length of this item. That will tell me how many delimiters there are. F2, and right at the beginning, I'm going to type the length of that. That will give me the full length - the length of that dashless text, ), CONTROL+ENTER, double click, and send it down. that tells me how many delimiters there are for this text. There are 6. (=LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))
Now I'm going to use that sixth now inside of substitute to put a different character right at the sixth listing of the delimiter, F2, and if I type SUBSTITUTE, what we want to notice is this function has an instance number. If you look at other text functions like search and find, they don't have an instance number. Substitute is the only one I can think of that actually lets you specifically say which instance of a delimiter you want to deal with. Here's the text, ,. Old text is in “ a -, and I need to pick for the new text some character that will never be in this text ring. I'm going to choose, like, or something like that, , and that's where instance number comes in, ), CONTROL+ENTER, and there it is. If I double click and send it down, it's always putting that in the position of the last delimiter. (=SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))
Now I need to figure out, in each one of these, what position it is in. F2. I'm going to use the SEARCH function. SEARCH. I type S and tab. Now, search and find are the same except for search is not case-sensitive. In this case, either one would be fine because the text I'm looking for is in “, that ^, ”, , within that text. By the way, the reason that I use search instead of find is because S tab gets me search but F I tab will get me find. So, it's like one character less when typing it out. CONTROL+ENTER, double click and send it down, and now it tells me, in the 27th position is that last delimiter. (=SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))))
Now, I'm going to take this approach for these text items. I'm now going to use the left function and get everything from the very beginning all the way up to that position. That will get rid of that last little bit. Now, actually, search tells us 27 which is right there and we only want to go to 26. So, F2, and, at the end, I'm going to - 1, CONTROL+ENTER, double click and send it down. Now, I can use the left function. F2. LEFT. There it is, left of that, ,. That's how many characters. ), CONTROL+ENTER, double click and send it down. So, now, we have gotten rid of the last little bit after the last delimiter in every cell. (=LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1))
Now all I need to do is replace the first four characters, first four characters, first three characters. Now, I can use the search function on the original text because it can find the - which is three and I'll tell replace, please go, from the first character, three characters in and replace it with nothing, F2, and right at the beginning, I'm going to type REPLACE. There's the old text. Now watch this. I want to give myself a little bit more breathing room. I'm just going to artificially pick a space, ALT+ENTER. That's kind of like we do in DAX. Now I just have more breathing room. That's the old text, ,. The starting number, I need to always start at the first position so I simply type 1, , and I need to find that first - which represents number of characters. So, S tab, “-” , through… within that text, that search will find 4, 4, 3. That will work. ) and then , new text “”. That will put nothing in those first characters. ). I have the entire column highlighted so I can populate this edited formula with CONTROL+ENTER, and there we go. All the way down, we’re extracting everything between the first and the last -. (=REPLACE(LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1),1,SEARCH(“-”,A2),“”))
Now, the only reason we want to be crazy like that with formulas is if we wanted the formula result to instantly update whenever we changed anything, so if I type -00, instantly it updates. Power query and flash fill will not automatically update, alright? Send it back to.
Bill: Well, that was one heck of a formula. Like, substitute was the trick. I had used substitute in the first step but didn't see that it had the instance number. Alright, so, we have four different methods here today. My first method is flash fill. Select first few, select the blank box below that, and then CONTROL+E to flash fill. Mike's method, use power query. I love that, especially the split data letting you use the leftmost - and then the rightmost -. My live seminars always talk about this one feature. Should be a finalist for the Nobel Prize for the best excel feature. It wouldn't win but it would be in one of the top five, I'm sure. My method number three, VBA function, a UDF user-defined function, that iterates from the end of the cell, and then, Mike's method, the awesome formula method. Use substitute to find the location of the nth - and then pass that answer back into substitute that tells you which instance number to look from. Brilliant.
Nah, di sana anda pergi. Saya ingin mengucapkan terima kasih kepada semua orang kerana berhenti. Kami akan berjumpa anda di lain masa untuk podcast Dueling Excel yang lain dari dan ExcelIsFun.
Muat turun fail
Muat turun fail sampel di sini: Duel185.xlsm
