PODSTAW w Excelu: SUBSTITUTE, wystąpienia i czyszczenie tekstu

PODSTAW zamienia wskazany fragment tekstu na inny. Angielska nazwa tej funkcji to SUBSTITUTE. Łatwo pomylić ją z ZASTĄP, ale to dwie różne funkcje: jedna szuka treści, druga zmienia znaki na wskazanej pozycji. Ja wybieram narzędzie dopiero po ustaleniu, co dokładnie ma zostać poprawione w danych.

Na małych, syntetycznych przykładach pokażę zamianę wszystkich wystąpień, jednego wybranego wystąpienia oraz różnicę między czyszczeniem napisu a zmianą jego znaczenia. Zostawiam kolumnę źródłową, żeby wynik zawsze dało się porównać z wejściem.

PODSTAW i ZASTĄP mają inne zadania

Dokumentacja PODSTAW opisuje zamianę konkretnego starego tekstu na nowy. ZASTĄP przyjmuje pozycję początkową i liczbę znaków. Ten drugi mechanizm jest przydatny przy stałym układzie napisu, lecz wymaga innej informacji wejściowej.

=PODSTAW(tekst;stary_tekst;nowy_tekst;[wystąpienie_liczba])

Pierwszy argument wskazuje napis albo komórkę źródłową. Drugi określa szukany fragment, trzeci jego zamiennik. Opcjonalny czwarty wybiera wystąpienie. Gdy go pomijasz, zamieniane są wszystkie wystąpienia wskazanego fragmentu. Numer wystąpienia liczy dopasowania tekstu, a nie znaki czy słowa.

Przykłady używają polskich nazw i typowych separatorów: średnika między argumentami. W angielskim Excelu odpowiednikiem jest SUBSTITUTE(text,old_text,new_text,[instance_num]). Sprawdź ustawienia regionalne, jeżeli kopiowana formuła jest odrzucana jeszcze przed obliczeniem. Cudzysłowy wokół stałych tekstowych również należą do składni.

Zamień wszystkie wystąpienia albo tylko jedno

W A2 wpisz syntetyczny kod AA-AA-AA. W B2 umieść pierwszą formułę:

=PODSTAW(A2;"AA";"BB")

Oczekiwany wynik to BB-BB-BB. W nowej komórce wpisz wariant z czwartym argumentem:

=PODSTAW(A2;"AA";"BB";2)

Tym razem wynik to AA-BB-AA, bo zmieniasz wyłącznie drugie wystąpienie AA. Łączniki pozostają na miejscu. Na tak krótkim napisie łatwo prześledzić każde dopasowanie i upewnić się, że numer oznacza właściwą część.

Wariant =PODSTAW(A2;"-";"";1) usuwa pierwszy łącznik, dając AAAA-AA. Pusty tekst "" jest celowym zamiennikiem. Przed użyciem takiej reguły na kodach produktu ustal jednak, czy separator nie rozróżnia dwóch różnych identyfikatorów. Estetycznie podobne napisy mogą mieć w systemie inne znaczenie.

Zamiana fragmentu nie rozpoznaje całych słów

W A3 wpisz kot i kotlet, a obok zastosuj:

=PODSTAW(A3;"kot";"pies")

Wynikiem będzie pies i pieslet. Funkcja szuka wskazanej sekwencji znaków, bez rozumienia granic słów. W tym przykładzie mechaniczna zamiana jest zgodna z regułą, a mimo to psuje sens zdania. To dobry powód, żeby obejrzeć różne rodzaje rekordów przed zastosowaniem operacji do całej kolumny.

Podobne ryzyko pojawia się przy automatycznej korekcie skrótów adresowych. Zbyt krótki fragment może wystąpić także w nazwie ulicy albo komentarzu. Ja zapisuję najpierw warunek biznesowy: jaki błąd poprawiam i gdzie może się pojawić. Jeżeli reguła wymaga rozpoznawania całego pola lub kategorii, samo zastąpienie podciągu może być zbyt szerokie.

