SUBSTITUTE Funkcja Excel - Znajdź i zamień tekst

Pobierz przykładowy skoroszyt

Pobierz przykładowy skoroszyt

Ten samouczek pokazuje, jak używać Excel SUBSTITUTE Funkcja w programie Excel, aby znaleźć i zamienić tekst.

Przegląd funkcji SUBSTITUTE

Funkcja SUBSTITUTE Znajduje i zastępuje istniejący tekst nowym ciągiem tekstowym. Wielkość liter ma znaczenie.

Aby użyć funkcji arkusza kalkulacyjnego SUBSTITUTE Excel, wybierz komórkę i wpisz:

(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)

Funkcja SUBSTITUTE Składnia i wejścia:

1	= PODSTAWA(tekst;stary_tekst;nowy_tekst;numer_instancji)

tekst - Oryginalny ciąg tekstu.

stary_tekst – ciąg tekstowy, który chcesz znaleźć i zamienić.

nowy_test - Tekst zastępczy.

numer_instancji - Opcjonalny. Numer instancji do zastąpienia. Jeśli puste, wszystkie instancje zostaną zastąpione.

Co to jest ZAMIENNIK?

Funkcja SUBSTITUTE w programie Excel służy do zastępowania ciągu znaków w ciągu tekstowym innym określonym ciągiem tekstowym.

Spójrzmy na poniższy przykład, w którym zamieniamy ciąg „szybki brąz” na „powolny biały”.

1	=ZAMIENNIK(C2,C3;C4)

SUBSTITUTE zagląda w strunę w C2, znajduje „szybki brąz” i zastępuje go „wolnym białym”.

Jak używać SUBSTITUTE

Funkcja SUBSTITUTE przyjmuje 3 wymagane argumenty i 1 opcjonalny:

• Tekst: tekst, który chcesz zastąpić znaki
• Stary_tekst: tekst, który chcesz zastąpić
• Nowy_tekst: tekst, którym chcesz zastąpić Stary_tekst
• Instance_num (opcjonalne): Określa wystąpienie starego_tekstu, który chcesz zastąpić nowym_tekstem. Domyślnie wszystkie wystąpienia Old_text zostaną zastąpione, jeśli instancja nie zostanie określona.

Zagnieżdżanie ZAMIENNIK

Co by było, gdybyśmy musieli podstawić dwie części tego samego ciągu? Zobaczmy, jak połączylibyśmy dwie funkcje SUBSTITUTE.

1	=ZAMIANA(ZAMIANA(C2,C3;C4);C5;C6)

Zwróć uwagę, jak wynik SUBSTITUTE(C2,C3,C4) jest używany jako pierwszy argument dla drugiego SUBSTITUTE. Jeśli rozłożymy formułę na części:

=SUBSTITUTE(C2,C3,C4) zwraca wartość „The powolny biały lis przeskakuje nad leniwym psem”.

Następnie SUBSTITUTE("Powolny biały lis przeskakuje nad leniwym psem", C5,C6), co daje wynik "Powolny biały lis Lew przeskakuje nad leniwym psem”.

Różnica między SUBSTITUTE a REPLACE

Podobną funkcją do SUBSTITUTE jest REPLACE. W większości przypadków można ich używać zamiennie, jednak obowiązuje następująca zasada.

Funkcji SUBSTITUTE należy używać, gdy ciąg tekstu do wymiany jest znany, lub całe słowo lub słowa są zastępowane, jak w naszym przykładzie z lisem.

1	=ZAMIENNIK(C2,C3;C4)

Funkcji REPLACE należy używać, gdy pozycja znaków tekstu do wymiany w strunie są znane, lub gdy część słowa lub ciągu jest zastępowana. Na przykład usunięcie łącznika z ciągu liczb.

Funkcja REPLACE może być również zagnieżdżona, podobnie jak SUBSTITUTE, jednak należy pamiętać, że względna pozycja znaku początkowego może się zmieniać w zależności od długości ciągu, który ma zostać zastąpiony w pierwszym kroku formuły zagnieżdżonej.

Na przykład zastąpienie „szybkiego brązu” słowem „powolna biel” oznacza zastąpienie 11 znaków 10 znakami. Dlatego, jeśli jest zagnieżdżony, początkowy znak dla drugiej REPLACE będzie o 1 znak od początkowych pozycji znaków.

Wskazówki dotyczące zamienników

• SUBSTITUTE może być użyty na podciągach:
• Pamiętaj, że musisz określić numer_instancji, jeśli próbujesz zastąpić zestaw znaków, które powtarzają się w ciągu:

Zauważ, że 3030 jest niepoprawny, ponieważ zarówno „20”, jak i „20” w „2020” zostały zastąpione przez „30”.

Interesują Cię funkcje Więcej funkcji tekstowych?

