Excel ma zestaw funkcji TEKSTOWYCH, które potrafią zdziałać cuda. Za pomocą tych funkcji możesz wykonywać wszystkie rodzaje operacji na plasterkach tekstu i kostkach.
Jednym z typowych zadań osób pracujących z danymi tekstowymi jest wyodrębnienie podciągu w programie Excel (tj. Pobranie psrt tekstu z komórki).
Niestety, w Excelu nie ma funkcji podciągów, która mogłaby to łatwo zrobić. Jednak nadal można to zrobić za pomocą formuł tekstowych, a także niektórych innych wbudowanych funkcji programu Excel.
Przyjrzyjmy się najpierw niektórym funkcjom tekstowym, których będziemy używać w tym samouczku.
Funkcje TEKSTU Excel
Program Excel ma szereg funkcji tekstowych, które bardzo ułatwiłyby wyodrębnienie podciągu z oryginalnego tekstu w programie Excel. Oto funkcje programu Excel Text, których użyjemy w tym samouczku:
- Funkcja RIGHT: Wyodrębnia określoną liczbę znaków z prawej strony ciągu tekstowego.
- Funkcja LEFT: Wyodrębnia określoną liczbę znaków z lewej strony ciągu tekstowego.
- Funkcja MID: Wyodrębnia określoną liczbę znaków z określonej pozycji początkowej w ciągu tekstowym.
- ZNAJDŹ, funkcja: wyszukuje pozycję początkową określonego tekstu w ciągu tekstowym.
- Funkcja LEN: Zwraca liczbę znaków w ciągu tekstowym.
Wyodrębnij podciąg w Excelu za pomocą funkcji
Załóżmy, że masz zbiór danych, jak pokazano poniżej:
Są to losowe (ale superbohaterskie) identyfikatory e-mail (z wyjątkiem mojego), a w poniższych przykładach pokażę, jak wyodrębnić nazwę użytkownika i nazwę domeny za pomocą funkcji tekstowych w programie Excel.
Przykład 1 - wyodrębnianie nazw użytkowników z identyfikatorów e-mail
Podczas korzystania z funkcji tekstowych ważne jest, aby zidentyfikować wzorzec (jeśli istnieje). To sprawia, że bardzo łatwo jest skonstruować formułę. W powyższym przypadku wzorcem jest znak @ między nazwą użytkownika a nazwą domeny i użyjemy go jako odniesienia do uzyskania nazw użytkowników.
Oto wzór na uzyskanie nazwy użytkownika:
=LEWO(A2,ZNAJDŹ("@";A2)-1)
Powyższa formuła wykorzystuje funkcję LEWO do wyodrębnienia nazwy użytkownika poprzez określenie pozycji znaku @ w identyfikatorze. Odbywa się to za pomocą funkcji ZNAJDŹ, która zwraca pozycję @.
Na przykład w przypadku [email protected] ZNAJDŹ(„@”,A2) zwróci 11, co jest jego pozycją w ciągu tekstowym.
Teraz używamy funkcji LEWO, aby wyodrębnić 10 znaków z lewej strony ciągu (o jeden mniej niż wartość zwracana przez funkcję LEWO).
Przykład 2 - wyodrębnianie nazwy domeny z identyfikatorów e-mail
Ta sama logika użyta w powyższym przykładzie może być użyta do uzyskania nazwy domeny. Mała różnica polega na tym, że musimy wyodrębnić znaki z prawej strony ciągu tekstowego.
Oto formuła, która to zrobi:
=PRAWO(A2,DŁ(A2)-ZNAJDŹ("@",A2))
W powyższym wzorze używamy tej samej logiki, ale dostosuj ją, aby upewnić się, że otrzymujemy poprawny ciąg.
Ponownie weźmy przykład [email protected]. Funkcja ZNAJDŹ zwraca pozycję znaku @, która w tym przypadku wynosi 11. Teraz musimy wyodrębnić wszystkie znaki po @. Identyfikujemy więc całkowitą długość ciągu i odejmujemy liczbę znaków do @. Podaje nam liczbę znaków, które pokrywają nazwę domeny po prawej stronie.
Teraz możemy po prostu użyć funkcji PRAWO, aby uzyskać nazwę domeny.
Przykład 3 - wyodrębnianie nazwy domeny z identyfikatorów e-mail (bez domeny .com)
Aby wyodrębnić podciąg ze środka ciągu tekstowego, musisz określić położenie znacznika tuż przed i za podciągiem.
Na przykład w poniższym przykładzie, aby uzyskać nazwę domeny bez części .com, znacznikiem będzie @ (znajdujący się tuż przed nazwą domeny) i . (co jest zaraz po nim).
Oto formuła, która wyodrębni tylko nazwę domeny:
=ŚRODEK(A2,ZNAJDŹ("@",A2)+1;ZNAJDŹ(".",A2)-ZNAJDŹ("@",A2)-1)
Funkcja Excel MID wyodrębnia określoną liczbę znaków z określonej pozycji początkowej. W powyższym przykładzie ZNAJDŹ(„@”,A2)+1 określa pozycję początkową (która znajduje się zaraz po @), a ZNAJDŹ(„.”,A2)-ZNAJDŹ(„@”,A2)-1 identyfikuje liczba znaków między '@‘ i ‘.‘
Aktualizacja: Jeden z czytelników William19 wspomniał, że powyższa formuła nie zadziała, jeśli w identyfikatorze e-mail znajduje się kropka (.) (na przykład [email protected]). Oto wzór na radzenie sobie z takimi przypadkami:
=MID(A1,ZNAJDŹ("@",A1)+1,ZNAJDŹ(".",A1,ZNAJDŹ("@",A1))-ZNAJDŹ("@",A1)-1)
Używanie tekstu do kolumn do wyodrębniania podciągu w programie Excel
Używanie funkcji do wyodrębniania podciągu w programie Excel ma tę zaletę, że jest dynamiczne. Jeśli zmienisz oryginalny tekst, formuła automatycznie zaktualizuje wyniki.
Jeśli jest to coś, czego możesz nie potrzebować, użycie funkcji Tekst do kolumn może być szybkim i łatwym sposobem na podzielenie tekstu na podciągi na podstawie określonych znaczników.
Oto jak to zrobić:
- Zaznacz komórki, w których masz tekst.
- Przejdź do Dane -> Narzędzia danych -> Tekst do kolumn.
- W Kreatorze tekstu na kolumnę, krok 1, wybierz Rozdzielone i naciśnij Dalej.
- W kroku 2 zaznacz opcję Inne i wpisz @ w polu obok niej. To będzie nasz ogranicznik, którego Excel użyje do podzielenia tekstu na podciągi. Poniżej możesz zobaczyć podgląd danych. Kliknij Dalej.
- W kroku 3 ustawienie Ogólne działa w tym przypadku dobrze. Możesz jednak wybrać inny format, jeśli dzielisz liczby/daty. Domyślnie komórka docelowa to miejsce, w którym znajdują się oryginalne dane. Jeśli chcesz zachować oryginalne dane w nienaruszonym stanie, zmień to na inną komórkę.
- Kliknij Zakończ.
To natychmiast da ci dwa zestawy podciągów dla każdego identyfikatora e-mail użytego w tym przykładzie.
Jeśli chcesz dalej podzielić tekst (na przykład podziel batman.com na batman i com), powtórz z nim ten sam proces.
Używanie funkcji ZNAJDŹ i ZAMIEŃ do wyodrębniania tekstu z komórki w programie Excel
ZNAJDŹ i ZAMIEŃ może być potężną techniką podczas pracy z tekstem w programie Excel. W poniższych przykładach dowiesz się, jak używać funkcji ZNAJDŹ i ZAMIEŃ ze znakami wieloznacznymi, aby robić niesamowite rzeczy w programie Excel.
Zobacz też: Dowiedz się wszystkiego o znakach wieloznacznych w programie Excel.
Weźmy te same przykłady identyfikatorów e-mail.
Przykład 1 - wyodrębnianie nazw użytkowników z identyfikatorów e-mail
Oto kroki, aby wyodrębnić nazwy użytkowników z identyfikatorów e-mail za pomocą funkcji Znajdź i zamień:
- Skopiuj i wklej oryginalne dane. Ponieważ funkcja Znajdź i zamień działa i zmienia dane, na których jest stosowana, najlepiej jest mieć kopię zapasową oryginalnych danych.
- Wybierz dane i przejdź do Strona główna -> Edycja -> Znajdź i wybierz -> Zamień (lub użyj skrótu klawiaturowego Ctrl + H).
- W oknie dialogowym Znajdź i zamień wprowadź następujące informacje:
- Znajdź co: @*
- Zastąp: (pozostaw puste)
- Kliknij Zamień wszystko.
Spowoduje to natychmiastowe usunięcie całego tekstu przed @ w identyfikatorach e-mail. Otrzymasz wynik, jak pokazano poniżej:
Jak to działa? - W powyższym przykładzie użyliśmy kombinacji @ i *. Gwiazdka (*) to symbol wieloznaczny reprezentujący dowolną liczbę znaków. Stąd @* oznaczałoby ciąg tekstowy, który zaczyna się od @ i może mieć po nim dowolną liczbę znaków. Na przykład w [email protected] @* będzie @batman.com. Gdy zastąpimy @* pustym, usuwa wszystkie znaki po @ (w tym @).
Przykład 2 - wyodrębnianie nazwy domeny z identyfikatorów e-mail
Korzystając z tej samej logiki, możesz zmodyfikować kryteria „Znajdź co”, aby uzyskać nazwę domeny.
Oto kroki:
- Wybierz dane.
- Przejdź do Strona główna -> Edycja -> Znajdź i wybierz -> Zamień (lub użyj skrótu klawiaturowego Ctrl + H).
- W oknie dialogowym Znajdź i zamień wprowadź następujące informacje:
- Znajdź co: *@
- Zastąp: (pozostaw puste)
- Kliknij Zamień wszystko.
Spowoduje to natychmiastowe usunięcie całego tekstu przed @ w identyfikatorach e-mail. Otrzymasz wynik, jak pokazano poniżej:
