Wiemy, że funkcje TRIM i CLEAN Excel służą do czyszczenia niedrukowalnych znaków i dodatkowych spacji z ciągów, ale nie pomagają one zbytnio w identyfikowaniu ciągów zawierających znaki specjalne, takie jak @ lub ! itp. W takich przypadkach korzystamy z UDF.
Jeśli więc chcesz wiedzieć, czy ciąg zawiera znaki specjalne, nie znajdziesz żadnej formuły ani funkcji programu Excel, aby to zrobić. Możemy znaleźć znaki specjalne, które są na klawiaturze, wpisując ręcznie, ale nie możesz wiedzieć, czy w ciągu znajdują się symbole, czy nie.
Znalezienie znaków specjalnych może być bardzo ważne dla celów czyszczenia danych. A w niektórych przypadkach trzeba to zrobić. Więc jak to robimy w Excelu? Skąd możemy wiedzieć, czy ciąg zawiera znaki specjalne? Cóż, możemy do tego użyć UDF.
Poniższa formuła zwróci TRUE, jeśli jakakolwiek komórka zawiera znaki inne niż 1 do 0 i A do Z (w obu przypadkach). Jeśli nie znajdzie żadnych znaków specjalnych, zwróci FALSE.
Formuła ogólna
| = Zawiera znaki specjalne (ciąg) |
Strunowy: Ciąg, który chcesz sprawdzić pod kątem znaków specjalnych.
Aby ta formuła działała, musisz umieścić poniższy kod w module swojego skoroszytu.
Więc otwórz Excela. Naciśnij ALT + F11, aby otworzyć VBE. Wstaw moduł z menu Wstaw. Skopiuj poniższy kod i wklej go do modułu.
Funkcja zawieraSpecialCharacters(str As String) As Boolean For I = 1 To Len(str) ch = Mid(str, I, 1) Wybierz Case ch Case "0" To "9", "A" To "Z", "a " To "z", " " ZawieraSpecialCharacters = False Case Else ZawieraSpecialCharacters = True Wyjdź na koniec Wybierz następną funkcję końcową
Teraz funkcja jest gotowa do użycia.
Przejdź do arkusza w skoroszycie, który zawiera ciągi, które chcesz sprawdzić.
Napisz poniższą formułę w komórce C2:
| = Zawiera znaki specjalne (B13) |
Zwraca TRUE dla pierwszego ciągu, ponieważ zawiera znak specjalny. Kiedy skopiujesz formułę w dół, pokaże się FALSE dla ciągu B14 i tak dalej.
Ale o dziwo pokazuje TRUE dla ostatniego ciągu „Exceltip.com”. To dlatego, że zawiera kropkę (.). Ale dlaczego tak? Zbadajmy kod, aby zrozumieć.
Jak działa funkcja?
Iterujemy przez wszystkie znaki ciągu za pomocą pętli For i funkcji mid języka VBA. Funkcja Mid wyodrębnia jeden znak na raz z łańcucha i zapisuje go w zmiennej ch.
Dla I = 1 To Len(str) ch = Mid(str, I, 1)
Teraz nadchodzi główna część. Używamy instrukcji select case, aby sprawdzić, co zawiera zmienna ch.
Mówimy VBA, aby sprawdził, czy ch zawiera jakąkolwiek ze zdefiniowanych wartości. Zdefiniowałem 0 do 9, A do Z, a do z i " " (spacja). Jeśli c
h zawiera którykolwiek z nich, ustawiamy jego wartość na False.
Wybierz wielkość liter Wielkość "0" Do "9", "A" Do "Z", "a" Do "z", " " Zawieraznaki specjalne = False
Widać, że nie mamy kropki (.) na liście i dlatego otrzymujemy TRUE dla ciągu zawierającego kropkę. Do listy można dodać dowolny znak, który zostanie zwolniony z formuły.
Jeśli ch zawiera jakikolwiek znak inny niż wymienione na liście, ustawiamy wynik funkcji na True i kończymy pętlę właśnie tam.
Case Else zawiera specjalne znaki = True Exit For
Więc tak to działa. Ale jeśli chcesz wyczyścić znaki specjalne, będziesz musiał wprowadzić pewne zmiany w funkcji.
Jak wyczyścić znaki specjalne z ciągów w programie Excel?
Aby wyczyścić znaki specjalne z ciągu znaków w Excelu, stworzyłem kolejną niestandardową funkcję w VBA. Składnia funkcji to:
| =Czysteznakispecjalne(ciąg) |
Ta funkcja zwraca oczyszczoną wersję przekazanego do niej ciągu. Nowy ciąg nie będzie zawierał żadnych znaków specjalnych.
Kod tej funkcji wygląda tak:
Funkcja CleanSpecialCharacters(str As String) Dim nstr As String nstr = str For I = 1 To Len(str) ch = Mid(str, I, 1) Wybierz Case ch Case "0" To "9", "A" To " Z", "a" To "z", " " 'Nie rób nic. Case Else nstr = Replace(nstr, ch, "") End Select Next CleanSpecialCharacters = nstr End Function
Skopiuj to w tym samym module, który skopiowałeś w powyższym kodzie.
Gdy użyjesz tej formuły na ciągach, wyniki będą wyglądać tak:
Możesz zobaczyć, że każdy znak specjalny jest usuwany z ciągu, w tym kropki.
Jak to działa?
Działa tak samo jak powyższa funkcja. Jedyna różnica polega na tym, że ta funkcja zastępuje każdy znak specjalny znakiem null. Tworzy nowy ciąg i zwraca ten ciąg.
Wybierz wielkość liter ch wielkość liter "0" na "9", "A" na "Z", "a" na "z", " " 'ContainsSpecialCharacters = False Case Else nstr = Replace(nstr, ch, "") End Select
Jeśli chcesz wyłączyć dowolny znak z czyszczenia, możesz dodać go do listy w kodzie. Excel wybaczy ten szczególny charakter.
Tak, tak, w ten sposób można znaleźć i zamienić znaki specjalne z ciągu w programie Excel. Mam nadzieję, że było to wystarczająco objaśniające i pomocne. Jeśli to nie pomoże w rozwiązaniu problemu, daj nam znać w sekcji komentarzy poniżej.
Jak korzystać z funkcji TRIM w programie Excel: Funkcja TRIM służy do przycinania ciągów i czyszczenia wszelkich końcowych lub wiodących spacji z ciągu. To bardzo pomaga nam w procesie czyszczenia danych.
Jak korzystać z funkcji CLEAN w programie Excel: Funkcja Clean służy do czyszczenia niedrukowalnych znaków z ciągu. Ta funkcja jest używana głównie z funkcją TRIM do czyszczenia importowanych danych obcych.
Zamień tekst na końcu ciągu, zaczynając od pozycji zmiennej: Aby zastąpić tekst z końca ciągu, używamy funkcji REPLACE. Funkcja REPLACE używa pozycji tekstu w ciągu, aby go zastąpić.
Jak sprawdzić, czy ciąg zawiera jeden z wielu tekstów w programie Excel?: Aby sprawdzić, czy ciąg zawiera którykolwiek z wielu tekstów, używamy tej formuły. Używamy funkcji SUMA, aby zsumować wszystkie dopasowania, a następnie wykonujemy logikę, aby sprawdzić, czy ciąg zawiera którykolwiek z wielu ciągów.
Policz komórki zawierające określony tekst: Prosta funkcja LICZ.JEŻELI zdziała magię. Aby policzyć liczbę wielu komórek, które zawierają dany ciąg, używamy operatora wieloznacznego z funkcją LICZ.JEŻELI.
Excel REPLACE vs funkcja SUBSTITUTE: Funkcje REPLACE i SUBSTITUTE są najbardziej niezrozumianymi funkcjami. Aby znaleźć i zamienić dany tekst, używamy funkcji SUBSTITUTE. Gdzie REPLACE służy do zastąpienia liczby znaków w ciągu…
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO programu Excel| Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z Excela LICZ.JEŻELI Funkcja| Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.