Helyettesítő Excel funkció - Szöveg keresése és cseréje

Példa munkafüzet letöltése

Töltse le a példa munkafüzetet

Ez a bemutató bemutatja, hogyan kell használni Excel SUBSTITUTE függvény az Excelben a szöveg megkereséséhez és cseréjéhez.

A CSERÉJ funkció áttekintése

A SUBSTITUTE funkció megkeresi és lecseréli a meglévő szöveget egy új szöveges karakterlánccal. Kis-nagybetű érzékeny.

A Helyettesítő Excel munkalap funkció használatához válasszon ki egy cellát, és írja be:

(Figyelje meg, hogyan jelennek meg a képletbemenetek)

SUBSTITUTE funkció Szintaxis és bemenetek:

1	= SUBSTITUTE (szöveg, régi_szöveg, új_szöveg, példányszám)

szöveg - Az eredeti szöveg.

régi_szöveg - A keresendő és cserélni kívánt szöveges karakterlánc.

new_test - A helyettesítő szöveg.

példányszám - Választható. A helyettesítendő példányszám. Ha üres, minden példány lecserélődik.

Mi az a helyettesítés?

Az Excel FELHASZNÁLÓ funkciója helyettesít egy karakterláncot egy szöveges karakterláncban egy másik megadott szöveges karakterlánccal.

Nézzük meg a következő példát, amely helyettesíti a „gyors barna” karakterláncot „lassú fehérrel”.

1	= CSERE (C2, C3, C4)

A SUBSTITUTE belenéz a C2 karakterláncba, megtalálja a „gyors barna” szót, és „lassú fehérre” cseréli.

A SUBSTITUTE használata

A CSERÉL funkció 3 kötelező argumentumot és 1 választható argumentumot tartalmaz:

• Szöveg: Az a szöveg, amelyet le szeretne cserélni
• Old_text: A cserélni kívánt szöveg
• Új_szöveg: Az a szöveg, amelyre a régi szöveget le szeretné cserélni
• Példányszám (nem kötelező): Megadja az Old_text előfordulását, amelyet le szeretne cserélni New_text -re. Alapértelmezés szerint a Old_text összes előfordulása le lesz cserélve, ha nincs megadva egy példány.

Fészkelő helyettesítő

Mi lenne, ha ugyanabból a karakterláncból két részt kellene kicserélnünk? Lássuk, hogyan kombinálnánk két helyettesítő funkciót.

1	= CSERÉLŐ (CSERÉL (C2, C3, C4), C5, C6)

Figyeld meg, hogyan használják a SUBSTITUTE (C2, C3, C4) eredményét a második CSERÉLŐ első argumentumaként. Ha megszakítjuk a képletet:

= A CSERÉLŐ (C2, C3, C4) a „The lassan fehér róka átugorja a lusta kutyát ”.

Ezután a PÓTLÓ („A lassú fehér róka átugrik a lusta kutyán”, C5, C6), amely így értékeli: „A lassú fehér oroszlán átugrik a lusta kutyán ”.

Különbség a CSERÉLÉS és a CSERÉLÉS között

A SUBSTITUTE hasonló funkciója a REPLACE. A legtöbb esetben ezeket fel lehet cserélni, azonban a következő hüvelykujjszabály érvényes.

A SUBSTITUTE funkciót akkor kell használni, ha a szövegsor cserélendő ismert, vagy egy egész szót vagy szavakat cserélnek le, mint a korábban használt rókapéldánk.

1	= CSERE (C2, C3, C4)

A REPLACE funkciót akkor kell használni, ha a a szöveges karakterek helyzete le kell cserélni a karakterláncban ismertek, vagy amikor egy szó vagy karakterlánc egy részét kicserélik. Például egy kötőjel eltávolítása a számsorból.

A REPLACE függvény beágyazható ugyanúgy, mint a SUBSTITUTE, azonban fontos megjegyezni, hogy a kezdő karakter relatív helyzete változhat a beágyazott képlet első lépésében cserélni kívánt karakterlánc hosszától függően.

Például a „gyors barna” helyett a „lassan fehér” helyett 11 karaktert 10 karakterrel helyettesít. Ezért beágyazva a második REPLACE kezdőkaraktere 1 karakterrel elmarad a kezdeti karakterpozícióktól.

PÓTLÁS Tippek

• A SUBSTITUTE az alábbi karakterláncokban használható:
• Ne feledje, hogy meg kell adnia az instance_num számot, ha a karakterláncban ismétlődő karakterkészletet kívánja helyettesíteni:

Vegye figyelembe, hogy a 3030 helytelen, mivel a „2020” „20” és „20” helyére „30” került.

Érdekelnek a További szöveg funkciók?

