Ebben az oktatóanyagban megtudhatja, hogyan lehet megtalálni a karakter utolsó előfordulásának helyét egy karakterláncban az Excelben.
Néhány nappal ezelőtt egy kolléga felvetette ezt a problémát.
Az URL -címek listája az alábbiak szerint volt, és ki kellett vonnia az összes karaktert az utolsó elővágójel után („/”).
Például a https://example.com/archive/január ki kellett szednie a „januárt”.
Igazán egyszerű lett volna, ha csak egy elővágás van az URL -ekben.
Volt egy hatalmas ezres listája különböző hosszúságú URL-eken és változó számú előrevágással.
Ilyen esetekben a trükk az, hogy megkeressük az URL -ben az utolsó perjel utolsó előfordulásának pozícióját.
Ebben az oktatóanyagban ennek két módját mutatom be:
- Excel képlet használatával
- Egyéni funkció használata (VBA -n keresztül létrehozva)
A karakter utolsó pozíciójának megszerzése Excel képlet használatával
Ha megvan az utolsó előfordulás helyzete, akkor a jobb oldali funkció segítségével egyszerűen bármit kinyerhet a jobb oldalán.
Itt található a képlet, amely megkeresi az előrevágás utolsó pozícióját, és kivonja a tőle jobbra található szöveget.
= JOBB (A2, LEN (A2) -FIND ("@", CSERÉLŐ (A2, "/", "@", LEN (A2) -LEN (Helyettesítő (A2, "/", ""))), 1 ))
Hogyan működik ez a képlet?
Bontsuk le a képletet, és magyarázzuk el, hogyan működik minden része.
- Helyettesítő (A2, ”/”,“”) - A képletnek ez a része lecseréli a perjelet egy üres karakterláncra. Például, ha szeretné megtalálni a karakterlánc előfordulását az előrevitt perjelen kívül, használja ezt itt.
- LEN (A2) -LEN (Helyettesítő (A2, ”/”),“”)) - Ez a rész megmondja, hogy hány elővágójel van a karakterláncban. Egyszerűen kivonja a karakterlánc hosszát az előjel nélküli perjel nélkül a karakterlánc hosszából.
- CSERÉLŐ (A2, ”/”, ”@”, LEN (A2) -LEN (CSERÉLŐ (A2, ”/”, ””))) - A képlet ezen része helyettesítené az utolsó előretörést @-val. Az ötlet az, hogy egyedivé tegyük ezt a karaktert. Bármilyen karaktert használhat. Csak győződjön meg arról, hogy egyedi, és még nem jelenik meg a karakterláncban.
- KERESÉS (“@”, CSERÉL (A2, ”/”, ”@”, LEN (A2) -LEN (Helyettesítő (A2, ”/”, ””))), 1) - A képlet ezen része megadja az utolsó elővágó pozícióját.
- LEN (A2) -FIND („@”, CSERÉL (A2, ”/”, „@”, LEN (A2) -LEN (CSERÉL (A2, ”/”, ””))), 1) - A képlet ezen része megmondaná, hogy hány karakter van az utolsó előrevágás után.
- = JOBB (A2, LEN (A2) -FIND (“@”, CSERÉLŐ (A2, ”/”, ”@”, LEN (A2) -LEN (CSERÉL (A2, ”/”, ””))), 1 )) - Most ez egyszerűen megadná nekünk a húrt az utolsó előrevágás után.
A karakter utolsó pozíciójának megszerzése egyéni funkcióval (VBA)
Bár a fenti képlet nagyszerű és varázsként működik, kissé bonyolult.
Ha kényelmesen használja a VBA -t, használhat egy egyéni funkciót (más néven felhasználó által definiált funkciót), amelyet a VBA -n keresztül hoztak létre. Ez egyszerűsítheti a képletet, és időt takaríthat meg, ha ezt gyakran kell tennie.
Használjuk ugyanazt az URL -címek adatkészletét (az alábbiak szerint):
Ebben az esetben létrehoztam egy LastPosition nevű függvényt, amely megkeresi a megadott karakter utolsó pozícióját (ami ebben az esetben egy perjel).
Íme a képlet, amely ezt teszi:
= JOBB (A2, LEN (A2)-Utolsó pozíció (A2, "/")+1)
Láthatja, hogy ez sokkal egyszerűbb, mint a fenti.
Ez így működik:
- A LastPosition - ez a mi egyéni funkciónk - az előre -perjel pozícióját adja vissza. Ez a függvény két érvet tartalmaz - a cellahivatkozást, amely tartalmazza az URL -t, és azt a karaktert, amelynek pozícióját meg kell találnunk.
- A JOBB funkció ezután megadja az összes karaktert az előrevágás után.
Íme a VBA kód, amely létrehozta ezt a funkciót:
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 Kilépés a funkcióból Vége, ha a következő i vége
A funkció működéséhez el kell helyeznie a VB szerkesztőben. Ha elkészült, használhatja ezt a funkciót, mint bármely más hagyományos Excel funkciót.
Az alábbi lépésekkel másolja és illessze be ezt a kódot a VB háttérbe:
Az alábbi lépésekkel helyezze el ezt a kódot a VB szerkesztőben:
- Lépjen a Fejlesztő fülre.
- Kattintson a Visual Basic lehetőségre. Ez megnyitja a VB szerkesztőt a háttérben.
- A VB szerkesztő Project Explorer ablaktáblájában kattintson jobb gombbal a munkafüzet bármely olyan objektumára, amelybe be szeretné illeszteni a kódot. Ha nem látja a Project Explorer programot, lépjen a Nézet fülre, és kattintson a Project Explorer elemre.
- Lépjen a Beszúrás elemre, és kattintson a Modul elemre. Ezzel beszúr egy modulobjektumot a munkafüzetébe.
- Másolja ki és illessze be a kódot a modul ablakába.
Most a képlet a munkafüzet összes munkalapján elérhető lenne.
Ne feledje, hogy a munkafüzetet .XLSM formátumban kell mentenie, mivel makró van benne. Továbbá, ha azt szeretné, hogy ez a képlet elérhető legyen az összes használt munkafüzetben, akkor vagy elmentheti azt a Személyes makró munkafüzetbe, vagy létrehozhat belőle egy bővítményt.
A következő Excel oktatóanyagok is tetszhetnek:
- Hogyan lehet leolvasni a Word számot az Excelben.
- A VLOOKUP használata több kritériummal.
- Keresse meg a keresés utolsó előfordulását Értékeljen egy listát az Excelben.
- Bontsa ki az alstringet az Excelben.