Az Excel egy sor TEXT funkciót tartalmaz, amelyek csodákra képesek. Ezekkel a funkciókkal mindenféle szöveges szelet és kocka műveletet végezhet.
A szöveges adatokkal dolgozó emberek egyik gyakori feladata az Excelben egy alstringet kibontani (azaz a szöveg psrt -jét lekérni egy cellából).
Sajnos az Excelben nincs olyan allánc funkció, amely ezt könnyen elvégezné. Ezt azonban továbbra is meg lehet tenni szöveges képletek, valamint néhány egyéb beépített Excel szolgáltatás segítségével.
Először nézzünk meg néhány szöveges funkciót, amelyeket ebben az oktatóanyagban fogunk használni.
Excel TEXT függvények
Az Excel számos szöveges funkcióval rendelkezik, amelyek nagyon megkönnyítenék az alstringek kinyerését az eredeti szövegből az Excelben. Íme az Excel szöveg funkciói, amelyeket ebben az oktatóanyagban fogunk használni:
- JOBB funkció: A megadott karakterek számát vonja ki a szöveges karakterlánc jobb oldaláról.
- LEFT funkció: Kivonja a megadott karakterszámot a szöveges karakterlánc bal oldaláról.
- MID függvény: Kivonja a megadott karakterek számát a szöveg kezdő pozíciójából.
- FIND függvény: megkeresi a megadott szöveg kezdő pozícióját a szöveges karakterláncban.
- LEN függvény: A karakterlánc karaktereit adja vissza.
Bontson ki egy alstringet az Excelben a függvények használatával
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:
Ezek néhány véletlenszerű (de szuperhősös) e-mail azonosító (kivéve az enyémet), és az alábbi példákban megmutatom, hogyan lehet kinyerni a felhasználónevet és a tartománynevet az Excel Szöveg funkcióival.
1. példa - Felhasználónevek kibontása az e -mail azonosítókból
A Szöveg funkciók használata során fontos azonosítani a mintát (ha van ilyen). Ez nagyon megkönnyíti a képlet elkészítését. A fenti esetben a minta a @ jel a felhasználónév és a tartománynév között, és ezt használjuk referenciaként a felhasználónevek lekéréséhez.
Íme a képlet a felhasználónév megszerzéséhez:
= BALRA (A2, KERESÉS ("@", A2) -1)
A fenti képlet a LEFT funkciót használja a felhasználónév kibontásához a @ jel pozíciójának azonosításával az azonosítóban. Ez a FIND függvénnyel történik, amely a @pozícióját adja vissza.
Például a [email protected] esetében a FIND („@”, A2) 11 -et adna vissza, ez a pozíciója a szöveges karakterláncban.
Most a LEFT funkciót használva 10 karaktert vonunk ki a karakterlánc bal oldaláról (eggyel kevesebbet, mint a LEFT függvény által visszaadott érték).
2. példa - A domain név kibontása az e -mail azonosítókból
Ugyanezt a logikát használhatjuk a fenti példában a tartománynév lekérésére. Kisebb különbség itt az, hogy ki kell vonnunk a karaktereket a szöveges karakterlánc jobb oldaláról.
Íme a képlet, amely ezt teszi:
= JOBB (A2, LEN (A2) -FIND ("@", A2))
A fenti képletben ugyanazt a logikát használjuk, de módosítsuk, hogy megbizonyosodjunk arról, hogy a megfelelő karakterláncot kapjuk.
Vegyük ismét a [email protected] példát. A FIND függvény a @ jel pozícióját adja vissza, ami ebben az esetben 11. Most ki kell vonnunk az összes karaktert a @után. Tehát azonosítjuk a karakterlánc teljes hosszát, és kivonjuk a karakterek számát a @-ig. Ez megadja számunkra a jobb oldalon lévő domain nevet lefedő karakterek számát.
Most egyszerűen használhatjuk a JOBB funkciót a tartománynév megszerzéséhez.
3. példa - A domain név kibontása az e -mail azonosítókból (.com nélkül)
Ahhoz, hogy egy szöveg karakterlánc közepéből kibonthasson egy alláncot, meg kell határoznia a jelölő pozícióját közvetlenül az allánc előtt és után.
Például az alábbi példában, ha a domain nevet a .com rész nélkül szeretné megkapni, a jelölő @ lesz (ami közvetlenül a domain név előtt van) és. (ami rögtön utána van).
Íme a képlet, amely csak a domain nevet vonja ki:
= MID (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
Az Excel MID funkció kibontja a megadott számú karaktert a megadott kezdőpozícióból. A fenti példában a FIND („@”, A2) +1 határozza meg a kiindulási pozíciót (amely közvetlenül a@után van), a FIND („.”, A2) -FIND („@”, A2) -1 pedig a karakterek száma a '@' és a '.‘
Frissítés: Az egyik olvasó, William19 megemlítette, hogy a fenti képlet nem működik, ha egy pont (.) Van az e -mail azonosítóban (például [email protected]). Tehát itt van a képlet az ilyen esetek kezelésére:
= MID (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Szöveg használata oszlopokba egy allánc kivonásához az Excelben
Az Excelben egy allánc kivonásához a funkciók előnye, hogy dinamikusak. Ha megváltoztatja az eredeti szöveget, a képlet automatikusan frissíti az eredményeket.
Ha erre nincs szüksége, akkor a Szöveg oszlopokba funkció használata gyors és egyszerű módja lehet a szöveg részjelzőkre bontásának a meghatározott jelzők alapján.
Ezt a következőképpen teheti meg:
- Válassza ki azokat a cellákat, ahol a szöveg található.
- Lépjen az Adatok -> Adateszközök -> Szöveg oszlopokba menüpontra.
- A Szöveg oszlopba varázsló 1. lépésében válassza a Lehatárolt elemet, majd nyomja meg a Tovább gombot.
- A 2. lépésben jelölje be az Egyéb opciót, és írja be a @ -ot a megfelelő mezőbe. Ez lesz a határolónk, amelyet az Excel használna a szöveg alláncokra osztására. Az adatok előnézetét alább láthatja. Kattintson a Tovább gombra.
- A 3. lépésben az Általános beállítás ebben az esetben jól működik. A számok/dátumok felosztása esetén azonban más formátumot is választhat. Alapértelmezés szerint a célcellában található az eredeti adat. Ha meg szeretné őrizni az eredeti adatokat, módosítsa ezt egy másik cellára.
- Kattintson a Befejezés gombra.
Ez azonnal két allánc -készletet ad a példában használt e -mail azonosítókhoz.
Ha tovább szeretné osztani a szöveget (például ossza fel a batman.com oldalt batmanra és comra), ismételje meg ugyanazt a folyamatot vele.
A FIND és a REPLACE használata a szöveg kibontásához az Excel cellából
A FIND és a REPLACE hatékony technika lehet, ha Excel szöveggel dolgozik. Az alábbi példákban megtanulhatja, hogyan kell a FIND és a REPLACE helyettesítő karakterekkel csodálatos dolgokat végrehajtani az Excelben.
Lásd még: Tudjon meg mindent az Excel helyettesítő karaktereiről.
Vegyük ugyanazokat a példákat az e -mail azonosítókra.
1. példa - Felhasználónevek kibontása az e -mail azonosítókból
Íme a lépések a felhasználónevek kinyeréséhez az e -mail azonosítókból a Keresés és csere funkció segítségével:
- Másolja és illessze be az eredeti adatokat. Mivel a Find and Replace működik és megváltoztatja azokat az adatokat, amelyekre alkalmazták, a legjobb, ha biztonsági másolatot készít az eredeti adatokról.
- Válassza ki az adatokat, és lépjen a Kezdőlap -> Szerkesztés -> Keresés és kiválasztás -> Csere menüpontra (vagy használja a Ctrl + H billentyűparancsot).
- A Keresés és csere párbeszédpanelen adja meg a következőket:
- Megtalálja, amit: @*
- Csere erre: (hagyja üresen)
- Kattintson az Összes cseréje gombra.
Ez azonnal eltávolítja az összes szöveget a @ előtt az e -mail azonosítókból. Az alább látható eredményt kapja:
Ez hogy működik ?? - A fenti példában a @ és a *kombinációt használtuk. A csillag (*) egy helyettesítő karakter, amely tetszőleges számú karaktert jelent. Ennélfogva a @* azt jelenti, hogy a szöveg @ karakterrel kezdődik, és tetszőleges számú karakter lehet utána. Például a [email protected] címben @* @batman.com lenne. Ha a @* karaktert üresre cseréljük, az eltávolítja a @utáni összes karaktert (beleértve a @karaktert is).
2. példa - A domain név kibontása az e -mail azonosítókból
Ugyanezzel a logikával módosíthatja a „Mit keressen” feltételeket a domain név megszerzéséhez.
Íme a lépések:
- Válassza ki az adatokat.
- Lépjen a Kezdőlapra -> Szerkesztés -> Keresés és kiválasztás -> Csere (vagy használja a Ctrl + H billentyűparancsot).
- A Keresés és csere párbeszédpanelen adja meg a következőket:
- Megtalálja, amit: *@
- Csere erre: (hagyja üresen)
- Kattintson az Összes cseréje gombra.
Ez azonnal eltávolítja az összes szöveget a @ előtt az e -mail azonosítókból. Az alább látható eredményt kapja:
