ZNAJDŹ w Excelu: pozycje znaków i poprawne formuły FIND

Jeżeli chcę wyciągnąć fragment kodu produktu albo tekst przed separatorem, najpierw muszę znać pozycję tego separatora. Do tego służy ZNAJDŹ, czyli angielskie FIND. Funkcja zwraca numer znaku, który mogę wykorzystać w kolejnej formule.

Zacznę od krótkich napisów, bo na nich łatwo zobaczyć pomyłkę o jeden znak. Potem pokażę pierwsze, kolejne i ostatnie wystąpienie oraz wyciągnięcie nazwy pliku. Przykłady są syntetyczne. Składnię i dostępność sprawdzono w dokumentacji Microsoft, a pozycje i operacje na napisach pomocniczo w JavaScript. Formuł nie uruchomiono w Excelu; pomocniczy test nie potwierdza zachowania konkretnego skoroszytu ani jego ustawień językowych.

Składnia ZNAJDŹ i znaczenie wyniku

W polskich przykładach używam średników, a w angielskich przecinków. Separator argumentów zależy od ustawień regionalnych. Nazwa funkcji i separator to dwa osobne elementy, które trzeba dopasować do własnej instalacji.

=ZNAJDŹ(tekst_szukany; w_tekście; [nr_poz_pocz])

Nawiasy kwadratowe oznaczają opcjonalny argument w opisie składni; nie wpisuję ich do gotowej formuły. Pierwszy argument określa szukany fragment, drugi tekst do przeszukania, trzeci pozycję rozpoczęcia. Pominięta pozycja początkowa oznacza 1. Numerowanie znaków również zaczyna się od 1.

Funkcja rozróżnia wielkie i małe litery. Szuka ciągu znaków, więc nie sprawdza, czy znaleziony fragment jest całym wyrazem. Wynik wskazuje początek dopasowania w całym napisie, także wtedy, gdy rozpocznę szukanie od dalszej pozycji. Reguły opisuje dokumentacja FIND.

Wielkość liter na przykładzie Excel

Rozpisuję napis Excel: wielkie E ma pozycję 1, x pozycję 2, c pozycję 3, małe e pozycję 4, a l pozycję 5. Taki prosty zapis wystarcza, żeby sprawdzić poniższe wyniki.

Wielkie E istnieje w napisie i powinno zostać znalezione. Błąd pojawia się dla wielkiego X, ponieważ w źródle jest małe x. Nie zmieniam danych na małe litery tylko po to, żeby pozbyć się błędu: najpierw ustalam, czy wielkość liter jest częścią reguły identyfikatora.

Jeżeli oczekujesz wyszukiwania bez tego rozróżnienia, odpowiednią alternatywą jest SZUKAJ.TEKST. To świadomy wybór sposobu dopasowania, a nie naprawa każdej sytuacji, w której ZNAJDŹ zwróciło błąd.

Pozycja początkowa i kolejne wystąpienie

W napisie Experience małe e występuje na pozycjach 4, 7 i 10. Pierwsza litera jest wielkim E, więc nie pasuje do małego e nawet przy wyszukiwaniu od początku.

Wynik 7 w drugim wierszu jest numerem znaku w całym Experience. Nie jest odległością od pozycji 5. To rozróżnienie przydaje się przy łączeniu formuły z FRAGMENT.TEKSTU.

Drugą spację w komórce A1 zawierającej a b c znajdę tak:

=ZNAJDŹ(" ";A1;ZNAJDŹ(" ";A1)+1)

Wewnętrzna funkcja daje 2. Dodanie 1 uruchamia kolejne szukanie od pozycji 3, więc wynik końcowy wynosi 4. Gdybym pominął dodawanie, znów znalazłbym tę samą spację. Przy wieloznakowym separatorze i wymaganiu dopasowań bez nakładania przesunąłbym początek o długość separatora.