Zobacz nasze inne artykuły, aby uzyskać więcej informacji na temat ZAMIEŃ lub tego, jak używane są inne funkcje tekstu w programie Excel, takie jak LEWY, ŚRODKOWY i PRAWY.

SUBSTITUTE w Arkuszach Google

Funkcja SUBSTITUTE działa dokładnie tak samo w Arkuszach Google jak w Excelu:

Dodatkowe uwagi

Funkcja SUBSTITUTE rozróżnia wielkość liter!. Możesz użyć funkcji LOWER lub UPPER, aby przekonwertować ciągi tekstu na spójną wielkość liter przed użyciem funkcji SUBSTITUTE. Następnie, jeśli chcesz, możesz użyć funkcji WŁAŚCIWE, aby zmienić pierwszą literę każdego słowa na wielką.

Alternatywnie możesz użyć funkcji SZUKAJ i ZAMIEŃ, aby zasymulować SUBSTITUTE bez rozróżniania wielkości liter. Użyj funkcji SEARCH, aby znaleźć pozycję początkową ciągu tekstowego. W funkcji SEARCH nie jest rozróżniana wielkość liter. Następnie użyj funkcji REPLACE, aby zastąpić tekst. Będziesz musiał użyć wyniku z funkcji SEARCH jako danych wejściowych numer_początkowy w REPLACE. Będziesz także musiał określić liczbę znaków do zastąpienia (liczba_znaków). Możesz je policzyć ręcznie lub użyć funkcji LEN, aby policzyć liczbę znaków. Przykład:

Zastąp - usuń łączniki

Częstym pytaniem jest, jak usunąć myślniki z tekstu. Sztuką jest zastąpienie myślnika niczym.

W tym przykładzie załóżmy, że komórka A1 zawiera tekst z myślnikami. Oto jak je usunąć

1. W pustej komórce pomocniczej wpisz =SUBSTITUTE(
2. Kliknij komórkę, w której chcesz zastąpić znaki lub A1 (to automatycznie wypełnia formułę)
3. Wpisz (przecinek)
4. Wpisz „-”
5. Wpisz (przecinek)
6. Wpisz „” i naciśnij enter
Twój wynik końcowy w komórce pomocniczej powinien wyglądać tak:
=ZAMIENNIK(A1,”-„,””)

3 Więcej przykładów zastępczych:

Patrząc na komórkę B3, chcemy zastąpić słowo „chory” słowem „duży”. Możemy użyć następującego wyrażenia:

1	=ZAMIENNIK(B3;"chory","duży";1)

Jeśli tekst, który chcemy zastąpić, występuje więcej niż raz, musimy określić instancję, którą mamy na myśli. W drugim przykładzie mamy dwa wystąpienia słowa „stary”. Jeśli więc chcemy zmienić drugą instancję na słowo „szary”, musimy użyć:

1	=ZAMIENNIK(B4,"stary","szary";2)

Zauważ, że jeśli wystąpienie nie zostanie określone, wszystkie wystąpienia zostaną zastąpione. Więc:

1	=ZAMIENNIK(B4,"stary","szary")

Pozbędzie się wszystkich wystąpień słowa „stary” i zastąpi je słowem „szary”. Zauważ, że jeśli nie można znaleźć starego tekstu, ciąg pozostaje niezmieniony. Patrząc więc na ostatni przykład:

1	=ZAMIENNIK(B5;"czarny","szary";1)

Oznacza to, że próbujemy zastąpić słowo „czarny” słowem „szary”. Ponieważ jednak „czarny” nie występuje, oryginalna struna pozostała niezmieniona levitra wysłana w Stanach Zjednoczonych:

Aby pobrać plik .XLSX z tego artykułu, kliknij tutaj

SUBSTITUTE Przykłady w VBA

Możesz także użyć funkcji SUBSTITUTE w VBA. Rodzaj:
application.worksheetfunction.substitute(tekst,stary_tekst,nowy_tekst,numer_instancji)
W przypadku argumentów funkcji (tekstu itp.) można wprowadzić je bezpośrednio do funkcji lub zdefiniować zmienne, które będą używane w zamian.

Po wykonaniu poniższego kodu

1	Range("A1") = WorksheetFunction.Substitute(Range("A1"), "using", "", 1)

Zawartość komórki A1 stanie się:

Tak więc słowo „używanie” zostało zastąpione pustym ciągiem. Ostatni parametr (1) wskazuje, które wystąpienie tekstu należy zastąpić, jeśli tekst występuje więcej niż jeden raz. Na przykład, jeśli wykonamy następującą instrukcję

1	Range("A1") = WorksheetFunction.Substitute(Range("A1"), "the", "an", 1)

Komórka A1 będzie wyglądać tak

Zauważ, że pierwsze wystąpienie „the” zostało zastąpione przez „an”, podczas gdy drugie „the” nie zostało naruszone. Gdybyśmy pominęli ostatni parametr, oba „te” słowa zostałyby zastąpione

Wróć do listy wszystkich funkcji w Excelu