Прост на пръв поглед проблем с неочевидно решение: извличане на последната дума от ред текст. Е, или в общия случай последният фрагмент, разделен с даден разделителен знак (интервал, запетая и т.н.) С други думи, необходимо е да се приложи обратно търсене (от край към начало) в низа на даден знак и след това извлечете всички знаци вдясно от него.
Нека да разгледаме традиционно няколко начина за избор: формули, макроси и чрез Power Query.
Метод 1. Формули
За да разберем по-лесно същността и механиката на формулата, нека започнем малко отдалеч. Първо, нека увеличим броя на интервалите между думите в нашия изходен текст до например 20 броя. Можете да направите това с функцията за замяна. ЗАМЕСТИТЕЛ (ЗАМЕСТВАНЕ) и функцията за повтаряне на даден знак N пъти – REPEAT (ПОВТОРЕНИЕ):
Сега отрязваме 20 знака от края на получения текст с помощта на функцията НАДЯСНО (НАДЯСНО):
Става по-топло, нали? Остава да премахнете излишните интервали с помощта на функцията TRIM (ОТРИМ) и проблемът ще бъде решен:
В английската версия нашата формула ще изглежда така:
=ОТРАЗВАНЕ(НАДЯСНО(ЗАМЕНЯНЕ(A1;» «;ПОВТОР(» «;20));20))
Надявам се, че е ясно, че по принцип не е необходимо да се вмъкват точно 20 интервала – всяко число ще свърши работа, стига да е повече от дължината на най-дългата дума в изходния текст.
И ако изходният текст трябва да бъде разделен не с интервал, а с друг разделителен знак (например запетая), тогава нашата формула ще трябва да бъде леко коригирана:
Метод 2. Макро функция
Задачата за извличане на последната дума или фрагмент от текста също може да бъде решена с помощта на макроси, а именно, писане на функция за обратно търсене във Visual Basic, която ще направи това, от което се нуждаем – търсене на даден подниз в низ в обратна посока – от края към началото.
Натиснете клавишната комбинация Друг+F11 или бутон Visual Basic етикет предприемач (Разработчик)за да отворите редактора на макроси. След това добавете нов модул чрез менюто Вмъкване – Модул и копирайте следния код там:
Функция LastWord(txt като низ, разделяне по избор като низ = " ", по избор n като цяло число = 1) като низ arFragments = Split(txt, разделяне) LastWord = arFragments(UBound(arFragments) - n + 1) Крайна функция
Сега можете да запишете работната книга (във формат с активиран макрос!) и да използвате създадената функция в следния синтаксис:
=Последна дума(txt; разделяне; n)
където
- TXT – клетка с изходен текст
- разделител — разделителен знак (по подразбиране — интервал)
- n – каква дума да се извлече от края (по подразбиране – първата от края)
С всякакви промени в изходния текст в бъдеще, нашата макро функция ще бъде преизчислена в движение, като всяка стандартна функция за лист на Excel.
Метод 3. Power Query
Запитване за захранване е безплатна добавка от Microsoft за импортиране на данни в Excel от почти всеки източник и след това трансформиране на изтеглените данни във всякаква форма. Силата и готината на тази добавка са толкова големи, че Microsoft е вградила всички нейни функции в Excel 2016 по подразбиране. За Excel 2010-2013 Power Query можете да изтеглите безплатно от тук.
Нашата задача да разделим последната дума или фрагмент чрез даден разделител с помощта на Power Query се решава много лесно.
Първо, нека превърнем нашата таблица с данни в интелигентна таблица с помощта на клавишни комбинации. Ctrl+T или команди Начало – Форматиране като таблица (Начало — Форматиране като таблица):
След това зареждаме създадената „интелигентна таблица“ в Power Query с помощта на командата От таблица/обхват (От таблица/обхват) етикет Дата (ако имате Excel 2016) или в раздела Запитване за захранване (ако имате Excel 2010-2013):
В прозореца на редактора на заявки, който се отваря, в раздела Трансформация (Трансформиране) изберете екип Разделяне на колона – чрез разделител (Разделяне на колона — по разделител) и след това остава да зададете разделителния знак и да изберете опцията Най-десният разделителда изрежете не всички думи, а само последната:
След като кликнете върху OK последната дума ще бъде отделена в нова колона. Ненужната първа колона може да бъде премахната, като щракнете с десния бутон върху нейната заглавка и изберете Премахване (Изтрий). Можете също да преименувате останалата колона в заглавката на таблицата.
Резултатите могат да бъдат качени обратно в листа с помощта на командата Начало — Затвори и зареди — Затвори и зареди в... (Начало — Затвори и зареди — Затвори и зареди в...):
И в резултат получаваме:
Така – евтино и весело, без формули и макроси, почти без докосване на клавиатурата 🙂
Ако първоначалният списък се промени в бъдеще, ще бъде достатъчно да щракнете с десния бутон или да използвате клавишна комбинация Ctrl+Друг+F5 актуализирайте нашата заявка.
- Разделяне на лепкав текст в колони
- Разбор и разбор на текст с регулярни изрази
- Извличане на първите думи от текста с функцията SUBSTITUTE