Dla polskiego zdania Excel to potężne narzędzie! warto sprawdzić jeszcze trzy pozycje: t jest najpierw na 7, potężne zaczyna się na 10, a t szukane od 10 znajduje się na 12. Spacje liczą się tak samo jak litery. Nie pomijam ich podczas ręcznego sprawdzania.

Wyciąganie tekstu przed separatorem

W A1 wpisuję ola@example.test, bez cudzysłowów. Znak @ jest na pozycji 4. Chcąc pobrać trzy wcześniejsze znaki, odejmuję od tej pozycji 1.

=LEWY(A1;ZNAJDŹ("@";A1)-1)

Oczekiwany wynik to ola. To prosty przykład dzielenia tekstu, a nie walidator adresów pocztowych. Nie sprawdza istnienia domeny, dostarczalności ani wszystkich dopuszczalnych formatów adresu. Jeżeli w danych występuje więcej niż jeden separator, funkcja wykorzysta pierwsze dopasowanie.

Dla powtarzalnego importu ustaliłbym osobno, co zrobić z pustą komórką, brakiem @ oraz separatorem na początku. W ostatnim przypadku długość pobieranego fragmentu wynosi zero i wynik jest pustym tekstem. Taki rezultat może wyglądać podobnie do brakującej wartości, choć pochodzi z konkretnej struktury napisu.

Warto zachować źródłową kolumnę obok wyniku. Jeśli ktoś zgłosi błędne rozdzielenie, porównanie obu wartości pozwoli odtworzyć sytuację. Usunięcie oryginału zaraz po wyliczeniu fragmentu utrudnia sprawdzenie, czy problem leży w formule, czy w niestandardowym zapisie danych.

ZNAJDŹ a SZUKAJ.TEKST

W ZNAJDŹ gwiazdka jest szukanym znakiem dosłownym. Na przykład =ZNAJDŹ("*";"ab*cd") powinno dać 3. W SZUKAJ.TEKST gwiazdka może oznaczać ciąg znaków, a pytajnik pojedynczy znak. Do wyszukania dosłownej gwiazdki w tej drugiej funkcji służy ~*. Te zasady opisuje dokumentacja SZUKAJ.TEKST.

Nie zamieniam tych funkcji mechanicznie. Jeśli separator pochodzi z komórki i zawiera gwiazdkę, zmiana na SZUKAJ.TEKST może zmienić znaczenie szukania. Najpierw określam, czy szukam dosłownego tekstu, czy wzorca, i dobieram przykłady kontrolne do tej decyzji.

Ostatnia spacja: rozbij formułę na kroki

ZNAJDŹ nie ma argumentu „szukaj od końca”. Dla A1 równego a b c mogę policzyć spacje, zastąpić ostatnią unikalnym znacznikiem i odszukać znacznik. W tym przykładzie wybieram #, którego nie ma w danych.

=ZNAJDŹ("#";PODSTAW(A1;" ";"#";DŁ(A1)-DŁ(PODSTAW(A1;" ";""))))

Rachunek wygląda następująco: długość a b c wynosi 5, a długość abc wynosi 3. Różnica 2 jest liczbą spacji. PODSTAW zamienia drugą spację i tworzy a b#c. Pozycja # wynosi 4. Liczba separatorów służy tutaj jako numer wystąpienia, nie jako pozycja startowa wyszukiwania.

Czwarty argument PODSTAW wybiera konkretne wystąpienie. Bez niego funkcja zastąpiłaby wszystkie spacje. To inna funkcja niż ZASTĄP, która operuje na pozycji i liczbie znaków. Długość tekstu w polskim Excelu podaje DŁ, a nie LICZBA.ZNAKÓW. Nazwy i argumenty potwierdza dokumentacja PODSTAW.

Ta konstrukcja ma założenia: separator jest jednym znakiem, występuje przynajmniej raz, a znacznik nie występuje wcześniej w tekście. Dla a# b c znacznik koliduje z danymi i wyszukiwanie zwróci złą pozycję. Dla abc liczba wystąpień wynosi zero i trzeba obsłużyć brak separatora. Nie ukrywam tych przypadków pod ogólną obietnicą uniwersalnej formuły.

