Stiahnite si ukážkový zošit
Tento návod ukazuje, ako používať Funkcia SUBSTITUTE programu Excel v programe Excel nájsť a nahradiť text.
SUBSTITUTE Prehľad funkcií
Funkcia SUBSTITUTE vyhľadá a nahradí existujúci text novým textovým reťazcom. Rozlišujú sa malé a veľké písmená.
Ak chcete použiť funkciu pracovného listu SUBSTITUTE Excel, vyberte bunku a zadajte:
(Všimnite si, ako sa zobrazujú vstupy vzorcov)
Funkcia SUBSTITUTE Syntax a vstupy:
| 1 | = SUBSTITUTE (text, starý_text, nový_text, číslo_inštancie) |
text - Pôvodný reťazec textu.
starý_text - Textový reťazec, ktorý chcete nájsť a nahradiť.
nový_test - náhradný text.
instance_num - VOLITEĽNÉ. Číslo inštancie, ktoré sa má nahradiť. Ak je pole prázdne, nahradia sa všetky inštancie.
Čo je to NÁHRADA?
Funkcia SUBSTITUTE v programe Excel sa používa na nahradenie reťazca znakov v textovom reťazci iným zadaným textovým reťazcom.
Pozrime sa na nasledujúci príklad, ktorý nahradí reťazec „rýchlo hnedá“ „pomalá biela“.
| 1 | = NÁHRADA (C2, C3, C4) |
SUBSTITUTE hľadá reťazec v C2, nájde „quick brown“ a nahradí ho „slow white“.
Ako používať SUBSTITUTE
Funkcia SUBSTITUTE má 3 povinné argumenty a 1 voliteľný:
- Text: Text, ktorý chcete nahradiť znakmi
- Old_text: Text, ktorý chcete nahradiť
- Nový_text: Text, ktorým chcete nahradiť starý_text
- Instance_num (voliteľné): Špecifikuje výskyt textu Old_text, ktorý chcete nahradiť textom New_text. Štandardne budú všetky výskyty Old_text nahradené, ak nie je zadaná inštancia.
Hniezdenie NÁHRADA
Čo keby sme museli nahradiť dve časti toho istého reťazca? Pozrime sa, ako by sme skombinovali dve funkcie SUBSTITUTE.
| 1 | = NÁHRADA (NÁHRADA (C2, C3, C4), C5, C6) |
Všimnite si, ako sa výsledok SUBSTITUTE (C2, C3, C4) použije ako prvý argument pre druhý SUBSTITUTE. Ak vzorec rozdelíme:
= NÁHRADA (C2, C3, C4) sa hodnotí ako „The pomalá biela líška skáče cez lenivého psa “.
Potom SUBSTITUTE („Pomalá biela líška skáče cez lenivého psa“, C5, C6), ktorý vyhodnotí ako „Pomalý biely lev skáče cez lenivého psa “.
Rozdiel medzi VÝHRADOU a NÁHRADOU
Podobnou funkciou ako SUBSTITUTE je REPLACE. Vo väčšine prípadov je možné ich používať zameniteľne, avšak platí nasledujúce pravidlo.
Funkciu SUBSTITUTE by ste mali používať vtedy, keď reťazec textu vymeniť je známe, alebo sa nahrádza celé slovo alebo slová, podobne ako v predchádzajúcom prípade.
| 1 | = NÁHRADA (C2, C3, C4) |
Funkciu REPLACE by ste mali používať vtedy, keď poloha znakov textu ktoré majú byť nahradené v reťazci sú známe, alebo keď sa nahrádza časť slova alebo reťazca. Napríklad odstránenie spojovníka z reťazca čísel.
Funkciu REPLACE je možné vnoriť aj ako SUBSTITUT, je však dôležité poznamenať, že relatívna poloha začiatočného znaku sa môže zmeniť v závislosti od dĺžky reťazca, ktorý sa má nahradiť v prvom kroku vnoreného vzorca.
Napríklad nahradenie výrazu „rýchlo hnedá“ výrazom „pomalá biela“ nahradí 11 znakov 10 znakmi. Ak je teda vnorený, začiatočný znak pre druhú REPLACE bude vzdialený 1 znak od počiatočných pozícií znakov.
NÁHRADNÉ Tipy
- SUBSTITUTE je možné použiť na podreťazci:
- Nezabudnite, že ak sa pokúšate nahradiť množinu znakov, ktoré sa v reťazci opakujú, musíte zadať číslo_inštancie:
Všimnite si, že 3030 je nesprávny, pretože „20“ aj „20“ v „2020“ boli nahradené „30“.
Máte záujem o viac textových funkcií?
Pozrite si naše ďalšie články, kde nájdete ďalšie informácie o REPLACE alebo o tom, ako sa používajú iné textové funkcie v programe Excel, ako napríklad LEFT, MID a RIGHT.
SUBSTITUTE v Tabuľkách Google
Funkcia SUBSTITUTE funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
Doplňujúce Poznámky
Funkcia SUBSTITUTE rozlišuje veľké a malé písmená !. Pred použitím funkcie SUBSTITUTE môžete pomocou funkcií LOWER alebo UPPER previesť reťazce textu na konzistentné puzdro. Potom, ak je to žiaduce, môžete použiť funkciu PROPER na veľké začiatočné písmeno každého slova.
Alternatívne môžete použiť funkcie VYHĽADÁVANIE a VYMENIŤ na simuláciu NÁHRADY, ktorá nerozlišuje malé a veľké písmená. Na vyhľadanie počiatočnej polohy textového reťazca použite funkciu SEARCH. Funkcia SEARCH nerozlišuje malé a veľké písmená. Potom pomocou funkcie REPLACE nahraďte text. Výsledok z funkcie VYHĽADÁVANIE budete musieť použiť ako vstupný_číslo v REPLACE. Budete tiež musieť definovať počet znakov, ktoré sa majú nahradiť (num_chars). Môžete ich spočítať ručne alebo pomocou funkcie LEN spočítať počet znakov. Príklad:
Náhradník - Odstráňte spojovníky
Častou otázkou je, ako odstrániť spojovníky z textu. Ide o to, nahradiť spojovník ničím.
V tomto prípade predpokladajme, že bunka A1 obsahuje text so spojovníkmi. Tu je postup, ako ich odstrániť
1. V prázdnej bunke pomocníka = NÁHRADA (
2. Kliknite na bunku, v ktorej chcete nahradiť znaky alebo A1 (tým sa automaticky vyplní váš vzorec)
3. Typ (čiarka)
4. Napíšte „-“
5. Napíšte (čiarka)
6. Napíšte „“ a stlačte kláves Enter
Váš konečný výsledok vo vašej pomocnej bunke by mal vyzerať takto:
= NÁHRADA (A1, ”-„, ””)
3 ďalšie náhradné príklady:
Pri pohľade na bunku B3 chceme nahradiť slovo „chorý“ výrazom „veľký“. Môžeme použiť nasledujúci výraz:
| 1 | = NÁHRADA (B3, „chorý“, „veľký“, 1) |
Ak sa text, ktorý chceme nahradiť, vyskytuje viackrát, potom musíme zadať inštanciu, ktorú máme na mysli. V druhom prípade máme dva výskyty slova „starý“. Ak teda chceme zmeniť druhú inštanciu na slovo „sivá“, musíme použiť:
| 1 | = NÁHRADA (B4, „starý“, „sivý“, 2) |
Ak výskyt nie je zadaný, nahradia sa všetky inštancie. Takže:
| 1 | = NÁHRADA (B4, „starý“, „sivý“) |
Zbavil by sa všetkých výskytov slova „starý“ a nahradil by ich slovom „sivé“. Ak starý text nemožno nájsť, reťazec sa nezmení. Pri pohľade na posledný príklad:
| 1 | = NÁHRADA (B5, "čierna", "sivá", 1) |
Znamená to, že sa pokúsime nahradiť slovo „čierny“ slovom „sivé“. Pretože sa však nevyskytuje „čierny“, pôvodný reťazec zostal levitra dodávaný v Spojených štátoch nezmenený:
Kliknite sem, ak si chcete stiahnuť súbor .XLSX z tohto článku
NÁHRADNÉ Príklady vo VBA
Vo VBA môžete použiť aj funkciu SUBSTITUTE. Typ:application.worksheetfunction.substitute (text, old_text, new_text, instance_num)
Pokiaľ ide o argumenty funkcie (text atď.), Môžete ich buď zadať priamo do funkcie, alebo definovať premenné, ktoré sa namiesto toho použijú.
Keď sa spustí nasledujúci kód
| 1 | Rozsah ("A1") = WorksheetFunction.Substitute (Rozsah ("A1"), "using", "", 1) |
Obsah bunky A1 bude:
Slovo „using“ bolo teda nahradené prázdnym reťazcom. Posledný parameter (1) udáva, ktorú inštanciu textu je potrebné nahradiť, ak je text prítomný viac ako raz. Napríklad, ak vykonáme nasledujúci príkaz
| 1 | Rozsah ("A1") = WorksheetFunction.Substitute (Rozsah ("A1"), "the", "an", 1) |
Bunka A1 bude vyzerať takto
Všimnite si toho, že prvý výskyt „the“ bol nahradený „an“, zatiaľ čo druhý „the“ nebol ovplyvnený. Ak by sme vynechali posledný parameter, obe „“ slová by boli nahradené
Vráťte sa do zoznamu všetkých funkcií v programe Excel