Traktuj wielkość liter jako część wzorca. Nie projektuj reguły z założeniem, że jeden zapis obejmie automatycznie wszystkie warianty pisowni. Ujednolicenie wielkości liter wymaga osobnej decyzji, szczególnie w nazwach własnych oraz identyfikatorach. Przewodnik Microsoft o czyszczeniu danych rozdziela takie operacje na osobne kroki.

Wybierz zamianę według treści albo pozycji

Dla napisu AB-123 dwie poniższe formuły dadzą CD-123:

=PODSTAW("AB-123";"AB";"CD")
=ZASTĄP("AB-123";1;2;"CD")

Pierwsza szuka AB, druga zmienia dwa znaki od pozycji 1. Jeżeli następny rekord ma inny prefiks, ZASTĄP nadal zmieni pierwsze dwa znaki. PODSTAW zareaguje na wskazaną treść. Przed wyborem zapisz, czy stała jest pozycja, czy wyszukiwany fragment.

Nie musisz wybierać najbardziej rozbudowanej formuły. W kontrolowanym formacie pozycyjne zastępowanie bywa czytelne. Przy zmiennym położeniu znanego fragmentu wygodniejsza może być zamiana po treści. Problem pojawia się wtedy, gdy w arkuszu zostaje formuła bez informacji, na jaką strukturę wejścia została przygotowana.

Spacje i liczby wymagają własnej reguły

Usunięcie wszystkich spacji formułą =PODSTAW(A4;" ";"") ma sens tylko wtedy, gdy żadna zwykła spacja nie jest potrzebna. Dla AB 12 otrzymasz AB12, ale dla Nowy Sącz powstanie NowySącz. Nie używaj takiej zamiany jako uniwersalnego sposobu porządkowania nazw.

USUŃ.ZBĘDNE.ODSTĘPY służy innemu celowi: porządkuje zwykłe spacje, pozostawiając pojedyncze między słowami. Dokumentacja zaznacza ograniczenie dotyczące spacji nierozdzielającej. Jeśli dwa napisy wyglądają identycznie, najpierw ustal rodzaj znaków. Sama szerokość odstępu na ekranie nie daje tej odpowiedzi.

Zamiana przecinka na kropkę też nie ustala automatycznie sensu liczby. W jednym źródle przecinek może rozdzielać część dziesiętną, w innym pełnić rolę separatora tysięcy. Najpierw wyjaśnij format danych, później projektuj konwersję. Do powtarzalnego importu i jawnego ustawienia typów rozważ Power Query.

Zachowaj źródło i obejrzyj wyjątki

W kolumnie A zostaw oryginał, w B wynik, a obok krótki opis reguły. Wybierz kilka rekordów: poprawiany, już poprawny i taki, który zawiera podobny fragment w innym kontekście. Obejrzyj także pustą komórkę oraz ewentualny błąd w źródle. Nie zakładaj, że każdy odmienny przypadek wymaga tej samej reakcji.

Przy kilku podmianach pokaż etapy w osobnych kolumnach. Kolejność może zmienić wynik, jeśli pierwszy zamiennik tworzy fragment szukany przez drugą operację. Dopiero po ocenie całego procesu zdecyduj, czy warto go skrócić. Jedna długa formuła oszczędza miejsce na ekranie, lecz może utrudnić wyjaśnienie nieoczekiwanego wyniku.

Ćwiczenie na zakończenie

Dla AA-AA-AA zamień tylko trzecie AA na CC, a następnie osobno usuń wszystkie łączniki z oryginału. Oczekiwane wyniki to AA-AA-CC i AAAAAA. Potem wyjaśnij, dlaczego reguła zamieniająca kot nie nadaje się do poprawiania wyłącznie całych słów.

Zanim przeniesiesz formułę do własnego pliku, dopisz jedno zdanie o tym, które fragmenty wolno zmieniać. Ta decyzja jest częścią jakości danych. Formuła może wykonać podmianę bezbłędnie i jednocześnie usunąć informację, którą raport miał zachować.