Funkcja REGEXTEST w Excelu

Wprowadzenie

Microsoft Excel od lat ewoluuje, oferując coraz bardziej zaawansowane funkcje, które wspierają użytkowników w codziennej pracy,jak i profesjonalistów pracujących z dużymi zbiorami danych. Jednym z najnowszych dodatków w arsenale Excela jest funkcja REGEXTEST. Dzięki niej możesz szybko i efektywnie sprawdzić, czy dany tekst pasuje do określonego wzorca wyrażeń regularnych (regex).

W tym artykule przyjrzymy się funkcji REGEXTEST w szczególności: jej zastosowaniom, składni, przykładom użycia i potencjalnym scenariuszom praktycznym. Dowiesz się, dlaczego ta funkcja jest tak ważna i jak może pomóc Ci zaoszczędzić czas w pracy z danymi tekstowymi.

Składnia funkcji REGEXTEST

Składnia funkcji REGEXTEST jest prosta i składa się z trzech argumentów:

REGEXTEST(tekst; wzorzec; [case_sensitivity])

Argumenty:

• tekst (wymagany) – Tekst lub odwołanie do komórki zawierającej tekst, który chcesz przetestować.
• wzorzec (wymagany) – Wyrażenie regularne opisujące wzorzec, który chcesz dopasować.
• case_sensitivity (opcjonalny) – Wartość logiczna (PRAWDA lub FAŁSZ), określająca, czy dopasowanie ma być wrażliwe na wielkość liter. Domyślna wartość to PRAWDA (wrażliwość na wielkość liter).

Wynik:

Funkcja REGEXTEST zwraca PRAWDA, jeśli tekst pasuje do wzorca, oraz FAŁSZ, jeśli nie ma dopasowania.

Przykłady wyrażeń regularnych

• [a-z] – dowolny znak z zakresu od a do z (tylko małe litery)
• [A-Z] – dowolny znak z zakresu od A do Z (tylko wielkie litery)
• [0-9] – dowolna cyfra ze zbioru od 0 do 9
• {3} – wartość podana w nawiasie klamrowym mówi ile razy ma wystąpić poprzednio podany zbiór dozwolonych wartości
• {3,} – poprzednio podany zbiór dozwolonych wartości może wystąpić co najmniej 3 razy.
• {3,5} – poprzednio podany zbiór dozwolonych wartości może wystąpić co najmniej 3 razy, ale nie więcej niż 5.
• () – nawiasy służą do grupowania części wyrażenia regularnego.
• ^ – oznacza początek ciągu znaków, czyli to czego szukamy musi być na samym początku
• $ – oznacza koniec ciągu znaków, czyli to czego szukamy musi być na samym końcu
• . – oznacza dowolny znak
• a* -zero lub więcej “a”
• a+ – jedno lub więcej “a”
• a? – jedno lub zero “a”
• \ – służy do wyszukania znaku specjalnego, np. \.

Przykład zastosowania funkcji REGEXTEST

Przykład 1: Sprawdzanie obecności cyfr w tekście

Załóżmy, że w kolumnie A jest lista nazw produktów, a chcemy sprawdzić, które z nich zawiera cyfry. Wyrażenie regularne [0-9] pozwala na wyszukanie dowolnych cyfr. W momencie znalezienia szukanego wzorca funkcja zwróci wartość PRAWDA, w przeciwnym razie zwracana jest wartość FAŁSZ.

Przykład 2: Sprawdzanie poprawności adresu mailowego w firmie.

Załóżmy, że każdy adres mailowy w firmie składa się z trzech pierwszych liter imienia połączonych z trzema pierwszymi literami nazwiska i z frazą “@firma.pl”. Tu również możemy wykorzystać funkcję z wyrażeniem regularnym [a-z]. Nie jest to natomiast w pełni prawidłowe rozwiązanie, ponieważ maile zapisane z wykorzystaniem wielkich liter nie zostaną zaakceptowane.

Naprawienie tego problemu możemy zrobić na dwa sposoby. Jednym z nich jest zapisane wyrażenia regularnego w postaci [a-zA-Z], co sprawie, że excel będzie brał pod uwagę wszystkie litery zarówno małe jak i wielkie od a do z.

Innym sposobem może być skorzystanie z argumentu opcjonalnego funkcji, który określa wrażliwość na wielkie litery. Ustawienie go na wartość 1 sprawi, że wielkość liter nie będzie brana pod uwagę.

Przykład 3: Problem polskich znaków

Jeżeli w poprzednim przykładzie mielibyśmy jakikolwiek adres mailowy z polskim znakiem, to niestety funkcja nie mogłaby zwrócić nam prawdy, przy użyciu wyrażenia [a-z]. Wyrażenie to niestety nie bierze pod uwagę polskich znaków. Aby obejść ten problem możemy rozbudować to wyrażenie o listę polskich znaków i będzie to wyglądało w następujący sposób [a-zęóąśłżźćń]. Zapis ten bierze pod uwagę wszystkie litery od a do z, ale dopuszcza także polskie znaki.

Przykład 4: Błędne zakończenie maila

Teoretycznie wcześniejsze rozwiązanie działa, jednakże jeśli mail zapisany w wierszu 4 poprawny niestety nie jest. Zamiast końcówki .pl mamy końcówkę .pll. Zapis funkcji z poprzedniego przykładu nie gwarantuje nam tego, że zakończeniem wpisu ma być “@firma.pl”. Taki zapis gwarantuje nam, że fraza “@firma.pl” ma wystąpić gdziekolwiek w przeszukiwanym tekście. Aby zagwarantować sobie poprawność rozwiązania warto wykorzystać symbol $, który gwarantuje nam, że zapis “@firma.pl” będzie końcem całego adresu mailowego.

Przykład 5: uwzględnienie zakończenia com i pl

Na liście maili w podanym przykładzie, zakończenie które występuje to w zdecydowanej większości .pl. ale jest też jeden adres mailowy zakończony .com. Aby uwzględnić też taki zapis w poprawności, należy skorzystać zapisu grupy alternatyw, czyli dopasowania jednego z dwóch lub więcej możliwych tekstów.

• | – jest operatorem logicznym, który wskazuje, że wyrażenie regularne może pasować do dowolnej opcji rozdzielonej tym znakiem
• ( ) – nawias grupuje elementy, które mają być traktowane jako całość. Dzięki temu wzorzec jest bardziej czytelny i elastyczny.

Przykład 6: sprawdzenie czy w komórce jest tylko adres mailowy.

Na liście z adresami mailowymi może okazać się, że ktoś dopisał jakiś inny tekst. Rozwiązaniem tego problemu, może być wykorzystanie operatora ^. Wpisanie go, gwarantuje nam, że to co jest po nim ma być na początku tekstu.

Podsumowanie

Jak widać na pokazanych przykładach funkcja REGEXTEST ma naprawdę szereg zastosowań począwszy od automatycznej klasyfikacji danych, przez czyszczenie danych poprzez sprawdzenie np. błędne wpisanych numerów telefonu czy też oczekiwanego formatu.

Zobacz również:

Szkolenia: