Hôm nay là một câu hỏi điên rồ. Bạn có một cột số bộ phận. Có từ 4 đến 7 dấu gạch ngang trong phần số. Bạn chỉ muốn trích xuất một phần của số bộ phận sau dấu gạch ngang đầu tiên và lên đến nhưng không bao gồm dấu gạch ngang cuối cùng. Đây là một tập Excel đấu tay đôi.
Xem video
- Mục tiêu là tìm dấu gạch ngang đầu tiên và cuối cùng và giữ mọi thứ ở giữa
- Phần khó ở đây là tìm dấu gạch ngang cuối cùng
- Phương pháp hóa đơn 1: Điền vào Flash
- Điền thủ công vào một số đầu tiên (bao gồm một số với số lượng dấu gạch ngang khác nhau)
- Chọn ô trống bên dưới
- Ctrl + E để Flash Fill
- Mike Phương pháp 2:
- Sử dụng Power Query
- Trong Excel 2016, Power Query nằm trong nhóm Get & Transform trong Excel 2016
- Trong Excel 2010 & 2013, tải xuống Power Query từ Microsoft. Nó tạo một tab Power Query mới trong Ribbon
- Chuyển đổi dữ liệu của bạn thành một bảng bằng Ctrl + T
- Sử dụng Dữ liệu phân tách trong Power Query - trước tiên để tách ở dấu gạch ngang ngoài cùng bên trái, sau đó để tách ở dấu gạch ngang ngoài cùng bên phải
- Phương thức hóa đơn 3:
- Hàm VBA lặp lại từ cuối ô trở lại để tìm dấu gạch ngang cuối cùng
- Mike Phương pháp 4:
- Sử dụng SUBSTITUTE để tìm vị trí của dấu gạch ngang thứ N
- SUBSTITUTE là hàm văn bản duy nhất cho phép bạn chỉ định số Phiên bản
- Để tìm số phiên bản, hãy sử dụng
=LEN(A2)-LEN(SUBSTITUTE)
Bản ghi video
Bill: Này. Chào mừng trở lại. Đã đến lúc cho một podcast Dueling Excel khác. Tôi là Bill Jelen từ MrExcel. (Tôi sẽ được tham gia bởi Mike Girvin từ ExcelIsFun. Đây là - 00:03 của chúng ta) tập 185: trích từ phần đầu tiên - đến phần cuối cùng -.
Ổn thỏa. Câu hỏi hôm nay do Anvar gửi trên YouTube. Làm cách nào để tôi có thể trích xuất mọi thứ từ cái đầu tiên - đến cái cuối cùng - và kiểm tra dữ liệu mà anh ấy có ở đây. Có rất nhiều dấu gạch ngang, từ 3, 5, 6, 7 dấu gạch ngang, được chứ?
Vì vậy, suy nghĩ đầu tiên của tôi là, à, này, thật sự dễ dàng để tìm thấy cái đầu tiên -, phải không? = left hoặc = MID của FIND của A2 và sau đó là -, +1 được rồi, nhưng để đến cuối cùng -, điều đó sẽ khiến tôi đau đầu, phải, bởi vì, chúng ta có bao nhiêu dấu gạch ngang? Chúng ta có thể lấy SUBSTITUTE của A2, thay thế các dấu gạch ngang và so sánh độ dài của nó, độ dài ban đầu. Điều đó cho tôi biết số lượng dấu gạch ngang, nhưng bây giờ tôi biết cái nào - để tìm, thứ 2, 3, 4, 5, nhưng tôi có sử dụng FIND không?
Tôi đã sẵn sàng để tham gia VBA, phải không? Đó là phản ứng giật đầu gối của tôi. Tôi nói, chờ một chút. Tôi nói, Anvar, bạn đang sử dụng phiên bản Excel nào? Anh ấy nói, tôi đang ở trong Excel 2016. Tôi nói, điều đó thật đẹp. Nếu bạn đang sử dụng Excel 2013 hoặc mới hơn, chúng tôi có thể sử dụng tính năng mới tuyệt vời này được gọi là điền nhanh. Với flash fill, chúng tôi chỉ cần tạo cho nó một mẫu và tôi sẽ tạo cho nó đủ một mẫu để không chỉ là tôi chụp một với hai dấu gạch ngang và làm điều đó một vài lần. Tôi muốn đảm bảo rằng tôi có một vài dấu gạch ngang khác nhau theo cách đó. Chad trong nhóm Excel biết những gì tôi đang tìm kiếm. Chad là người đã viết logic cho flash fill. Vì vậy, tôi nhận được khoảng 3 trong số chúng trong đó và sau đó CONTROL + E là phím tắt để sử dụng DATA và sau đó FLASH FILL, và chắc chắn, có vẻ như nó đã làm đúng. Được rồi, Mike.Hãy xem những gì bạn có.
Mike: Cảm ơn, MrExcel. Vâng. Điền flash thắng. Tính năng ngay đó, flash fill, là một trong những công cụ Excel hiện đại, đơn giản là tuyệt vời. Nếu đó là thỏa thuận một lần và bạn có một mô hình nhất quán, thì đó là cách tôi sẽ làm.
Này, hãy chuyển sang trang tiếp theo. Bây giờ, thay vì sử dụng lấp đầy flash, chúng ta thực sự có thể sử dụng truy vấn nguồn. Bây giờ, tôi đang sử dụng Excel 2016 nên tôi có nhóm GET & TRANSFORM. Đó là truy vấn nguồn. Trong các phiên bản trước đó, 2013 (đến 10 - 2:30), bạn thực sự phải tải xuống phần bổ trợ truy vấn nguồn miễn phí.
Bây giờ, để truy vấn nguồn hoạt động, điều này phải được chuyển đổi thành bảng Excel. Bây giờ, một lần nữa, tôi sẽ sử dụng flash fill nếu đây là thỏa thuận một lần. Khi nào bạn sẽ sử dụng truy vấn nguồn? Chà, nếu bạn có dữ liệu thực sự lớn hoặc đến từ một nguồn bên ngoài, đây sẽ là cách để thực hiện, hoặc thậm chí bạn có thể thích điều này hơn là phải nhập 3 hoặc 4 ví dụ cho flash fill bởi vì, với power query, chúng ta có thể nói cụ thể là tìm cái đầu tiên - và tìm cái cuối cùng -.
Bây giờ, tôi sẽ chuyển nó thành một bảng Excel. Tôi đã chọn một ô duy nhất, các ô trống xung quanh. Tôi đi tới CHÈN, BẢNG, hoặc bạn sử dụng bàn phím, CONTROL + T. Tôi có thể nhấp vào OK hoặc ENTER. Tôi muốn đặt tên cho bảng này, vì vậy tôi sẽ chuyển đến CÔNG CỤ BẢNG, THIẾT KẾ, chuyển đến PROPERTIES. Tôi sẽ gọi đây là STARTKEYTABLE và ENTER. Bây giờ tôi có thể quay lại DATA, đưa nó vào truy vấn nguồn bằng cách sử dụng nút FROM TABLE. Đây là cột của tôi. Có tên. Tôi không muốn giữ tên này vì kết quả sẽ được xuất sang Excel và tôi muốn đặt tên khác cho nó. Vì vậy, tôi sẽ gọi nó là CLEANEDKEYTABLE. Tôi không cần LOẠI ĐÃ ĐỔI đó. Tôi chỉ đang xem xét nguồn. Bây giờ tôi có thể nhấp vào cột và ngay trong TRANG CHỦ, có nút SPLIT. Tôi có thể nói SPLIT, BY DELIMITER. Có vẻ như nó đã đoán ra rồi. TÔI'tôi sẽ nói LEFT-MOST. Bấm OK.
Bây giờ, nếu tôi nhìn qua đây, tôi thấy LOẠI ĐÃ ĐỔI. Tôi không cần điều đó nên tôi sẽ bỏ bước đó. Tôi chỉ có SPLIT COLUMN BY DELIMITER. Bây giờ, tôi sẽ làm điều này một lần nữa, nhưng thay vì sử dụng nút SPLIT ở đây, hãy nhấp chuột phải xuống SPLIT COLUMN, BY DELIMITER và nhìn vào đó. Chúng ta có thể chọn chia nó theo GIỚI HẠN ĐÚNG-NHẤT. Bấm OK. Bây giờ, tôi không cần hai cột này vì vậy tôi sẽ nhấp chuột phải vào cột tôi muốn giữ lại, XÓA CÁC CỘT KHÁC. Tôi thực sự sẽ X LOẠI ĐÃ THAY ĐỔI này. Nó sẽ nói rằng BẠN CÓ CHẮC LÀ BẠN MUỐN XÓA ĐIỀU NÀY KHÔNG? Tôi sẽ nói, vâng, XÓA. Có dữ liệu sạch của tôi.
Bây giờ tôi có thể đi đến ĐÓNG & TẢI. ĐÓNG & TẢI VÀO. Đây là hộp thoại IMPORT mới. Nó được sử dụng để nói TẢI ĐẾN nhưng tôi muốn tải nó vào một bảng, trên một BẢNG CÔNG TRÌNH HIỆN CÓ. Nhấp vào nút thu gọn. Tôi sẽ chọn C1, mở rộng, nhấp vào OK, và chúng ta bắt đầu. Truy vấn nguồn để làm sạch dữ liệu của chúng tôi và chỉ lấy dữ liệu chúng tôi muốn. Ổn thỏa. Tôi sẽ ném nó trở lại.
Bill: Có một điểm ở ngay đó, RIGHT-NHIỀU NHẤT DELIMITER trong SPLIT COLUMN BY DELIMITER, một trong những tính năng thú vị trong power query. Thật tuyệt vời.
Ổn thỏa. Phản ứng giật đầu gối của tôi - VBA UDF (khó hiểu - 05:34) thực sự dễ dàng để thực hiện VBA. Chuyển sang ALT + F11. CHÈN MỘT MODULE. Trong mô-đun đó, hãy nhập mã này. Tôi sẽ (tạo - 05:43) chức năng hoàn toàn mới, tôi sẽ gọi nó là MIDPART, và tôi sẽ chuyển nó một số văn bản, và sau đó những gì tôi sẽ làm là đi từ ký tự cuối cùng trong ô đó từ độ dài MYTEXT trở lại 1, BƯỚC -1 và xem ký tự đó. Vì vậy, MID của MYTEXT, biến i đó, cho chúng ta biết ký tự nào chúng ta đang xem xét có độ dài là 1. Nó có phải là - không? Ngay sau khi tôi tìm thấy dấu -, tôi sẽ lấy TRÁI của MYTEXT bắt đầu từ ký tự i - 1, vì vậy tôi loại bỏ mọi thứ cho lần cuối cùng đó - tất cả các cách và sau đó, hãy đảm bảo rằng tôi không đi tiếp tục tìm kiếm thêm dấu gạch ngang, EXIT FOR sẽ giúp tôi thoát khỏi vòng lặp (khó hiểu - 06:17) này,và từ đó là phần dễ dàng. Chúng ta sẽ chỉ lấy MYTEXT, bắt đầu ở MID của MYTEXT, (nơi tôi sử dụng - 06:26) sử dụng hàm FIND để tìm đầu tiên -, đi thêm 1 lần nữa và trả về.
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.
Vâng, bạn đi rồi. Tôi muốn cảm ơn mọi người đã ghé qua. Chúng tôi sẽ gặp lại bạn vào lần tới với một podcast khác của Dueling Excel từ và ExcelIsFun.
Tải tập tin
Tải file mẫu tại đây: Duel185.xlsm
