Podczas pracy z danymi tekstowymi w programie Excel może być konieczne usunięcie tekstu przed lub po określonym znaku lub ciągu tekstowym.
Na przykład, jeśli masz nazwiska i dane osobowe osób, możesz chcieć usunąć oznaczenie po przecinku i zachować tylko nazwisko (lub odwrotnie, jeśli zachowujesz oznaczenie i usuwasz nazwisko).
Czasami można to zrobić za pomocą prostej formuły lub szybkiego Znajdź i zamień, a czasami wymaga to bardziej złożonych formuł lub obejścia.
W tym samouczku pokażę, jak usunąć tekst przed lub po określonym znaku w programie Excel (przy użyciu różnych przykładów).
Zacznijmy więc od kilku prostych przykładów.
Usuń tekst po znaku za pomocą funkcji Znajdź i zamień
Jeśli chcesz szybko usunąć cały tekst po określonym ciągu tekstowym (lub przed ciągiem tekstowym), możesz to zrobić za pomocą funkcji Znajdź i zamień oraz znaków wieloznacznych.
Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz usunąć oznaczenie po przecinku i zachować tekst przed przecinkiem.
Poniżej znajdują się kroki, aby to zrobić:
- Skopiuj i wklej dane z kolumny A do kolumny B (jest to również zachowanie oryginalnych danych)
- Po zaznaczeniu komórek w kolumnie B kliknij kartę Strona główna
- W grupie Edytowanie kliknij opcję Znajdź i wybierz
- W opcjach, które pojawią się w menu rozwijanym, kliknij opcję Zamień. Otworzy się okno dialogowe Znajdź i zamień
- W polu „Znajdź co” wpisz ,* (tj. przecinek, po którym następuje znak gwiazdki)
- Pozostaw pole „Zamień na” puste
- Kliknij przycisk Zamień wszystko
Powyższe kroki pozwolą znaleźć przecinek w zestawie danych i usunąć cały tekst po przecinku (w tym przecinek).
Ponieważ powoduje to zastąpienie tekstu z wybranych komórek, dobrym pomysłem jest skopiowanie tekstu do innej kolumny, a następnie wykonanie tej operacji wyszukiwania i zamiany lub utworzenie kopii zapasowej danych, aby oryginalne dane były nienaruszone
Jak to działa?
* (znak gwiazdki) to symbol wieloznaczny, który może reprezentować dowolną liczbę znaków.
Kiedy używam go po przecinku (w polu „Znajdź co”), a następnie klikam przycisk Zamień wszystko, znajduje pierwszy przecinek w komórce i uznaje go za dopasowanie.
Dzieje się tak, ponieważ znak gwiazdki (*) jest uważany za dopasowanie do całego ciągu tekstowego następującego po przecinku.
Więc kiedy naciśniesz przycisk zamień wszystko, zastępuje on przecinek i cały następujący po nim tekst.
Notatka: Ta metoda działa dobrze, wtedy masz tylko jeden przecinek w każdej komórce (jak w naszym przykładzie). Jeśli masz wiele przecinków, ta metoda zawsze znajdzie pierwszy przecinek, a następnie usunie wszystko po nim. Nie możesz więc użyć tej metody, jeśli chcesz zastąpić cały tekst po drugim przecinku i zachować pierwszy bez zmian.Jeśli chcesz usunąć wszystkie znaki przed przecinkiem, zmień wyszukiwanie w polu, umieszczając znak gwiazdki przed przecinkiem (*, zamiast ,*)
Usuń tekst za pomocą formuł
Jeśli potrzebujesz większej kontroli nad znajdowaniem i zamienianiem tekstu przed określonym znakiem lub po nim, lepiej użyć wbudowanych formuł tekstowych w programie Excel.
Załóżmy, że masz poniższy zestaw danych, w którym chcesz usunąć cały tekst po przecinku.
Poniżej znajduje się formuła, aby to zrobić:
=LEWO(A2,ZNAJDŹ(",";A2)-1)
Powyższa formuła wykorzystuje funkcję ZNAJDŹ, aby znaleźć pozycję przecinka w komórce.
Ten numer pozycji jest następnie używany przez funkcję LEWO do wyodrębnienia wszystkich znaków przed przecinkiem. Ponieważ nie chcę, aby przecinek był częścią wyniku, odjąłem 1 od wartości wynikowej formuły Znajdź.
To był prosty scenariusz.
Weźmy nieco złożoną kwestię.
Załóżmy, że mam poniżej zestaw danych, w którym chcę usunąć cały tekst po drugim przecinku.
Oto formuła, która to zrobi:
=LEWO(A2,ZNAJDŹ("!",ZAMIENNIK(A2",","!",2))-1)
Ponieważ ten zestaw danych ma wiele przecinków, nie mogę użyć funkcji ZNAJDŹ, aby uzyskać pozycję pierwszego przecinka i wyodrębnić wszystko po lewej stronie.
Muszę jakoś znaleźć położenie drugiego przecinka, a potem wydobyć wszystko, co jest na lewo od drugiego przecinka.
Aby to zrobić, użyłem funkcji SUBSTITUTE, aby zmienić drugi przecinek na wykrzyknik. Teraz to daje mi wyjątkowy charakter w celi. Mogę teraz użyć pozycji wykrzyknika, aby wyodrębnić wszystko na lewo od drugiego przecinka.
Ta pozycja wykrzyknika jest używana w funkcji LEFT, aby wyodrębnić wszystko przed drugim przecinkiem.
Na razie w porządku!
Ale co, jeśli w zestawie danych jest niespójna liczba przecinków.
Na przykład w poniższym zestawie danych niektóre komórki mają dwa przecinki, a niektóre komórki mają trzy przecinki i muszę wyodrębnić cały tekst przed ostatnim przecinkiem.
W takim przypadku muszę jakoś ustalić pozycję ostatniego wystąpienia przecinka, a następnie wyodrębnić wszystko po jego lewej stronie.
Poniżej znajduje się formuła, która to zrobi
=LEWY(A2,ZNAJDŹ("!",ZAMIANA(A2,",","!",DŁ(A2)-DŁ(ZAMIANA(A2,",",""))))-1)
Powyższa formuła wykorzystuje funkcję LEN, aby znaleźć całkowitą długość tekstu w komórce, a także długość tekstu bez przecinka.
Gdy odejmę te dwie wartości, otrzymam całkowitą liczbę przecinków w komórce.
Więc dałoby mi 3 dla komórki A2 i 2 dla komórki A4.
Ta wartość jest następnie używana w formule SUBSTITUTE w celu zastąpienia ostatniego przecinka wykrzyknikiem. A następnie możesz użyć lewej funkcji wyodrębnij wszystko, co znajduje się na lewo od wykrzyknika (tam był ostatni przecinek)
Jak widać na przykładach, użycie kombinacji formuł tekstowych pozwala poradzić sobie z wieloma różnymi sytuacjami.
Ponadto, ponieważ wynik jest połączony z oryginalnymi danymi, po zmianie oryginalnych danych wynik zostanie automatycznie zaktualizowany.
Usuń tekst za pomocą wypełniania Flash
Flash Fill to narzędzie, które zostało wprowadzone w programie Excel 2013 i jest dostępne we wszystkich późniejszych wersjach.
Działa poprzez identyfikowanie wzorców, gdy ręcznie wprowadzasz dane, a następnie ekstrapoluje, aby uzyskać dane dla całej kolumny.
Więc jeśli chcesz usunąć tekst przed lub po określonym znaku, wystarczy pokazać wróżce flash, jak będzie wyglądał wynik (wprowadzając go ręcznie kilka razy), a wypełnianie flash automatycznie zrozumie wzór i da ci wyniki.
Pokażę to na przykładzie.
Poniżej mam zestaw danych, w którym chcę usunąć cały tekst po przecinku.
Oto kroki, aby to zrobić za pomocą funkcji Flash Fill:
- W komórce B2, która jest sąsiednią kolumną naszych danych, ręcznie wpisz „Jeffery Haggins” (co jest oczekiwanym wynikiem)
- W komórce B3 wpisz „Tim Scott” (co jest oczekiwanym wynikiem dla drugiej komórki)
- Wybierz zakres B2:B10
- Kliknij kartę Strona główna
- W grupie Edycja kliknij opcję rozwijaną Wypełnij
- Kliknij Wypełnienie Flash
Powyższe kroki dadzą wynik, jak pokazano poniżej:
Możesz także użyć skrótu klawiszowego Flash Fill Kontrola + E po zaznaczeniu komórek w kolumnie wynikowej (w naszym przykładzie kolumna B)
Flash Fill to wspaniałe narzędzie, które w większości przypadków jest w stanie rozpoznać wzór i uzyskać odpowiedni efekt. ale w niektórych przypadkach może nie być w stanie prawidłowo rozpoznać wzoru i może dać błędne wyniki.
Upewnij się więc, że dokładnie sprawdziłeś wyniki Flash Fill
I tak jak usunęliśmy cały tekst po określonym znaku za pomocą wypełniania flash, możesz wykonać te same czynności, aby usunąć tekst przed określonym znakiem. po prostu pokaż flash fill jak wynik powinien wyglądać jak mój Internet ręcznie w sąsiedniej kolumnie, a zrobi resztę.
Usuń tekst za pomocą VBA (funkcja niestandardowa)
Cała koncepcja usuwania tekstu przed lub po określonym znaku jest uzależniona od znalezienia pozycji tego znaku.
Jak widać powyżej, znalezienie dobrego ostatniego wystąpienia tej postaci oznacza użycie mikstury formuł.
Jeśli jest to coś, co musisz robić dość często, możesz uprościć ten proces, tworząc niestandardową funkcję za pomocą VBA (nazywaną funkcjami zdefiniowanymi przez użytkownika).
Po utworzeniu możesz ponownie używać tej funkcji. Jest również o wiele prostszy i łatwiejszy w użyciu (ponieważ większość ciężkiego podnoszenia jest wykonywana przez kod VBA na zapleczu).
Poniżej kod VBA, którego możesz użyć do utworzenia funkcji niestandardowej w programie Excel:
Function LastPosition(rCell As Range, rChar As String) 'Ta funkcja podaje ostatnią pozycję określonego znaku 'Ten kod został opracowany przez Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len(rCell) For i = rLen To 1 Step -1 If Mid(rCell, i - 1, 1) = rChar Then LastPosition = i - 1 Wyjście z funkcji End If Next i End Function
Musisz umieścić kod VBA w zwykłym module w edytorze VB lub w skoroszycie makr osobistych. Gdy już go masz, możesz go używać jako dowolnej innej zwykłej funkcji arkusza roboczego w skoroszycie.
Ta funkcja przyjmuje 2 argumenty:
- Odwołanie do komórki, dla którego chcesz znaleźć ostatnie wystąpienie określonego znaku lub ciągu tekstowego
- Znak lub ciąg tekstowy, którego pozycję musisz znaleźć
Załóżmy, że masz teraz poniższy zestaw danych i chcesz usunąć cały tekst po ostatnim przecinku i mieć tylko tekst przed ostatnim przecinkiem.
Poniżej znajduje się formuła, która to zrobi:
=LEWO(A2Ostatnia pozycja(A2,",")-1)
W powyższym wzorze określiłem położenie ostatniego przecinka. Jeśli chcesz znaleźć pozycję jakiegoś innego znaku lub ciągu tekstowego, powinieneś użyć tego jako drugiego argumentu w funkcji.
Jak widać, jest to o wiele krótsze i łatwiejsze w użyciu w porównaniu do długiej formuły tekstowej, której użyliśmy w poprzedniej sekcji
Jeśli umieścisz kod VBA w module w skoroszycie, będziesz mógł używać tej funkcji niestandardowej tylko w tym konkretnym skoroszycie. Jeśli chcesz użyć tego we wszystkich skoroszytach w systemie, musisz skopiować i wkleić ten kod w skoroszycie makr osobistych.
Oto niektóre z wiadomości e-mail, których możesz użyć do szybkiego usunięcia tekstu przed lub po określonym znaku w programie Excel.
Jeśli jest to prosta jednorazowa rzecz, możesz to zrobić za pomocą funkcji Znajdź i zamień. co, jeśli jest trochę bardziej złożony, musisz użyć kombinacji wbudowanych formuł Excela, a nawet stworzyć własną niestandardową formułę za pomocą VBA.
Mam nadzieję, że ten samouczek okazał się przydatny.
