Сьогодні це шалене питання. У вас є стовпець номерів деталей. У номері деталі є від 4 до 7 тире. Ви хочете витягти лише частину номера деталі після першого тире і до, але не включаючи останнього тире. Це дуельний епізод Excel.
Переглянути відео
- Мета полягає в тому, щоб знайти перший і останній тире і зберегти все між ними
- Важкою частиною тут є пошук останньої риски
- Метод рахунку 1: Flash Fill
- Вручну заповніть перші кілька (включаючи деякі з різною кількістю рисок)
- Виділіть порожню комірку під цим
- Ctrl + E для заливки Flash
- Метод Майка 2:
- Використовуйте Power Query
- В Excel 2016 Power Query знаходиться в групі Отримати та перетворити в Excel 2016
- В Excel 2010 та 2013 завантажте Power Query від Microsoft. Він створює нову вкладку Power Query на стрічці
- Перетворіть дані в таблицю, використовуючи Ctrl + T
- Використовуйте розділення даних у Power Query - спочатку для розділення на крайньому лівому штриху, а потім - на крайньому правому штриху
- Метод рахунку 3:
- Функція VBA, яка виконує ітерацію від кінця комірки назад, щоб знайти останній тире
- Метод Майка 4:
- Використовуйте ЗАМІННИК, щоб знайти місце N-го тире
- ЗАМІНА - це єдина текстова функція, яка дозволяє вказати номер екземпляра
- Щоб знайти номер екземпляра, використовуйте
=LEN(A2)-LEN(SUBSTITUTE)
Стенограма відео
Білл: Ей. З поверненням. Настав час чергового подкасту Duel Excel. Я Білл Джелен із MrExcel. (До мене приєднається Майк Гірвін з ExcelIsFun. Це наш - 00:03) епізод 185: витяг з першого - до останнього -.
Гаразд Сьогоднішнє запитання надсилає Anvar на YouTube. Як я можу витягти все від першого - до останнього - і перевірити ці дані, які він має тут. Існує величезна кількість тире, десь від 3, 5, 6, 7 тире, добре?
Отже, моя перша думка: ну, привіт, дійсно легко знайти першу - так? = ліворуч = = ПОСЕРЕДИНА ЗНАХОДЖЕННЯ А2, а потім -, +1 гаразд, але щоб дійти до останнього -, це призведе до болю в голові, так, адже, ну, скільки тире у нас? Ми могли б взяти ЗАМІННИК A2, замінивши тире, і порівняти довжину цього, початкову довжину. Це говорить мені кількість тире, але тепер я знаю, який - знайти, 2-й, 3-й, 4-й, 5-й, але чи використовую я FIND?
Я був готовий піти на VBA, так? Це моя реакція на ривок у колінах. Я сказав, почекай секунду. Я сказав, Anvar, у якій ти версії Excel? Він каже, я в Excel 2016. Я сказав, це прекрасно. Якщо ви використовуєте Excel 2013 або новішу версію, ми могли б скористатися цією чудовою новою функцією, яка називається flash fill. За допомогою флеш-заливки нам просто потрібно надати йому шаблон, і я збираюся надати йому достатньо шаблону, так що це не просто те, що я беру один із двома рисками і роблю це пару разів. Я хочу переконатися, що таким чином у мене є кілька різних рисок. Чад із команди Excel знає, що я шукаю. Чад - хлопець, який написав логіку для заповнення спалахом. Отже, я отримую там приблизно 3 з них, а потім CONTROL + E - це ярлик для використання ДАНИХ, а потім FLASH FILL, і, звичайно, схоже, він зробив правильно. Гаразд, Майк.Давайте подивимось, що у вас є.
Майк: Дякую, MrExcel. Так. Flash заливка виграє. Ця функція тут, flash fill, є одним із сучасних інструментів Excel, який просто вражає. Якщо це разова угода, і у вас є послідовна схема, привіт, я би так зробив.
Гей, перейдемо до наступного аркуша. Тепер замість того, щоб використовувати флеш-заливку, ми можемо фактично використовувати енергетичний запит. Зараз я використовую Excel 2016, тому маю групу GET & TRANSFORM. Це енергетичний запит. У попередніх версіях, 2013 (до 10 - 2:30), ви фактично повинні завантажити надбудову з безкоштовним запитом живлення.
Тепер, щоб змусити запит живлення працювати, його потрібно перетворити в таблицю Excel. Тепер, знову ж таки, я б використовував флеш-заливку, якби це була разова угода. Коли б ви використовували енергетичний запит? Ну, якщо у вас були справді великі дані або ви надходили із зовнішнього джерела, це був би шлях, або вам це може навіть сподобатися краще, ніж потрібно вводити 3 або 4 приклади для флеш-заповнення, тому що за запитом живлення ми можемо конкретно скажіть знайти перше - і знайти останнє -.
Тепер я збираюся перетворити це в таблицю Excel. У мене вибрано одну клітинку, порожні клітини навколо. Я переходжу до INSERT, TABLE, або ви використовуєте клавіатуру, CONTROL + T. Я можу натиснути OK або ENTER. Я хочу назвати цю таблицю, тому я перейду до ТАБЛИЦІ ІНСТРУМЕНТІВ, ДИЗАЙН, до ВЛАСТИВОСТІ. Я збираюся назвати це STARTKEYTABLE та ENTER. Тепер я можу повернутися до ДАНИХ, перевести їх у запит живлення за допомогою кнопки ВІД ТАБЛИЦІ. Там моя рубрика. Там назва. Я не хочу зберігати це ім'я, оскільки вихідні дані будуть експортовані в Excel, і я хочу дати йому інше ім'я. Отже, я називатиму це ОЧИЩЕНИМ КЛЮЧОВИМ СТОЛОМ. Мені не потрібен ЗМІНЕНИЙ ТИП. Я просто дивлюсь на джерело. Тепер я можу натиснути на стовпець і, прямо в HOME, є кнопка SPLIT. Я можу сказати РОЗДІЛ, ЗА ДЕЛІМЕРОМ. Схоже, це вже здогадалося. Яя збираюся сказати ВЛІВО-НАЙБІЛЬШЕ. Клацніть OK.
Тепер, якщо я зазирну сюди, то побачу ЗМІНЕНИЙ ТИП. Мені це не потрібно, тому я збираюся позбутися цього кроку. У мене є лише РОЗДІЛИТИ СТОЛБКУ НА ДЕЛІМЕТР. Тепер я збираюся зробити це ще раз, але, замість того, щоб використовувати кнопку РОЗДІЛ тут, клацніть правою кнопкою миші до РОЗДІЛИТИ СТОЛБКУ, ДЕЛІМЕТРОМ, і подивіться на це. Ми можемо вирішити розділити його ПРАВО-НАЙБІЛЬШИМ РОЗДУМОМ. Клацніть OK. Тепер мені не потрібні ці два стовпці, тому я збираюся клацнути правою кнопкою миші стовпець, який я хочу зберегти, ВИДАЛИТИ ІНШІ СТОЛЬЦИ. Я фактично збираюся X цей ЗМІНЕНИЙ ТИП. Там буде сказано, ЧИ Впевнені, що хочете видалити це? Я збираюся сказати, так, ВИДАЛИТИ. Там мої чисті дані.
Тепер я можу підійти до ЗАКРИТИ І ЗАвантажити. ЗАКРИТИ І ЗАвантажити. Це нове діалогове вікно ІМПОРТ. Раніше говорилося ЗАВАНТАЖИТИ, але я хочу завантажити його до таблиці на Існуючому робочому аркуші. Натисніть кнопку згортання. Я збираюся вибрати C1, розгорнути, натиснути OK, і ми йдемо. Потужний запит на очищення наших даних і отримання лише тих даних, які ми хочемо. Гаразд Я відкину його назад.
Білл: Тут справа саме в цьому, ВПРАВО-САМИЙ РОЗМІНИК у РОЗДІЛІ СТОЛБЦІ ДЕЛІМЕТРОМ, одна з найцікавіших функцій запиту живлення. Це круто.
Гаразд Моя реакція ривка в колінах - VBA UDF (незрозуміле - 05:34) дуже проста у виконанні VBA. Перейдіть на ALT + F11. ВСТАВИТИ МОДУЛЬ. У цьому модулі введіть цей код. Я збираюся (створити - 05:43) нову функцію, я буду називати її MIDPART, і я передаю їй якийсь текст, а потім, що я буду робити, це я збирається перейти від останнього символу в цій клітинці з довжини MYTEXT назад до 1, КРОК -1 і подивитися на цей символ. Отже, MID MYTEXT, ця змінна i, говорить нам, який символ ми шукаємо для довжини 1. Це -? Як тільки я знайду -, я збираюся ВЛИВО МІТЕКСТУ, починаючи з символу i - 1, тому я позбавляюся від усього останнього - до кінця, а потім, переконайтеся, що я не йду продовжуйте шукати більше тире, EXIT FOR виведе мене з цього (незрозумілого - 06:17) циклу,а звідти - легка частина. Ми просто візьмемо MYTEXT, почнемо з MID MYTEXT, (де я використовую - 06:26), використовуємо функцію FIND, щоб знайти перший -, переходимо на 1 більше, ніж це, і повертаємо назад.
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.
Ну ось і ви. Я хочу подякувати усім, що завітали. Ми побачимо вас наступного разу для чергового подкасту дуелей Excel від і ExcelIsFun.
Завантажити файл
Завантажте зразок файлу тут: Duel185.xlsm
