Kiedy otrzymujesz surowe dane Excela, czasami okazuje się, że tekst, liczby i znaki niealfanumeryczne są wymieszane i musisz usunąć znaki niealfanumeryczne, zanim będziesz mógł pracować z danymi.
Jednym ze sposobów usunięcia znaków niealfanumerycznych byłoby przejście przez zbiór danych komórka po komórce i usunięcie ich, ale ta metoda nie jest praktyczna, zwłaszcza w przypadku dużych zbiorów danych.
Niniejszy samouczek przedstawia kilka metod, których można użyć do łatwego usunięcia niechcianych znaków niealfanumerycznych ze zbioru danych.
Dwie metody usuwania znaków niealfanumerycznych w Excelu
Poniższe dwie metody to niektóre z podejść, których można użyć do usunięcia znaków niealfanumerycznych w programie Excel:
- Użyj funkcji SUBSTITUTE programu Excel.
- Użyj funkcji zdefiniowanej przez użytkownika programu Excel.
METODA 1 – Użyj funkcji SUBSTITUTE programu Excel
Jeśli zestaw danych zawiera tylko jeden typ znaków niealfanumerycznych, można użyć funkcji SUBSTITUTE, aby zastąpić je pustymi ciągami znaków.
Oto przykład zbioru danych z jednym typem znaku niealfanumerycznego – gwiazdką (*):
Użyjemy funkcji SUBSTITUTE, aby zastąpić gwiazdki (*) pustymi ciągami znaków:
Krok 1- Wprowadź formułę =SUBSTITUTE(A2, ” * „,” „) w komórce B2:
Krok 2 – Naciśnij klawisz Enter :
Krok 3 – Użyj uchwytu wypełniania w komórce B2, aby przeciągnąć formułę w dół i skopiować ją do pozostałych pustych komórek w zestawie danych:
Wszystkie gwiazdki (*) zostały zastąpione pustymi ciągami znaków.
Zagnieżdżone funkcje SUBSTITUTE
Funkcja SUBSTITUTE nie może zastąpić więcej niż jednego znaku niealfanumerycznego naraz.
Jeśli zbiór danych zawiera co najmniej dwa typy znaków niealfanumerycznych, można użyć co najmniej dwóch zagnieżdżonych funkcji SUBSTITUTE, aby je usunąć.
Na przykład, jeśli chcesz usunąć 2 typy znaków niealfanumerycznych, użyj 2 zagnieżdżonych funkcji SUBSTITUTE , a jeśli chcesz usunąć 3 typy, użyj 3 zagnieżdżonych funkcji SUBSTITUTE .
Poniższy zestaw danych zawiera dwa typy znaków niealfanumerycznych: gwiazdkę (*) i znak funta (#):
W poniższych krokach użyjemy 2 zagnieżdżonych funkcji SUBSTITUTE , aby usunąć znaki niealfanumeryczne:
Krok 1 – Wprowadź formułę =SUBSTITUTE(SUBSTITUTE(A2, ” * „,” „), ” # „,” „) w komórce B2:
Krok 2 – Naciśnij klawisz Enter i przeciągnij uchwyt wypełniania w dół, aby wypełnić puste komórki formułą:
Znaki niealfanumeryczne zostały zastąpione pustymi ciągami znaków.
Możesz dodawać coraz więcej zagnieżdżonych funkcji SUBSTITUTE do formuły, w zależności od tego, ile znaków niealfanumerycznych chcesz usunąć. Jednak im więcej funkcji SUBSTITUTE zostanie dodanych, tym cięższa stanie się formuła.
Bardziej praktycznym sposobem na usunięcie wszystkich znaków niealfanumerycznych jednocześnie jest użycie funkcji zdefiniowanych przez użytkownika.
Zobacz także: Korzystanie z funkcji VLOOKUP programu Excel do zwracania kilku wartości w pionie
METODA 2 – Użycie funkcji zdefiniowanej przez użytkownika
Aby usunąć wszystkie znaki niealfanumeryczne ze zbioru danych, należy napisać makro Excel VBA, wykonując następujące kroki:
Krok 1- Na karcie Deweloper kliknij polecenie Visual Basic lub naciśnij Alt + F8:
Pojawi się oknoVisual Basic Editor (VBE).
Krok 2 – Kliknij Wstaw>>Moduł, aby utworzyć nowy moduł:
Zostanie utworzony nowy moduł:
Krok 3 – W nowym module wprowadź następujący kod i kliknij przycisk Zapisz :
Function RemoveNonAlphaN(str As String) As String Dim ch, bytes() As Byte: bytes = str For Each ch in bytes If Chr(ch) Like "[A-Z.a-z 0-9] " _ Then RemoveNonAlphaN = RemoveNonAlphaN & Chr(ch) Next ch End Function
Krok 4 – Kliknij przycisk Wyświetl Microsoft Excel lub naciśnij skrót klawiaturowy Alt + F11, aby powrócić do bieżącego arkusza. Wprowadź formułę =RemoveNonAlphaN(A2) w komórce B2 zestawu danych, którego używamy jako przykładu:
Krok 5 – Wprowadź i przeciągnij w dół za pomocą uchwytu wypełniania:
Wszystkie znaki niealfanumeryczne zostaną usunięte.
Podsumowanie
W tym poradniku przedstawiliśmy dwie metody usuwania znaków niealfanumerycznych ze zbioru danych. Możesz użyć dowolnej metody, która najbardziej Ci odpowiada.