Jak wyodrębnić fragment tekstu w programie Excel (za pomocą formuł tekstowych)

Excel ma zestaw funkcji tekstowych, które mogą zdziałać cuda. Za pomocą tych funkcji można wykonywać wszelkiego rodzaju operacje wycinania tekstu i kości.

jednym z typowych zadań dla osób pracujących z danymi tekstowymi jest wyodrębnienie fragmentu tekstu w programie Excel (tzn. pobranie psrt tekstu z komórki).

Niestety, w Excelu nie ma funkcji podłańcucha, która mogłaby to łatwo zrobić. Jednak nadal można to zrobić za pomocą formuł tekstowych, a także innych wbudowanych funkcji programu Excel.,

Ten samouczek obejmuje:

najpierw przyjrzyjmy się niektórym funkcjom tekstowym, których będziemy używać w tym samouczku.

funkcje tekstowe Excela

Excel ma szereg funkcji tekstowych, które ułatwią wyodrębnienie fragmentu tekstu z oryginalnego tekstu w programie Excel. Oto Funkcje tekstowe programu Excel, które będziemy używać w tym samouczku:

RIGHT funkcja: wyodrębnia określoną liczbę znaków z prawej strony ciągu tekstowego.
LEFT funkcja: wyodrębnia określoną liczbę znaków z lewej strony łańcucha tekstowego.,
funkcja MID: wyodrębnia określoną liczbę znaków z określonej pozycji początkowej w łańcuchu tekstowym.
funkcja FIND: znajduje początkową pozycję określonego tekstu w ciągu tekstowym.
funkcja LEN: zwraca ilość znaków w ciągu tekstowym.,

Wyodrębnij Podłańcuch w programie Excel za pomocą funkcji

Załóżmy, że masz zestaw danych, jak pokazano poniżej:

są to niektóre losowe (ale superbohaterskie) identyfikatory e-mail (z wyjątkiem moich), a w poniższych przykładach pokażę Ci, 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 zidentyfikowanie wzorca (jeśli istnieje). To sprawia, że bardzo łatwo jest skonstruować formułę., W powyższym przypadku wzorzec jest znakiem @ pomiędzy nazwą użytkownika a nazwą domeny i użyjemy go jako odniesienia do uzyskania nazw użytkowników.

tutaj jest formuła, aby uzyskać nazwę użytkownika:

=LEFT(A2,FIND("@",A2)-1)

powyższa formuła używa funkcji LEFT, aby wyodrębnić nazwę użytkownika, identyfikując pozycję znaku @ w id. Odbywa się to za pomocą funkcji FIND, która zwraca pozycję @.

na przykład w przypadku [email protected], FIND („@ ” , A2) zwróci 11, która jest jego pozycją w ciągu tekstowym.,

teraz używamy funkcji LEFT, aby wyodrębnić 10 znaków z lewej strony łańcucha (o jeden mniej niż wartość zwracana przez funkcję LEFT).

przykład 2 – wyodrębnienie 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. Drobna różnica polega na tym, że musimy wyodrębnić znaki z prawej strony ciągu tekstowego.,

oto formuła, która to zrobi:

=RIGHT(A2,LEN(A2)-FIND("@",A2))

w powyższej formule używamy tej samej logiki, ale dostosuj ją, aby upewnić się, że otrzymujemy prawidłowy ciąg znaków.

weźmy jeszcze raz przykład z [email protected]. funkcja FIND Zwraca pozycję znaku@, która w tym przypadku wynosi 11. Teraz musimy wyodrębnić wszystkie znaki po znaku @. Określamy więc całkowitą długość łańcucha i odejmujemy liczbę znaków aż do znaku @. Daje nam liczbę znaków, które obejmują nazwę domeny po prawej stronie.,

teraz możemy po prostu użyć odpowiedniej funkcji, aby uzyskać nazwę domeny.

przykład 3 – wyodrębnienie nazwy domeny z identyfikatorów e-mail (bez .com)

aby wyodrębnić podłańcuch ze środka łańcucha tekstowego, należy określić pozycję znacznika tuż przed i za podłańcuchem.