Nazwa pliku po ostatnim ukośniku odwrotnym

W A1 wpisuję C:\Data\report.xlsx. Ostatni znak \ jest na pozycji 8, więc nazwa pliku zaczyna się na 9. Dla tej kontrolowanej ścieżki mogę użyć poprzedniego pomysłu i pobrać resztę tekstu.

=FRAGMENT.TEKSTU(A1;ZNAJDŹ("#";PODSTAW(A1;"\";"#";DŁ(A1)-DŁ(PODSTAW(A1;"\";""))))+1;DŁ(A1))

Oczekiwany wynik to report.xlsx. W formule arkusza zapis "\" oznacza jeden ukośnik odwrotny. Nie podwajam go tak jak w niektórych językach programowania. Nadal obowiązują założenia o obecności separatora i braku znacznika # w źródle.

Nie zakładam też, że każda ścieżka kończy się nazwą pliku. Gdy ostatnim znakiem jest separator, pobrany fragment jest pusty. Warto rozpoznać tę sytuację osobno, jeżeli dalszy krok wymaga konkretnej nazwy. Do mieszanych ścieżek z różnymi separatorami potrzebna jest wcześniej ustalona reguła normalizacji.

Krótsze formuły w Microsoft 365 i Excelu 2024

Jeżeli odbiorcy mają obsługiwaną wersję, TEKST.PO pozwala pobrać tekst po ostatnim separatorze bez znacznika zastępczego. Ujemny numer wystąpienia oznacza szukanie od końca.

=TEKST.PO(A1;"\";-1)

Dla tej samej ścieżki oczekuję report.xlsx. Gdy potrzebuję pozycji ostatniej spacji w a b c, mogę wykorzystać długość tekstu przed nią:

=DŁ(TEKST.PRZED(A1;" ";-1))+1

W tej drugiej formule A1 musi ponownie zawierać a b c; wynik wynosi 4. Microsoft wymienia te funkcje dla Microsoft 365 i Excela 2024. Nie deklaruję ich dostępności w Excelu 2021. Sprawdź TEKST.PO i TEKST.PRZED. Przy braku separatora domyślny błąd tych nowych funkcji to #N/D, inny niż błąd braku dopasowania w ZNAJDŹ.

Błędy, puste wartości i kontrola wyniku

Brak dopasowania daje #ARG!, czyli angielskie #VALUE!. Ten sam rodzaj błędu może wynikać z pozycji początkowej równej zero albo większej od długości tekstu. Zanim opakuję formułę obsługą błędów, sprawdzam argumenty.

=JEŻELI.BŁĄD(ZNAJDŹ("@";A1);"Brak dopasowania lub błędne dane")
=CZY.LICZBA(ZNAJDŹ("@";A1))

Pierwsza formuła daje opis zamiast błędu, druga logiczne PRAWDA albo FAŁSZ. Samo PRAWDA potwierdza znalezienie znaku, nie poprawność całego adresu. Obsługa błędu nie powinna usuwać informacji potrzebnej do naprawy importu.

Pusty szukany tekst jest osobnym przypadkiem: przy zwykłym niepustym napisie ZNAJDŹ("";"Excel") zwraca 1. Jeśli zapytanie pochodzi z komórki, pilnuję, żeby przypadkowa pustka nie została uznana za znalezienie wymaganego fragmentu. Przykłady tutaj dotyczą zwykłych liter, polskich znaków i separatorów; pomocnicza kontrola nie obejmuje wszystkich złożonych sekwencji Unicode.

Na koniec biorę kilka rzeczywistych formatów z danego procesu, zastępuję dane syntetycznymi i zapisuję oczekiwany wynik. Uwzględniam brak separatora, dwa separatory, pusty tekst i znak na końcu. Taki mały zestaw łatwiej ocenić niż jedną długą formułę oglądaną bez danych. Do pracy z długością fragmentów przyda się też osobny opis funkcji DŁ.