Menemukan Tanggal - Tips Excel

Beberapa pertanyaan yang masuk cukup sulit. Hari ini, kami memiliki kolom sel. Setiap sel memiliki beberapa kata, lalu tanggal, lalu beberapa kata lagi. Tujuannya adalah untuk menarik bagian tanggal dari teks tersebut ke kolom baru. Ini adalah episode duel dengan ide dari Bill dan Mike.

Menonton video

• Pendekatan Bill yang sangat luas:
• Letakkan semua 12 bulan di kolom terpisah
• Gunakan fungsi FIND untuk melihat apakah bulan ini ada dalam teks aslinya
• Untuk menemukan posisi awal minimum, gunakan = AGGREGATE (5,6,…
• Beberapa rumus tambahan untuk mencari posisi angka 2 atau 3 sebelum bulan
• Pendekatan Mike:
• Gunakan SEARCH alih-alih FIND. Temukan peka huruf besar / kecil, Pencarian tidak.
• Buat operasi array argumen fungsi dengan menentukan B13: B24 sebagai Find_Text.
• Rumusnya mengembalikan #VALUE! Kesalahan, tetapi jika Anda menekan F2, F9, Anda akan melihat bahwa itu mengembalikan array.
• 13 fungsi pertama di AGGREGATE tidak dapat menangani array, tetapi fungsi 14-19 dapat menangani array.
• 5 = MIN dan 15 = SMALL (, 1) serupa, tetapi SMALL (, 1) akan bekerja dengan array.
• LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX, dan AGGREGATE dapat menangani argumen array fungsi tanpa Ctrl + Shift + Enter
• Mike lebih pintar dengan melihat apakah 2 karakter sebelum Start adalah angka, dan kemudian mengambil 3 karakter sebelumnya. Ruang ekstra dihilangkan oleh TRIM ()
• Untuk mendapatkan Judul, gunakan fungsi SUBSTITUTE untuk menghilangkan teks Tanggal di kolom C

Transkrip Video

Bill Jelen: Hei, selamat datang kembali. Saatnya untuk Podcast Excel Duel lainnya. Saya Bill Jelen dari. Saya akan bergabung dengan Mike Girvin dari Excel Is Fun.

Ini adalah Duel # 170 kami: Menemukan Tanggal

Hei, selamat datang kembali semuanya. Saya memiliki pertanyaan yang sangat bagus di sini dan saya tidak dapat menyelesaikannya. Setidaknya saya tidak bisa menyelesaikannya dengan mudah jadi saya pergi ke Mike Girvin dan saya berkata, "Mike, hei, apakah Anda punya cara untuk melakukan ini?" Dia berkata, “Ya, saya punya cara untuk melakukannya. Ayo lakukan Duel. ”

Jadi, seseorang di YouTube mengirimkan data ini dan setiap sel pada umumnya memiliki sesuatu seperti judul dokumen yang diikuti dengan tanggal. Mereka ingin memecah data ini ke dalam judul dokumen: apa itu, apa masalahnya dan kemudian berapa tanggalnya. Tapi tanggalnya benar-benar jahat. Seperti di sini, tanggal 20 Januari; tapi di sini, ada hal-hal yang tanggalnya mungkin setelah sel, 9 April. Baiklah, dan bagaimanapun caranya kami ingin menemukannya. Dan kadang-kadang ada dua tanggal dan ini benar-benar mengerikan dan itu hanya situasi yang campur aduk dan mungkin, bahkan tidak ada tanggal yang muncul, oke. Jadi, inilah usaha saya. Di sisi kanan, saya akan meletakkan hal-hal yang saya cari. Yang sangat saya suka di sini adalah mereka tidak pernah menyingkat nama bulan. Aku sungguh,sangat menghargai itu. Jadi ketik Januari dan saya akan menyeretnya ke sini hingga Desember seperti itu, dan untuk setiap sel yang ingin saya ketahui dapat kita temukan = TEMUKAN Januari itu. Jadi saya akan menekan F4 satu, dua kali untuk menguncinya menjadi hanya satu baris, di teks di sana di Kolom A, seperti itu. Saya akan menekan F4 satu, dua, tiga kali untuk menguncinya ke kolom, oke. Dan di sini, kami diberitahu bahwa Januari ada di Posisi 32 dan selama 11 bulan lainnya, ini akan memberi tahu kami bahwa itu tidak ditemukan sama sekali. Dengan kata lain, kita mendapatkan kesalahan Nilai sekarang. Yang perlu saya lakukan di sana adalah saya perlu menemukan, saya perlu menemukan nilai minimum dengan mengabaikan semua kesalahan nilai. Jadi, perlihatkan rumus kecil ini di sini = AGGREGATE dan mari kita buat dari awal saja, = AGGREGATE, yang kita inginkan adalah MIN jadi itu angka 5,lalu Abaikan nilai kesalahan nomor 6 koma dan kemudian semua sel ini dari Januari hingga Desember. Dan apa yang akan memberi tahu kita adalah itu akan memberi tahu kita di mana bulan itu terjadi. Dan dalam kasus ini, kita akan mendapatkan 0, katakanlah bulan itu tidak terjadi sama sekali.

Baiklah sekarang, mari kita perlihatkan sisanya. Jadi, untuk menangani situasi di mana kita memiliki 20 Januari atau 1 November, saya mengatakan hal pertama yang akan saya lakukan adalah saya akan melihat di mana bulan itu dimulai dan kembali ke dua sel, dua sel, dua karakter , dua karakter. Dan lihat apakah itu sebuah angka, bukan begitu. Itu kolom saya di sini bernama, Adjust2. Sesuaikan2. Dan inilah yang akan kami lakukan. Saya akan mengatakan, ambil MID dari A2 memulainya di mana di G2-2 untuk panjang 1, tambahkan 0 dan tanyakan, apakah itu angka atau tidak? Baiklah, itu nomornya. Dan kemudian, kami juga akan mencari situasi di mana itu adalah tanggal 2 digit, jadi 20 Januari. Jadi yang disebut Adjust3, kembali 3 karakter dari mana. Jadi ada Di Mana, kembali tiga karakter dengan panjang 1, tambahkan 0 padanya, dan lihat apakah itu 'nomor, oke? Kemudian kita akan menyesuaikan dan Adjusted Where mengatakan IF. JIKA kasus aneh ini adalah 0, kami hanya akan memberikan nilai yang sangat besar 999; jika tidak, kita akan beralih dari G2 dan kembali ke 3, jika Adjust3 Benar atau kembali 2 jika Adjust2 Benar, atau jika tidak ada yang Benar, Di mana akan menjadi tempat bulan dimulai. Baiklah, sekarang kita tahu bahwa Adjusted Where itu, kita akan klik dua kali untuk menyalinnya. Nah, hei sekarang, itu sangat mudah. Kami hanya akan - untuk Judul, kami akan mengatakan ambil kiri A2, berapa banyak karakter yang kami inginkan. Kami menginginkan D2-1 karena itulah -1 untuk menghilangkan ruang di akhir. Meskipun saya kira TRIM juga menghilangkan ruang di bagian akhir.JIKA kasus aneh ini adalah 0, kami hanya akan memberikan nilai yang sangat besar 999; jika tidak, kita akan beralih dari G2 dan kembali ke 3, jika Adjust3 Benar atau kembali 2 jika Adjust2 Benar, atau jika tidak ada yang Benar, Dimana akan menjadi tempat bulan dimulai. Baiklah, sekarang kita tahu bahwa Adjusted Where itu, kita akan klik dua kali untuk menyalinnya. Nah, hei sekarang, itu sangat mudah. Kami hanya akan - untuk Judul, kami akan mengatakan ambil kiri A2, berapa banyak karakter yang kami inginkan. Kami ingin D2-1 karena itulah -1 untuk menghilangkan ruang di akhir. Meskipun saya kira TRIM juga menghilangkan ruang di bagian akhir.JIKA kasus aneh ini adalah 0, kami hanya akan memberikan nilai yang sangat besar 999; jika tidak, kita akan beralih dari G2 dan kembali ke 3, jika Adjust3 Benar atau kembali 2 jika Adjust2 Benar, atau jika tidak ada yang Benar, Di mana akan menjadi tempat bulan dimulai. Baiklah, sekarang kita tahu bahwa Adjusted Where itu, kita akan klik dua kali untuk menyalinnya. Nah, hei sekarang, itu sangat mudah. Kami hanya akan - untuk Judul, kami akan mengatakan ambil kiri A2, berapa banyak karakter yang kami inginkan. Kami menginginkan D2-1 karena itulah -1 untuk menghilangkan ruang di akhir. Meskipun saya kira TRIM juga menghilangkan ruang di bagian akhir.atau jika tidak ada satupun yang Benar, Dimana akan menjadi tempat awal bulan. Baiklah, sekarang kita tahu bahwa Adjusted Where itu, kita akan klik dua kali untuk menyalinnya. Nah, hei sekarang, itu sangat mudah. Kami hanya akan - untuk Judul, kami akan mengatakan ambil kiri A2, berapa banyak karakter yang kami inginkan. Kami menginginkan D2-1 karena itulah -1 untuk menghilangkan ruang di akhir. Meskipun saya kira TRIM juga menghilangkan ruang di bagian akhir.atau jika tidak ada satupun yang Benar, Dimana akan menjadi tempat awal bulan. Baiklah, sekarang kita tahu bahwa Adjusted Where itu, kita akan klik dua kali untuk menyalinnya. Nah, hei sekarang, itu sangat mudah. Kami hanya akan - untuk Judul, kami akan mengatakan ambil kiri A2, berapa banyak karakter yang kami inginkan. Kami ingin D2-1 karena itulah -1 untuk menghilangkan ruang di akhir. Meskipun saya kira TRIM juga menghilangkan ruang di bagian akhir.s the -1 adalah untuk menghilangkan spasi di bagian akhir. Meskipun saya kira TRIM juga menghilangkan ruang di bagian akhir.s the -1 adalah untuk menghilangkan spasi di bagian akhir. Meskipun saya kira TRIM juga menghilangkan ruang di bagian akhir.

Dan untuk tanggalnya, kami akan menggunakan MID. MID untuk-MID A2 dimulai dari Adjusted Where di D2 dan keluar 50 atau apa pun yang menurut Anda mungkin, dan kemudian fungsi TRIM, dan kami akan mengklik dua kali untuk menyalinnya.

Baiklah sekarang, alasan saya menghubungi Mike adalah saya berkata, Saya ingin tahu apakah ada cara agar saya bisa mengganti 12 kolom ini dengan satu formulir, sebenarnya 13 kolom ini dengan satu formulir. Adakah cara agar saya dapat melakukan ini menggunakan rumus Array? Dan Mike, tentu saja, menulis buku hebat itu, Ctrl + Shift + Enter, tentang rumus Array. Dan saya mencoba beberapa hal berbeda dan dalam pikiran saya, tidak mungkin itu bisa dilakukan. Baiklah, tapi Anda tahu, mari kita tanyakan pada ahlinya. Jadi Mike, mari kita lihat apa yang Anda miliki.

Mike Girvin: Terima kasih ,. Hei, dan berbicara tentang ahli, ini dilakukan dengan sangat ahli. Anda menggunakan FIND, AGGREGATE, ISNUMBER (MID. Sekarang, ketika Anda mengirimkan pertanyaan ini kepada saya, saya melanjutkan dan menyelesaikannya dan sungguh menakjubkan betapa miripnya solusi saya dengan solusi Anda.

Alright, I'm going to go over to this sheet here. I'm going to start with figuring out where the start position in this text string is for each particular month. Now the way I'm going to do it is I'm going to, hey, use this SEARCH function. Now, you used FIND, I use SEARCH. Actually probably FIND is better in this situation because FIND is case-sensitive, SEARCH is not. Now normally what we do with either FIND or SEARCH, I say, hey, go FIND, January, comma within this larger text string, that's how we normally use SEARCH Ctrl+Enter, and it counts on its finger: one, two, three, four, five. It says the 32nd character is where it found January.

Now, instead of doing it in many cells across the columns, I'm going to hit F2, come up here and the FIND_TEXT. Notice we gave it 1 item, SEARCH gave us 1 answer. But if I highlight the entire column of month names, now instead of a single item I put many items in there. This is a Function Argument. We're putting an array of items in and so that means we're doing a Function Argument Array operation. Any time you do that, you tell the function, hey, give me 12 answers, 1 for each month. Now this will deliver an array so if I try to Enter this and copy down it's not going to work.

Well, let's go down to any particular cell, F2 and then F9 to look that yes, in fact, it is delivering an array, and look at that. It looks like I F2 up here, forgot to lock it. So I'm going to click on that and F4, Ctrl+Enter, double click and send it down, F2, F9. There we go, that's that array. There's exactly 12 answers and there's the 34 and the 55. Now, from this array, since we always want the actual first month, not the second month, we want whatever the MIN is because those are number of characters in from the left. So I'm going to click Escape, come up to the top F2. I'm going to use the AGGREGATE function. Hey, we would like to use AGGREGATE 5 but no matter how hard you try if you have an array operation and we do here, if you try to put any function 1 to 13, it just doesn't work. But no problem, we have SMALL, so number 15 comma. Any one of those functions 14 to 19, they understand array operations. And once you select 14 or above, this is the screen tip you're working off, not this bottom one with the references. Alright, comma.

The second options here we want to Ignore errors, comma. That number 6 will then tell AGGREGATE to look through here and ignore the errors. It will only see the numbers. And this is one of five functions in Excel: LOOKUP some product, CHI SQUARE TEST, AGGREGATE, and INDEX that actually have a special argument that can handle Array operations without doing any special key stroke. So there is the Array, comma, and then for K we simply put A1. That's our way of getting them in. Close parentheses, Ctrl+Enter, double click and send it down. And so that tells us the position where it found the first month name from this list.

Now, we'll deal with the NUM error at the very end in our final formula. Now, we are going to have to take these and notice that sometimes there's a number before the month and sometimes, like down here in December, there is not. So I'm going to do the same thing did. I'm going to go back two characters and check whether it is a letter or in this case a number =MID, there’s the text, comma, the starting position. Well, I want to start at 32 in this case and -2 to go back to and comma. I get exactly one character. Now, if I close parenthesis MID LEFT RIGHT they all deliver text, double click and send it down and we want to check if it's a number. So watch this, the whole column is highlighted. Active cell at the top, I'm going to hit F2. We could do any Math operation to convert text numbers back to number so I'm going to add 0, Ctrl+Enter to populate this edited formula down through the column. Ctrl+Enter. Now, we can ask the question: Is the returned item a number? F2. So now I say ISNUMBER, close parenthesis, Ctrl+Enter. So now we have a pattern of Trues and Falses. Now, remember we need to get the starting position and for 32 we're definitely going to have to subtract 3 and start at that 20 but notice down here, we don't want to subtract any. So our logical test if I hit F2, that will simply be put into the IF Logical Test Argument. If that comes out True comma then I want to jump back 3 comma. Otherwise I want to jump back 0, close parenthesis, Ctrl+Enter to populate that all the way down. Now we can take this number and subtract the number over here to give us our starting position. Active cell at the top F2, I'm putting this inside of MID. There's the text, comma. And can you believe it? All of this to get the start number. So I'm going to click on that B2 and subtract our IF, come to the end comma and I'm just going to put a big number in here, 100, some big number big enough to get all the way to the end, close parenthesis and Ctrl+Enter to populate that all the way down. It looks like we have some extra spaces and that makes sense because right here we went back three, so no problem. Active cell at the top, F2, I'm going to use the haircut function, the diet function. No, the TRIM function to remove extra spaces except for single spaces between words. Come to the end, close parenthesis, Ctrl+Enter to populate that all the way down.

Now, I have the date, oh, except for the NUM. Now, I could come to the top and use IF error but then it would run all of these plus that cell right there and for a small data set, it doesn't matter at all; but, with the goal of efficiency, I'm going to say IF(ISNUMBER and I'm going to click on that cell, that way close parenthesis, comma. The trigger for whether we run the formula is only based on that instead of the entire formula. If that comes out True, we want to run the formula, comma. Otherwise, double quote double quote. That zero link text string will show nothing. Ctrl+Enter, double click and send it down. And now, all we need to do is get the Title. Well, I already have the text that I don't want in here so I'm going to use the SUBSTITUTE function. SUBSTITUTE, there's the text, comma. The old text, it's that right there, comma, the new text. Hey, I want to take that and SUBSTITUTE in nothing. There's our zero link text string, Ctrl+Enter, double click and send it down.

Now, I'm going to come over here to column B, right click, Hide and there we go. Alright, throw it back to.

Bill Jelen: Hey, Mike, that is brilliant and I know exactly, exactly where I went wrong. Right here in row 12 when the formula returned the #VALUE error, you pressed F2, F9 to see that it's returning an array. When I got the #VALUE error, I just swore a little bit and said, why isn't this working? Never thought of pressing F2, F9, alright. Also, like that, of course, MIN and SMALL(,1) are the same but the difference is SMALL(,1) will work with an array in the AGGREGATE function. That was a beautiful, beautiful trick. And then, I went through that whole hassle to look at 2 characters before and 3 characters before. You were smart enough to say, “Hey, we're going to go 2 characters before and if so, go back 3 characters.” Worst case you get a space for that extra space and eliminated by the TRIM. And then the cherry on top, using SUBSTITUTE function to get rid of the Date text in column C. What a brilliant, brilliant way to go, alright.

Jadi, saya ingin berterima kasih kepada semua orang karena telah mampir. Sampai jumpa di lain waktu untuk Dueling Excel Podcast dari dan Excel Is Fun.

Unduh berkas

Unduh file sampel di sini: Duel180.xlsm