Prosty na pierwszy rzut oka problem z nieoczywistym rozwiązaniem: wyodrębnij ostatnie słowo z wiersza tekstu. Cóż, lub w ogólnym przypadku ostatni fragment oddzielony danym znakiem ogranicznika (spacja, przecinek itp.) Innymi słowy, konieczne jest zaimplementowanie wyszukiwania wstecznego (od końca do początku) w ciągu danego znaku, a następnie wyodrębnij wszystkie znaki z jego prawej strony.
Przyjrzyjmy się tradycyjnym sposobom wyboru: formuł, makr i Power Query.
Metoda 1. Formuły
Aby ułatwić zrozumienie istoty i mechaniki formuły, zacznijmy trochę z daleka. Najpierw zwiększmy liczbę spacji między słowami w naszym tekście źródłowym do np. 20 sztuk. Możesz to zrobić za pomocą funkcji replace. ZASTĄPIĆ (ZASTĄPIĆ) oraz funkcję powtarzania danego znaku N-krotnie – REPEAT (POWT):
Teraz odcinamy 20 znaków od końca wynikowego tekstu za pomocą funkcji PRAWO (DOBRZE):
Robi się cieplej, prawda? Pozostaje usunąć dodatkowe spacje za pomocą funkcji TRIM (PRZYCINAĆ) a problem zostanie rozwiązany:
W wersji angielskiej nasza formuła będzie wyglądać tak:
=PRZYC(PRAWO(ZAMIANA(A1;»«;POWT(»«;20));20))
Mam nadzieję, że jasne jest, że w zasadzie nie trzeba wstawiać dokładnie 20 spacji – wystarczy każda liczba, o ile jest większa niż długość najdłuższego słowa w tekście źródłowym.
A jeśli tekst źródłowy trzeba podzielić nie spacją, a kolejnym separatorem (np. przecinkiem), to nasza formuła będzie musiała zostać lekko poprawiona:
Metoda 2. Funkcja makro
Zadanie wydobycia ostatniego słowa lub fragmentu z tekstu można również rozwiązać za pomocą makr, a mianowicie pisania funkcji wyszukiwania wstecznego w Visual Basic, która zrobi to, czego potrzebujemy – wyszuka dany podciąg w ciągu w przeciwnym kierunku – z od końca do początku.
Naciśnij skrót klawiaturowy inny+F11 lub przycisk Visual Basic Zakładka wywoływacz (Deweloper)aby otworzyć edytor makr. Następnie dodaj nowy moduł za pomocą menu Wstaw – moduł i skopiuj tam następujący kod:
Funkcja LastWord(txt As String, opcjonalny delim As String = " ", opcjonalny n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) End Function
Teraz możesz zapisać skoroszyt (w formacie z obsługą makr!) i użyć utworzonej funkcji w następującej składni:
=Ostatniesłowo(txt ; odstęp ; n)
gdzie
- txt – komórka z tekstem źródłowym
- rozgraniczać — znak separatora (domyślnie — spacja)
- n – jakie słowo należy wydobyć z końca (domyślnie – pierwsze z końca)
Przy jakichkolwiek zmianach w tekście źródłowym w przyszłości nasza funkcja makr zostanie przeliczona w locie, jak każda standardowa funkcja arkusza Excela.
Metoda 3. Power Query
Zapytanie o moc to darmowy dodatek firmy Microsoft do importowania danych do programu Excel z niemal dowolnego źródła, a następnie przekształcania pobranych danych do dowolnej postaci. Moc i chłód tego dodatku są tak wspaniałe, że Microsoft domyślnie wbudował wszystkie jego funkcje w Excel 2016. W przypadku programu Excel 2010-2013 dodatek Power Query można pobrać bezpłatnie stąd.
Nasze zadanie oddzielenia ostatniego słowa lub fragmentu przez dany separator za pomocą dodatku Power Query rozwiązuje się bardzo łatwo.
Najpierw zamieńmy naszą tabelę danych w inteligentną tabelę za pomocą skrótów klawiaturowych. Ctrl+T lub komendy Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę):
Następnie ładujemy utworzoną „inteligentną tabelę” do Power Query za pomocą polecenia Ze stołu/zakresu (Z tabeli/zakresu) Zakładka Dane (jeśli masz Excel 2016) lub na karcie Zapytanie o moc (jeśli masz Excel 2010-2013):
W otwartym oknie edytora zapytań, na zakładce Transformacja (Przekształcać) wybierz drużynę Podziel kolumnę — według ogranicznika (Podziel kolumnę — według ogranicznika) a następnie pozostaje ustawić znak separatora i wybrać opcję Ogranicznik z prawej stronywyciąć nie wszystkie słowa, ale tylko ostatnie:
Po kliknięciu OK ostatnie słowo zostanie oddzielone do nowej kolumny. Zbędną pierwszą kolumnę można usunąć, klikając prawym przyciskiem myszy jej nagłówek i wybierając Usunąć (Usuwać). Możesz również zmienić nazwę pozostałej kolumny w nagłówku tabeli.
Wyniki można przesłać z powrotem do arkusza za pomocą polecenia Strona główna — Zamknij i załaduj — Zamknij i załaduj do… (Strona główna — Zamknij i załaduj — Zamknij i załaduj do…):
W rezultacie otrzymujemy:
Tak – tanio i wesoło, bez formułek i makr, prawie bez dotykania klawiatury 🙂
Jeśli oryginalna lista zmieni się w przyszłości, wystarczy kliknąć prawym przyciskiem myszy lub użyć skrótu klawiaturowego Ctrl+inny+F5 zaktualizuj naszą prośbę.
- Dzielenie przyklejonego tekstu na kolumny
- Parsowanie i parsowanie tekstu za pomocą wyrażeń regularnych
- Wyodrębnianie pierwszych słów z tekstu za pomocą funkcji SUBSTITUTE