✅ Nájdite pozíciu posledného výskytu znaku v programe Excel

Obsah

Získanie poslednej pozície znaku pomocou vzorca Excel
Získanie poslednej pozície znaku pomocou vlastnej funkcie (VBA)

V tomto tutoriále sa naučíte, ako nájsť pozíciu posledného výskytu znaku v reťazci v programe Excel.

Pred pár dňami prišiel s týmto problémom kolega.

Mal zoznam adries URL, ako je uvedené nižšie, a potreboval extrahovať všetky znaky za poslednou lomkou („/“).

Napríklad z https://example.com/archive/Január musel vyťažiť „január“.

Bolo by to skutočne jednoduché, keby v adresách URL bola iba jedna lomka.

To, čo mal, bol obrovský zoznam tisícov adries URL rôznej dĺžky a rôzneho počtu lomiek.

V takýchto prípadoch je trikom nájsť pozíciu posledného výskytu lomky v adrese URL.

V tomto návode vám ukážem dva spôsoby, ako to urobiť:

Použitie vzorca Excel
Použitie vlastnej funkcie (vytvorenej prostredníctvom VBA)

Získanie poslednej pozície znaku pomocou vzorca Excel

Keď máte pozíciu posledného výskytu, môžete jednoducho extrahovať čokoľvek napravo od neho pomocou funkcie DOPRAVA.

Tu je vzorec, ktorý by našiel poslednú pozíciu lomky a extrahoval by všetok text napravo od nej.

= VPRAVO (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "/", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "/", ""))), 1 )))

Ako tento vzorec funguje?

Poďme rozobrať vzorec a vysvetliť, ako každá jeho časť funguje.

NÁHRADA (A2, ”/”,“”) - Táto časť vzorca nahrádza lomku prázdnym reťazcom. Napríklad, v prípade, že chcete nájsť výskyt akéhokoľvek iného reťazca ako lomítka, použite ho tu.
LEN (A2) -LEN (NÁHRADA (A2, ”/”,“”)) - Táto časť by vám povedala, koľko lomiek vpred je v reťazci. Jednoducho skráti dĺžku reťazca bez lomítka od dĺžky reťazca s lomkami.
SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””))) - Táto časť vzorca by nahradila poslednú lomku za znak @. Cieľom je, aby bola táto postava jedinečná. Môžete použiť ľubovoľnú postavu. Uistite sa, že je jedinečný a že sa už v reťazci nezobrazuje.
FIND (“@”, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””)))), 1) - Táto časť vzorca by vám poskytla pozíciu posledného lomítka.
LEN (A2) -FIND („@“, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””))), 1) - Táto časť vzorca by nám povedala, koľko znakov je po poslednej lomke.
= VPRAVO (A2, LEN (A2) -FIND (“@”, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””))), 1 ))) - Teraz by nám to jednoducho poskytlo reťazec po poslednom lomisku.

Získanie poslednej pozície znaku pomocou vlastnej funkcie (VBA)

Aj keď je vyššie uvedený vzorec skvelý a funguje ako kúzlo, je trochu komplikovaný.

Ak vám vyhovuje používanie jazyka VBA, môžete použiť vlastnú funkciu (nazývanú tiež funkcia definovaná používateľom) vytvorenú prostredníctvom jazyka VBA. To môže zjednodušiť vzorec a ušetriť čas, ak to musíte robiť často.

Použime rovnakú množinu údajov adries URL (ako je uvedené nižšie):

Pre tento prípad som vytvoril funkciu s názvom LastPosition, ktorá nájde poslednú pozíciu zadaného znaku (čo je v tomto prípade lomka).

Tu je vzorec, ktorý to urobí:

= VPRAVO (A2, LEN (A2)-posledná poloha (A2, "/")+1)

Vidíte, že je to oveľa jednoduchšie ako ten, ktorý sme použili vyššie.

Funguje to takto:

LastPosition - čo je naša vlastná funkcia - vracia pozíciu lomky. Táto funkcia má dva argumenty - odkaz na bunku s adresou URL a znak, ktorého pozíciu musíme nájsť.
Funkcia VPRAVO nám potom dáva všetky znaky za lomkou.

Tu je kód VBA, ktorý vytvoril túto funkciu:

Funkcia LastPosition (rCell As Range, rChar As String) „Táto funkcia poskytuje poslednú pozíciu určeného znaku“ Tento kód bol vyvinutý spoločnosťou Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) Pre i = rPožičať 1 krok -1 Ak je stredný (rCell, i - 1, 1) = rChar Then LastPosition = i Ukončiť funkciu Koniec Ak ďalej i Ukončiť funkciu

Aby táto funkcia fungovala, musíte ju vložiť do editora VB. Po dokončení môžete túto funkciu používať ako každú inú bežnú funkciu programu Excel.

Tu sú kroky na skopírovanie a vloženie tohto kódu do back-endu VB:

Tu sú kroky na vloženie tohto kódu do editora VB:

Prejdite na kartu Vývojár.
Kliknite na možnosť Visual Basic. Tým sa otvorí editor VB v backende.
Na table Project Explorer v editore VB kliknite pravým tlačidlom myši na ľubovoľný objekt zošita, do ktorého chcete vložiť kód. Ak sa Project Explorer nezobrazuje, choďte na kartu Zobraziť a kliknite na Project Explorer.
Prejdite na položku Vložiť a kliknite na modul. Tým sa vloží objekt modulu do vášho zošita.
Skopírujte a prilepte kód do okna modulu.

Teraz by bol vzorec k dispozícii vo všetkých pracovných listoch zošita.

Všimnite si toho, že zošit musíte uložiť vo formáte .XLSM, pretože obsahuje makro. Ak chcete, aby bol tento vzorec k dispozícii vo všetkých zošitoch, ktoré používate, môžete si ho buď uložiť do osobného zošita makier, alebo z neho vytvoriť doplnok.

Tiež by sa vám mohli páčiť nasledujúce návody na Excel: