I denne vejledning lærer du, hvordan du finder placeringen af den sidste forekomst af et tegn i en streng i Excel.
For et par dage siden kom en kollega med dette problem.
Han havde en liste over webadresser som vist nedenfor, og han havde brug for at udtrække alle tegnene efter den sidste skråstreg (“/”).
Så for eksempel fra https://example.com/archive/januar han måtte udtrække ‘januar’.
Det ville have været virkelig let, har der kun været et skråstreg i URL'erne.
Hvad han havde, var en enorm liste over tusinder af webadresser af forskellig længde og et varierende antal skråstreger.
I sådanne tilfælde er tricket at finde placeringen af den sidste forekomst af fremad skråstreg i URL'en.
I denne vejledning viser jeg dig to måder at gøre dette på:
- Ved hjælp af en Excel -formel
- Brug af en brugerdefineret funktion (oprettet via VBA)
Få den sidste position af et tegn ved hjælp af Excel Formula
Når du har positionen for den sidste forekomst, kan du simpelthen udtrække alt til højre ved hjælp af funktionen HØJRE.
Her er formlen, der ville finde den sidste position af et skråstreg og udtrække al teksten til højre for den.
= HØJRE (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "/", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "/", "")))), 1 ))
Hvordan fungerer denne formel?
Lad os nedbryde formlen og forklare, hvordan hver del af den fungerer.
- UDSKIFTNING (A2, ”/”,“”) - Denne del af formlen erstatter skråstregen med en tom streng. Så for eksempel, hvis du vil finde forekomsten af en anden streng end den skråstreg, skal du bruge den her.
- LEN (A2) -LEN (SUBSTITUTE (A2, ”/”,“”)) - Denne del ville fortælle dig, hvor mange skråstreger der er i strengen. Det trækker simpelthen længden af strengen uden skråstreget fra længden af strengen med skråstreger.
- SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””)))) - Denne del af formlen ville erstatte den sidste skråstreg med @. Ideen er at gøre den karakter unik. Du kan bruge enhver karakter, du ønsker. Bare sørg for, at den er unik og ikke allerede vises i strengen.
- FIND (“@”, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””))), 1) - Denne del af formlen vil give dig placeringen af det sidste skråstreg.
- LEN (A2) -FIND ("@", SUBSTITUTE (A2, "/", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "/", ""))), 1) - Denne del af formlen vil fortælle os, hvor mange tegn der er efter den sidste skråstreg.
- = HØJRE (A2, LEN (A2) -FIND (“@”, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””)))), 1 )) - Nu ville dette ganske enkelt give os strengen efter det sidste skråstreg.
Få den sidste position af et tegn ved hjælp af brugerdefineret funktion (VBA)
Selvom ovenstående formel er fantastisk og fungerer som en charme, er den lidt kompliceret.
Hvis du har det godt med at bruge VBA, kan du bruge en brugerdefineret funktion (også kaldet en brugerdefineret funktion), der er oprettet via VBA. Dette kan forenkle formlen og kan spare tid, hvis du skal gøre dette ofte.
Lad os bruge det samme datasæt med webadresser (som vist nedenfor):
I dette tilfælde har jeg oprettet en funktion kaldet LastPosition, der finder den sidste position af det angivne tegn (hvilket er et skråstreg i dette tilfælde).
Her er formlen, der vil gøre dette:
= HØJRE (A2, LEN (A2) -LastPosition (A2, "/")+1)
Du kan se, at dette er meget enklere end det, vi brugte ovenfor.
Sådan fungerer dette:
- LastPosition - som er vores brugerdefinerede funktion - returnerer placeringen af skråstregen. Denne funktion tager to argumenter - cellereferencen, der har URL'en og det tegn, hvis position vi skal finde.
- RIGHT -funktionen giver os derefter alle tegnene efter skråstreget.
Her er VBA -koden, der oprettede denne funktion:
Funktion LastPosition (rCell As Range, rChar As String) 'Denne funktion giver den sidste position af det angivne tegn' Denne kode er udviklet af Sumit Bansal (https://trumpexcel.com) Dim rLen som heltal rLen = Len (rCell) For i = rLen Til 1 Trin -1 Hvis Midt (rCell, i - 1, 1) = rChar Herefter LastPosition = i Afslut funktion Afslut Hvis næste i Afslut funktion
For at få denne funktion til at fungere, skal du placere den i VB Editor. Når du er færdig, kan du bruge denne funktion som enhver anden almindelig Excel -funktion.
Her er trinene til at kopiere og indsætte denne kode i VB-backend:
Her er trinene til at placere denne kode i VB Editor:
- Gå til fanen Udvikler.
- Klik på indstillingen Visual Basic. Dette åbner VB -editoren i backend.
- Højreklik på et objekt til projektmappen, hvor du vil indsætte koden, i ruden Project Explorer i VB Editor. Hvis du ikke kan se Project Explorer, skal du gå til fanen View og klikke på Project Explorer.
- Gå til Indsæt og klik på Modul. Dette vil indsætte et modulobjekt til din projektmappe.
- Kopier og indsæt koden i modulvinduet.
Nu ville formlen være tilgængelig i alle regneark i projektmappen.
Bemærk, at du skal gemme projektmappen som .XLSM -format, da den har en makro i den. Hvis du vil have denne formel til at være tilgængelig i alle de projektmapper, du bruger, kan du enten gemme den personlige makro-projektmappe eller oprette et tilføjelsesprogram fra den.
Du kan også lide følgende Excel -selvstudier:
- Sådan får du ordtællingen i Excel.
- Sådan bruges VLOOKUP med flere kriterier.
- Find den sidste forekomst af en opslagsværdi en liste i Excel.
- Udtræk Substring i Excel.