Tekintse meg további cikkeinket, ha többet szeretne megtudni a REPLACE -ről, vagy arról, hogy az Excel más szövegfunkciói, például a BAL, MID és JOBB, hogyan használhatók.

CSERE a Google Táblázatokban

A SUBSTITUTE funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:

További megjegyzések

A SUBSTITUTE funkció megkülönbözteti a kis- és nagybetűket !. A LOWER vagy UPPER funkciók segítségével a szöveges karakterláncokat konzisztenssé alakíthatja, mielőtt a SUBSTITUTE funkciót használná. Ezt követően, ha szükséges, a PROPER függvény segítségével nagybetűvel írhatja be minden szó első betűjét.

Alternatív megoldásként használhatja a SEARCH és a REPLACE függvényeket, hogy szimulálja a kis- és nagybetűk közötti különbséget. A SEARCH funkció segítségével keresse meg a szöveges karakterlánc kezdő pozícióját. A SEARCH funkció nem különbözteti meg a kis- és nagybetűket. Ezután használja a REPLACE funkciót a szöveg cseréjéhez. A SEARCH függvény eredményét kell használnia start_num bemenetként a REPLACE -ben. Meg kell határoznia a cserélendő karakterek számát is (szám_karakterek). Ezeket manuálisan is megszámolhatja, vagy a LEN funkcióval számolhatja a karakterek számát. Példa:

Csere - Távolítsa el a kötőjeleket

Gyakori kérdés, hogyan lehet eltávolítani a kötőjeleket a szövegből. A trükk az, hogy a kötőjelet semmivel helyettesítsük.

Ebben a példában tegyük fel, hogy az A1 cellában szöveg van kötőjelekkel. Íme, hogyan távolíthatja el őket

1. Üres segítő cella típusban = SUBSTITUTE (
2. Kattintson arra a cellára, amelyben karaktereket szeretne helyettesíteni, vagy A1 -re (ez automatikusan kitölti a képletet)
3. Írja be, (vessző)
4. Írja be a „-” billentyűt
5. Írja be, (vessző)
6. Írja be a „” billentyűt, és nyomja meg az Enter billentyűt
A segítő cellában a végeredménynek így kell kinéznie:
= CSERE (A1, ”-“, ””)

3 további helyettesítő példa:

A B3 cellát tekintve a „beteg” szót a „nagy” szóval szeretnénk helyettesíteni. A következő kifejezést használhatjuk:

1	= CSERE (B3, "beteg", "nagy", 1)

Ha a cserélni kívánt szöveg többször fordul elő, akkor meg kell adnunk az általunk értett példányt. A második példában két előfordulása van az „öreg” szónak. Tehát ha a másodpéldányt a „szürke” szóra akarjuk megváltoztatni, akkor a következőket kell használnunk:

1	= CSERE (B4, "régi", "szürke", 2)

Vegye figyelembe, hogy ha az előfordulás nincs megadva, akkor minden példány lecserélődik. Így:

1	= CSERE (B4, "régi", "szürke")

Megszabadulna a „régi” szó összes példányától, és helyébe a „szürke” szót lépne. Vegye figyelembe, hogy ha a régi szöveg nem található, akkor a karakterlánc változatlan. Tehát nézzük az utolsó példát:

1	= CSERE (B5, "fekete", "szürke", 1)

Azt jelenti, hogy megpróbáljuk helyettesíteni a „fekete” szót a „szürke” szóval. Mivel azonban „fekete” nem fordul elő, az eredeti karakterlánc változatlanul levitra maradt az Egyesült Államokban:

A .XLSX fájl letöltéséhez kattintson ide

PÓTLÁS Példák a VBA -ban

A VBA -ban a SUBSTITUTE funkciót is használhatja. Típus:
application.worksheetfunction.substitute (szöveg, régi_szöveg, új_szöveg, példányszám)
A függvény argumentumok (szöveg stb.) Esetén közvetlenül beírhatja őket a függvénybe, vagy definiálhat helyette változókat.

A következő kód végrehajtásakor

1	Tartomány ("A1") = WorksheetFunction.Substitute (tartomány ("A1"), "using", "", 1)

Az A1 cella tartalma a következő lesz:

Tehát a „használó” szót egy üres karakterlánc váltotta fel. Az utolsó paraméter (1) jelzi, hogy a szöveg melyik példányát kell kicserélni, ha a szöveg többször van jelen. Például, ha végrehajtjuk a következő állítást

1	Tartomány ("A1") = WorksheetFunction.Substitute (tartomány ("A1"), "the", "an", 1)

Az A1 cella így fog kinézni

Vegye figyelembe, hogy az „első” előfordulás helyébe „an” lépett, míg a második „az” nem volt hatással. Ha kihagyjuk az utolsó paramétert, akkor mindkét „the” szó le lett cserélve

Térjen vissza az Excel összes funkciójának listájához