Zastępowanie tekstu za pomocą SUBSTITUTE

Dane często wymagają wyszukiwania i zastępowania bezpośrednio w formułach: usuwania myślników z numerów telefonów, zamiany słowa albo usuwania przypadkowych symboli.

Funkcja SUBSTITUTE wyszukuje fragment tekstu w komórce i zastępuje każde jego wystąpienie nowym tekstem, nie zmieniając przy tym oryginalnej komórki.

Jest opartym na formule odpowiednikiem funkcji Znajdź & zamień, ale aktualizuje wynik automatycznie wraz ze zmianą danych.

Funkcja SUBSTITUTE przyjmuje maksymalnie cztery argumenty:

• text oryginalny ciąg znaków lub komórka
• old_text wyszukiwany tekst
• new_text tekst, który ma go zastąpić
• instance_num (opcjonalnie) numer wystąpienia, które ma zostać zastąpione

Ogólna postać to =SUBSTITUTE(text, old_text, new_text, [instance_num]). Jeśli pominą Państwo ostatni argument, zastąpione zostaną wszystkie wystąpienia.

Popularnym sposobem jest zastąpienie czegoś niczym, aby to usunąć. W tym celu należy ustawić new_text na pusty ciąg znaków "".

Jeśli A2 zawiera wartość 555-123-4567, poniższa formuła usuwa każdy myślnik i zwraca wartość 5551234567.

Ten schemat służy do czyszczenia numerów telefonów, identyfikatorów i innych wartości zawierających znaki interpunkcyjne.

Funkcja SUBSTITUTE działa również na całych słowach, a nie tylko na pojedynczych znakach.

Jeśli A2 zawiera wartość Annual report 2023, można zaktualizować rok, zastępując słowo:

Wynikiem jest Annual report 2024. Każde pasujące wystąpienie w komórce zostaje zmienione.

Ważne: SUBSTITUTE dopasowuje tekst dokładnie, z uwzględnieniem wielkości liter.

Jeśli A2 zawiera wartość Cat cat CAT, formuła =SUBSTITUTE(A2, "cat", "dog") zmienia tylko zapisane małymi literami cat, zwracając Cat dog CAT.

Jeśli potrzebują Państwo zastępowania bez rozróżniania wielkości liter, należy najpierw ujednolicić wielkość liter za pomocą LOWER lub UPPER albo użyć funkcji wyrażeń regularnych z następnej lekcji w Sheets.

Opcjonalny czwarty argument, instance_num, pozwala wskazać pojedyncze wystąpienie zamiast zastępować wszystkie.

Jeśli A2 zawiera wartość a-b-c-d i chcą Państwo zmienić tylko drugi myślnik, należy ustawić instance_num na 2.

Wynikiem jest a-b/c-d: tylko drugi myślnik został zamieniony na ukośnik.

Wyobraźmy sobie zaimportowaną kwotę przechowywaną jako tekst: $1,250.00. Aby przekształcić ją w użyteczną liczbę, trzeba usunąć znak dolara i przecinek.

Należy zagnieździć dwa wywołania funkcji SUBSTITUTE, jedno w drugim, aby usunąć oba znaki.

Wewnętrzne wywołanie usuwa przecinek, a zewnętrzne znak dolara, pozostawiając wartość 1250.00.

Można zagnieździć tyle wywołań funkcji SUBSTITUTE, ile potrzeba, aby każde z nich usuwało inny znak.

Aby ujednolicić nieuporządkowany kod zawierający spacje, kropki i ukośniki, zastępując je myślnikami, należy połączyć trzy zastąpienia.

Każda warstwa przekazuje swój wynik do następnej, tworząc końcowy oczyszczony ciąg znaków.

Nie należy mylić funkcji SUBSTITUTE z podobną funkcją REPLACE.

• SUBSTITUTE zastępuje tekst, dopasowując jego treść (znajdź to słowo i je zamień).
• REPLACE zastępuje tekst na podstawie pozycji (zastąp 4 znaki, zaczynając od pozycji 5).

Należy używać SUBSTITUTE, gdy wiadomo, czego szukać, a REPLACE, gdy wiadomo, gdzie znajduje się dany tekst.

Przydatna sztuczka polega na połączeniu funkcji SUBSTITUTE z LEN w celu zliczenia, ile razy coś występuje.

Należy odjąć długość tekstu po usunięciu znaku od jego długości pierwotnej. Jeśli A2 zawiera wartość a,b,c,d, pozwala to policzyć przecinki i zwraca wynik 3.

Ta sztuczka świetnie sprawdza się przy zliczaniu elementów na liście rozdzielanej separatorami.

Załóżmy, że A2 zawiera numery w niejednolitym formacie, z kropkami i spacjami, na przykład 555.123 4567. Chcą Państwo uzyskać spójny format z myślnikami: 555-123-4567.

Najpierw należy usunąć kropki i spacje, a następnie można ponownie wstawić myślniki; na razie wystarczy po prostu ujednolicić separatory, zastępując je myślnikami.

Zagnieżdżanie kolejnych zastąpień ujednolica dowolne przypadkowe separatory.

Proszę sprawdzić, jak dobrze rozumieją Państwo funkcję SUBSTITUTE.

Poznali Państwo sposób wyszukiwania i zastępowania tekstu w formułach:

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Użycie "" jako new_text pozwala usuwać znaki.
• Funkcja rozróżnia wielkość liter; w razie potrzeby należy najpierw ujednolicić wielkość liter.
• Opcjonalny instance_num wskazuje pojedyncze wystąpienie.
• Wywołania można zagnieżdżać, aby oczyścić wiele znaków, a połączenie z LEN pozwala zliczać wystąpienia.

Ukończyli Państwo część dotyczącą łączenia i zmiany tekstu. Świetna praca nad tworzeniem uporządkowanych i spójnych danych!