Amikor Excelben dolgozik a szöveges adatokkal, előfordulhat, hogy el kell távolítania a szöveget egy adott karakter vagy szövegsor előtt vagy után.
Például, ha rendelkezik az emberek nevével és megnevezési adataival, akkor érdemes eltávolítani a megjelölést a vessző után, és csak a nevet megtartani (vagy fordítva, ahol megtartja a megnevezést, és eltávolítja a nevet).
Néha egyszerű képlettel vagy gyors kereséssel és cserével is elvégezhető, néha pedig összetettebb képletekre vagy megoldásokra van szükség.
Ebben az oktatóanyagban megmutatom, hogyan lehet eltávolítani a szöveget egy adott karakter előtt vagy után az Excelben (különböző példák segítségével).
Kezdjük tehát néhány egyszerű példával.
Szöveg eltávolítása karakter után a Keresés és csere segítségével
Ha gyorsan el szeretné távolítani az összes szöveget egy adott szöveges karakterlánc után (vagy egy szöveges karakterlánc előtt), akkor ezt a Find and Replace és a helyettesítő karakterek segítségével teheti meg.
Tegyük fel, hogy rendelkezik egy adathalmazzal az alábbiak szerint, és el szeretné távolítani a vessző karakter utáni megjelölést, és meg szeretné tartani a szöveget a vessző előtt.
Ennek lépései az alábbiakban találhatók:
- Másolja és illessze be az adatokat az A oszlopból a B oszlopba (ez az eredeti adatok megőrzése)
- A B oszlop celláit kiválasztva kattintson a Kezdőlap fülre
- A Szerkesztés csoportban kattintson a Keresés és kiválasztás lehetőségre
- A legördülő menüben megjelenő lehetőségek közül kattintson a Csere lehetőségre. Ezzel megnyílik a Keresés és csere párbeszédpanel
- A „Mit keres” mezőbe írja be ,* (azaz vessző, amelyet csillag jel követ)
- Hagyja üresen a „Csere” mezőt
- Kattintson az Összes cseréje gombra
A fenti lépések megkeresik a vesszőt az adathalmazban, és eltávolítják az összes szöveget a vessző után (beleértve a vesszőt is).
Mivel ez helyettesíti a kijelölt cellák szövegét, érdemes másolni a szöveget egy másik oszlopba, majd végrehajtani ezt a keresési és cserélési műveletet, vagy biztonsági másolatot készíteni az adatokról, hogy az eredeti adatok sértetlenek maradjanak.
Hogy működik ez?
* (csillag jel) egy helyettesítő karakter, amely tetszőleges számú karaktert képviselhet.
Amikor a vessző után használom (a „Find what” mezőben), majd rákattintok a Replace All gombra, akkor megtalálja az első vesszőt a cellában, és egyezésnek tekinti.
Ez azért van, mert a csillag (*) jel a vesszőt követő teljes szöveges karakterláncnak felel meg.
Tehát amikor megnyomja az összes cseréje gombot, akkor a vesszőt és az azt követő szöveget helyettesíti.
jegyzet: Ez a módszer jól működik, akkor csak egy vessző van minden cellában (mint a példánkban). Ha több vesszője van, ez a módszer mindig megtalálja az első vesszőt, majd mindent eltávolít utána. Tehát nem használhatja ezt a módszert, ha a második vessző után le akarja cserélni az összes szöveget, és az elsőt változatlan formában szeretné megtartani.Abban az esetben, ha az összes karaktert el szeretné távolítani a vessző előtt, módosítsa a keresendő mezőt úgy, hogy a csillag jel a vessző előtt van (*, a*helyett)
Szöveg eltávolítása képletek segítségével
Ha nagyobb ellenőrzésre van szüksége a szöveg megtalálásához és cseréjéhez egy adott karakter előtt vagy után, akkor jobb, ha az Excel beépített szövegképleteit használja.
Tegyük fel, hogy rendelkezik az alábbi adathalmazzal, ahová az összes szöveget el szeretné távolítani a vessző után.
Az alábbi képlet ennek elvégzésére szolgál:
= LEFT (A2, FIND (",", A2) -1)
A fenti képlet a FIND függvény segítségével határozza meg a vessző pozícióját a cellában.
Ezt a pozíciószámot a LEFT funkció használja a vessző előtti összes karakter kinyerésére. Mivel nem akarok vesszőt kapni az eredmény részeként, kivontam 1 -et a Find képlet eredményértékéből.
Ez egy egyszerű forgatókönyv volt.
Vegyünk egy kicsit bonyolultat.
Tegyük fel, hogy megvan az alábbi adathalmaz, ahol a második vessző után el akarom távolítani az összes szöveget.
Íme a képlet, amely ezt teszi:
= BALRA (A2, FIND ("!", CSERÉL (A2, ",", "!", 2))-1)
Mivel ez az adathalmaz több vesszőt tartalmaz, nem használhatom a FIND függvényt az első vessző pozíciójának lekérésére és a bal oldali rész kivonására.
Valahogy ki kell találnom a második vessző helyzetét, majd ki kell szednem mindent, ami a második vesszőtől balra található.
Ehhez a SUBSTITUTE funkciót használva a második vesszőt felkiáltójelre változtattam. Most ez egyedi karaktert ad nekem a cellában. Most már használhatom a felkiáltójel pozícióját, hogy kivonjak mindent a második vesszőtől balra.
A felkiáltójel ezen pozícióját használják a BALFÉL funkcióban, hogy kivonjanak mindent a második vessző előtt.
Eddig jó!
De mi van akkor, ha következetlen számú vessző van az adathalmazban?
Például az alábbi adatkészletben egyes cellák két vesszővel, néhány pedig három vesszővel rendelkeznek, és ki kell vonnom az összes szöveget az utolsó vessző előtt.
Ebben az esetben valahogy ki kell találnom a vessző utolsó előfordulásának helyzetét, majd mindent ki kell szednem tőle balra.
Az alábbiakban látható a képlet, amely ezt megteszi
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
A fenti képlet a LEN függvény segítségével határozza meg a cella szövegének teljes hosszát, valamint a szöveg vessző nélküli hosszát.
Amikor kivonom ezt a két értéket, ez megadja a cella összes vesszőjét.
Tehát az A2 cella esetén 3, az A4 cellánál 2.
Ezt az értéket a SUBSTITUTE képleten belül az utolsó vessző helyett felkiáltójellel helyettesítik. Ezután használhatja a bal függvényt, és kivonhat mindent, ami a felkiáltójel bal oldalán található (ez volt az utolsó vessző)
Amint a példákban látható, a szöveges képletek kombinációjának használatával sokféle helyzet kezelhető.
Továbbá, mivel az eredmény össze van kapcsolva az eredeti adatokkal, az eredeti adatok megváltoztatásakor az eredmény automatikusan frissül.
Szöveg eltávolítása a Flash Fill használatával
A Flash Fill egy olyan eszköz, amelyet az Excel 2013 -ban vezettek be, és azután minden verzióban elérhető.
Úgy működik, hogy azonosítja a mintákat, amikor manuálisan adja meg az adatokat, majd extrapolálja, és így megadja a teljes oszlop adatait.
Tehát ha el szeretné távolítani a szöveget egy adott karakter előtt vagy után, akkor csak meg kell mutatnia a flash tündérnek, hogyan nézne ki az eredmény (ha kétszer kézzel írja be), és a flash kitöltés automatikusan megérti a mintát, és megadja az eredmények.
Hadd mutassam meg ezt egy példával.
Az alábbiakban megvan az az adatkészlet, amelyből a vessző után el akarom távolítani az összes szöveget.
Íme a lépések a Flash Fill használatával:
- A B2 cellába, amely adataink szomszédos oszlopa, manuálisan írja be a „Jeffery Haggins” értéket (ez a várt eredmény)
- A B3 cellába írja be a „Tim Scott” szót (ez a második cella várható eredménye)
- Válassza ki a B2: B10 tartományt
- Kattintson a Kezdőlap fülre
- A Szerkesztés csoportban kattintson a Kitöltés legördülő menüre
- Kattintson a Flash Fill elemre
A fenti lépések az alábbi eredményt adják:
Használhatja a Flash Fill billentyűparancsot is Control + E miután kiválasztotta a cellákat az eredmény oszlopban (példánk B oszlopa)
A Flash Fill csodálatos eszköz, és a legtöbb esetben képes felismerni a mintát, és megadja a megfelelő eredményt. de bizonyos esetekben előfordulhat, hogy nem képes helyesen felismerni a mintát, és rossz eredményeket adhat.
Ezért mindenképpen ellenőrizze a Flash Fill eredményeit
És ahogy a flash kitöltés segítségével eltávolítottuk az összes szöveget egy adott karakter után, ugyanazokkal a lépésekkel távolíthatja el a szöveget egy adott karakter előtt. csak mutassa meg a flash kitöltést, hogy az eredménynek hogyan kell kinéznie az Internetemnek manuálisan a szomszédos oszlopban, és a többi elvégzi.
Szöveg eltávolítása VBA (egyéni funkció) használatával
A szöveg eltávolításának teljes koncepciója egy adott karakter előtt vagy után attól függ, hogy megtaláljuk -e az adott karaktert.
Mintha fentebb látnánk, az adott karakter utolsó előfordulásának jó megtalálása azt jelenti, hogy képleteket használunk.
Ha ezt gyakran meg kell tennie, akkor leegyszerűsítheti ezt a folyamatot, ha létrehoz egy egyéni függvényt a VBA használatával (úgynevezett felhasználó által definiált funkciók).
A létrehozás után újra és újra használhatja ezt a funkciót. Ezenkívül sokkal egyszerűbb és könnyebben használható (mivel a nehéz emelések nagy részét a hátsó részen található VBA kód végzi).
A VBA -kód alatt, amellyel egyéni funkciót hozhat létre az Excelben:
Funkció LastPosition (rCell As Range, rChar As String) 'Ez a funkció megadja a megadott karakter utolsó pozícióját' Ezt a kódot Sumit Bansal fejlesztette ki (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) I = rLen esetén 1 lépés -1 Ha közepes (rCell, i - 1, 1) = rChar, akkor LastPosition = i - 1 Kilépés a funkció vége, ha a következő i vége funkció
A VBA kódot el kell helyeznie a VB Editor rendszeres moduljában vagy a Személyes makró munkafüzetben. Ha ott van, használhatja a munkafüzet bármely más rendszeres munkalap -funkciójaként.
Ez a függvény 2 érvet tartalmaz:
- Az a cellahivatkozás, amelyhez egy adott karakter vagy szöveges karakterlánc utolsó előfordulását szeretné megtalálni
- Az a karakter vagy szöveg, amelynek pozícióját meg kell találnia
Tegyük fel, hogy most már rendelkezik az alábbi adatkészlettel, és el szeretné távolítani az összes szöveget az utolsó vessző után, és csak az utolsó vessző előtt.
Az alábbi képlet ezt teszi:
= BALRA (A2, Utolsó pozíció (A2, ",")-1)
A fenti képletben megadtam, hogy megtaláljam az utolsó vessző pozícióját. Ha más karakter vagy szöveg karakterláncának pozícióját szeretné megtalálni, akkor ezt használja a függvény második argumentumaként.
Mint látható, ez sokkal rövidebb és könnyebben használható, összehasonlítva az előző szakaszban használt hosszú szöveges formulával
Ha a VBA kódot egy munkafüzet egyik moduljába helyezi, akkor csak az adott munkafüzetben tudja használni ezt az egyéni funkciót. Ha ezt szeretné használni a rendszer összes munkafüzetében, akkor ezt a kódot másolja be és illessze be a Személyes makró munkafüzetbe.
Tehát ezek azok a levelek, amelyekkel gyorsan eltávolíthatja a szöveget az Excel adott karaktere előtt vagy után.
Ha egyszerű egyszeri dologról van szó, akkor ezt a keresés és csere segítségével teheti meg. mi van, ha kicsit bonyolultabb, akkor a beépített Excel képletek kombinációját kell használnia, vagy akár saját egyéni képletet kell létrehoznia a VBA használatával.
Remélem, hasznosnak találta ezt az oktatóanyagot.