na przykład w poniższym przykładzie, aby uzyskać nazwę domeny bez części .com, znacznikiem będzie @ (który jest tuż przed nazwą domeny) i . (czyli zaraz po nim).,

oto formuła, która wyodrębni tylko nazwę domeny:

=MID(A2,FIND("@",A2)+1,FIND(".",A2)-FIND("@",A2)-1)

funkcja Excel MID wyodrębnia określoną liczbę znaków z określonej pozycji początkowej. W tym przykładzie powyżej, FIND („@ ” , A2)+1 określa pozycję początkową (która jest tuż za@) I FIND(„.”, A2) – FIND ( ” @ ” , A2) -1 określa liczbę znaków pomiędzy '@' i '.”

Update: jeden z czytelników William19 wspomniał, że powyższy wzór nie zadziała, gdyby była kropka(.) w id e-mail (na przykład bruce.wayne @ batman.,com). Oto wzór do radzenia sobie z takimi przypadkami:

=MID(A1,FIND("@",A1)+1,FIND(".",A1,FIND("@",A1))-FIND("@",A1)-1)

używanie tekstu do kolumn do wyodrębniania fragmentu w Excelu

używanie funkcji do wyodrębniania fragmentu w Excelu ma tę zaletę, że jest dynamiczne. Jeśli zmienisz oryginalny tekst, formuła automatycznie zaktualizuje wyniki.

Jeśli jest to coś, czego nie potrzebujesz, użycie funkcji Text to Columns może być szybkim i łatwym sposobem na podzielenie tekstu na podłańcuchy na podstawie określonych znaczników.

Oto Jak to zrobić:

Wybierz komórki, w których masz tekst.,
przejdź do danych –> Narzędzia danych –> tekst do kolumn.
w Kreatorze tekstu na kolumnę Krok 1 Wybierz rozdzielone i naciśnij Dalej.
w Kroku 2 zaznacz drugą opcję i wprowadź @ w polu po prawej stronie. Będzie to nasz ogranicznik, którego Excel użyje do podziału tekstu na podłańcuchy. Możesz zobaczyć podgląd danych poniżej. Kliknij Dalej.
w Kroku 3 Ustawienie ogólne działa dobrze w tym przypadku., Możesz jednak wybrać inny format, jeśli dzielisz liczby/daty. Domyślnie Komórka docelowa jest miejscem, w którym znajdują się oryginalne dane. Jeśli chcesz zachować oryginalne dane w Nienaruszonym Stanie, zmień to na inną komórkę.
kliknij na Zakończ.

to natychmiast da ci dwa zestawy podłańcuchów dla każdego identyfikatora e-mail używanego w tym przykładzie.

Jeśli chcesz dalej podzielić tekst (na przykład podzielić batman.com do Batmana i com), powtórz ten sam proces z nim.,

Korzystanie z funkcji znajdź i zastąp do wyodrębniania tekstu z komórki w programie Excel

znajdź i zastąp 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ń za pomocą znaków wieloznacznych, aby robić niesamowite rzeczy w programie Excel.

See Also: Learn All about Wildcard Characters in 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 wyodrębniania nazw użytkowników z identyfikatorów e-mail za pomocą funkcji Znajdź i zamień:

skopiuj i wklej oryginalne dane., Ponieważ Find and Replace działa i zmienia dane, na których jest stosowany, najlepiej jest mieć kopię zapasową oryginalnych danych.
wybierz Dane i przejdź do strony głównej –> Edycja –> Znajdź & Wybierz –> replace (lub użyj skrótu klawiaturowego Ctrl + H).,
w oknie dialogowym znajdź i zamień wpisz:
- Znajdź co: @*
- Zamień na: (pozostaw to puste)
kliknij Zamień wszystko.

spowoduje to natychmiastowe usunięcie całego tekstu przed znakiem @ w identyfikatorach e-mail. Uzyskasz wynik pokazany poniżej:

przykład 2 – wyodrębnianie nazwy domeny z identyfikatorów e-mail

używając tej samej logiki, możesz zmodyfikować kryteria „Znajdź co”, aby uzyskać nazwę domeny.,

oto kroki: