Stáhněte si ukázkový sešit
Tento výukový program ukazuje, jak používat Funkce SUBSTITUTE aplikace Excel v Excelu najít a nahradit text.
SUBSTITUTE Přehled funkcí
Funkce SUBSTITUTE vyhledá a nahradí stávající text novým textovým řetězcem. Citlivý na velká písmena.
Chcete -li použít funkci SUBSTITUTE Excel Worksheet, vyberte buňku a zadejte:
(Všimněte si, jak se zobrazují vstupy vzorců)
Funkce SUBSTITUTE Syntaxe a vstupy:
| 1 | = SUBSTITUTE (text, old_text, new_text, instance_num) |
text - Původní řetězec textu.
starý_text - Textový řetězec, který chcete najít a nahradit.
nový_test - Náhradní text.
instance_num - VOLITELNÉ. Číslo instance, které se má nahradit. Je -li prázdné, nahradí se všechny instance.
Co je SUBSTITUT?
Funkce SUBSTITUTE v Excelu slouží k nahrazení řetězce znaků v textovém řetězci jiným zadaným textovým řetězcem.
Podívejme se na následující příklad, který nahradí řetězec „rychlá hnědá“ „pomalá bílá“.
| 1 | = NÁHRADA (C2, C3, C4) |
SUBSTITUTE hledá řetězec v C2, najde „quick brown“ a nahradí jej „slow white“.
Jak používat SUBSTITUTE
Funkce SUBSTITUTE přebírá 3 požadované argumenty a 1 volitelný:
- Text: Text, který chcete nahradit znaky
- Old_text: Text, který chcete nahradit
- New_text: Text, který chcete nahradit Old_text
- Instance_num (volitelně): Určuje výskyt Old_text, který chcete nahradit New_text. Ve výchozím nastavení budou všechny výskyty Old_text nahrazeny, pokud není zadána instance.
Vnoření NÁHRADA
Co kdybychom museli nahradit dvě části stejného řetězce? Podívejme se, jak bychom zkombinovali dvě funkce SUBSTITUT.
| 1 | = NÁHRADA (NÁHRADA (C2, C3, C4), C5, C6) |
Všimněte si, jak je výsledek SUBSTITUTE (C2, C3, C4) použit jako první argument pro druhý SUBSTITUTE. Pokud vzorec rozdělíme:
= NÁHRADA (C2, C3, C4) se vyhodnotí jako „The pomalu bílá liška přeskočí líného psa “.
Poté SUBSTITUTE („Pomalá bílá liška přeskakuje líného psa“, C5, C6), která se vyhodnotí jako „Pomalá bílá lev skáče přes líného psa “.
Rozdíl mezi NÁHRADOU a VYMĚNIT
Podobnou funkcí jako SUBSTITUTE je VYMĚNIT. Ve většině případů je lze použít zaměnitelně, nicméně platí následující pravidlo.
Funkce SUBSTITUTE by měla být použita, když textový řetězec k výměně je známo, nebo se nahrazuje celé slovo nebo slova, jako náš příklad lišky použitý dříve.
| 1 | = NÁHRADA (C2, C3, C4) |
Funkce REPLACE by měla být použita, když pozice znaků textu které mají být nahrazeny v řetězci jsou známy, nebo když se nahrazuje část slova nebo řetězce. Například odebrání pomlčky z řetězce čísel.
Funkci REPLACE lze také vnořit jako SUBSTITUTE, je však důležité si uvědomit, že relativní poloha počátečního znaku se může změnit na základě délky řetězce, který má být nahrazen v prvním kroku vnořeného vzorce.
Například nahrazení „rychlé hnědé“ výrazem „pomalá bílá“ nahradí 11 znaků 10 znaky. Pokud je tedy vnořený, bude počáteční znak pro druhé VYMĚNIT o 1 znak od počátečních pozic znaků.
NÁHRADNÍ TIPY
- SUBSTITUTE lze použít na podřetězce:
- Pamatujte, že musíte zadat instance_num, pokud se pokoušíte nahradit sadu znaků, které se v řetězci opakují:
Všimněte si, že 3030 je nesprávný, protože „20“ i „20“ v „2020“ byly nahrazeny „30“.
Máte zájem o další textové funkce?
Další informace najdete v našich dalších článcích o VYMĚNIT nebo o tom, jak se používají jiné textové funkce v Excelu, například LEFT, MID a RIGHT.
SUBSTITUTE v Tabulkách Google
Funkce SUBSTITUTE funguje v Tabulkách Google úplně stejně jako v Excelu:
Další poznámky
Funkce SUBSTITUTE rozlišuje velká a malá písmena !. Před použitím funkce SUBSTITUTE můžete pomocí funkcí LOWER nebo UPPER převést řetězce textu na konzistentní případ. Potom, pokud je to žádoucí, můžete použít funkci PROPER k použití velkých písmen na začátku každého slova.
Alternativně můžete použít funkce VYHLEDÁVÁNÍ a VYMĚNIT k simulaci Substituce, který nerozlišuje malá a velká písmena. Pomocí funkce SEARCH vyhledejte počáteční pozici textového řetězce. Funkce SEARCH nerozlišuje velká a malá písmena. Poté použijte funkci REPLACE k nahrazení textu. Výsledek z funkce SEARCH budete muset použít jako vstup start_num v REPLACE. Budete také muset definovat počet znaků, které chcete nahradit (num_chars). Můžete je ručně spočítat nebo pomocí funkce LEN spočítat počet znaků. Příklad:
Náhradník - Odstraňte spojovníky
Častou otázkou je, jak z textu odstranit spojovníky. Jde o to, nahradit spojovník ničím.
V tomto případě předpokládejme, že buňka A1 obsahuje text s pomlčkami. Zde je návod, jak je odstranit
1. V prázdné buňce pomocné buňky = SUBSTITUTE (
2. Klikněte na buňku, ve které chcete nahradit znaky nebo A1 (tím se automaticky vyplní váš vzorec)
3. Typ, (čárka)
4. Zadejte „-“
5. Typ, (čárka)
6. Zadejte „“ a stiskněte klávesu Enter
Váš konečný výsledek ve vaší pomocné buňce by měl vypadat takto:
= NÁHRADA (A1, ”-„, ””)
3 Další příklady náhrad:
Při pohledu na buňku B3 chceme nahradit slovo „nemocný“ výrazem „velký“. Můžeme použít následující výraz:
| 1 | = NÁHRADA (B3, "nemocný", "velký", 1) |
Pokud se text, který chceme nahradit, vyskytuje více než jednou, pak musíme určit instanci, kterou máme na mysli. Ve druhém příkladu máme dva výskyty slova „starý“. Pokud tedy chceme změnit druhou instanci na slovo „šedá“, musíme použít:
| 1 | = NÁHRADA (B4, "starý", "šedý", 2) |
Všimněte si, že pokud není výskyt zadán, budou nahrazeny všechny instance. Tak:
| 1 | = NÁHRADA (B4, "starý", "šedý") |
Zbavil by se všech výskytů slova „starý“ a nahradil by je slovem „šedý“. Všimněte si, že pokud nelze najít starý text, řetězec se nezmění. Podíváme -li se tedy na poslední příklad:
| 1 | = NÁHRADA (B5, "černá", "šedá", 1) |
Znamená to, že se pokusíme nahradit slovo „černý“ slovem „šedý“. Protože se však „černé“ nevyskytuje, původní řetězec zůstal levitra dodávaná ve Spojených státech beze změny:
Chcete -li stáhnout soubor .XLSX z tohoto článku, klikněte sem
NÁHRADNÍ Příklady ve VBA
Můžete také použít funkci SUBSTITUTE ve VBA. Typ:application.worksheetfunction.substitute (text, old_text, new_text, instance_num)
Argumenty funkce (text atd.) Můžete buď zadat přímo do funkce, nebo definovat proměnné, které se místo toho použijí.
Po spuštění následujícího kódu
| 1 | Rozsah ("A1") = WorksheetFunction.Substitute (Rozsah ("A1"), "pomocí", "", 1) |
Obsah buňky A1 se stane:
Slovo „using“ bylo tedy nahrazeno prázdným řetězcem. Poslední parametr (1) udává, kterou instanci textu je třeba nahradit v případě, že je text přítomen více než jednou. Pokud například provedeme následující příkaz
| 1 | Rozsah ("A1") = WorksheetFunction.Substitute (Rozsah ("A1"), "the", "an", 1) |
Buňka A1 bude vypadat takto
Všimněte si, že první výskyt „the“ byl nahrazen „an“, zatímco druhý „the“ nebyl ovlivněn. Pokud bychom vynechali poslední parametr, byla by nahrazena obě slova „the“
Zpět na seznam všech funkcí v Excelu
