Najděte polohu posledního výskytu znaku v aplikaci Excel

V tomto kurzu se naučíte, jak najít pozici posledního výskytu znaku v řetězci v Excelu.

Před pár dny přišel s tímto problémem kolega.

Měl seznam adres URL, jak je uvedeno níže, a potřeboval extrahovat všechny znaky za posledním lomítkem („/“).

Například z https://example.com/archiv/leden musel vytáhnout ‚leden‘.

Bylo by opravdu snadné, kdyby v adresách URL bylo pouze jedno lomítko.

Měl obrovský seznam tisíců URL různých délek a různého počtu lomítek.

V takových případech jde o to, najít polohu posledního výskytu lomítka v URL.

V tomto tutoriálu vám ukážu dva způsoby, jak toho dosáhnout:

• Pomocí vzorce aplikace Excel
• Pomocí vlastní funkce (vytvořené prostřednictvím VBA)

Získání poslední pozice znaku pomocí vzorce Excel

Když máte pozici posledního výskytu, můžete jednoduše extrahovat cokoli napravo od něj pomocí funkce VPRAVO.

Zde je vzorec, který by našel poslední pozici lomítka a extrahoval veškerý text napravo od něj.

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

Jak tento vzorec funguje?

Pojďme rozebrat vzorec a vysvětlit, jak každá jeho část funguje.

• NÁHRADA (A2, ”/”,“”) - Tato část vzorce nahrazuje lomítko prázdným řetězcem. Například v případě, že chcete zjistit výskyt jiného řetězce než lomítka, použijte to zde.
• LEN (A2) -LEN (NÁHRADA (A2, ”/”,“”)) - Tato část vám řekne, kolik lomítek je v řetězci. Jednoduše odečte délku řetězce bez lomítka od délky řetězce s lomítky vpřed.
• SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””))) - Tato část vzorce nahradí poslední lomítko znakem @. Cílem je učinit tuto postavu jedinečnou. Můžete použít libovolnou postavu, kterou chcete. Jen se ujistěte, že je jedinečný a že se již v řetězci nezobrazuje.
• FIND (“@”, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””)))), 1) - Tato část vzorce by vám poskytla pozici posledního lomítka.
• LEN (A2) -FIND (“@”, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””))), 1) - Tato část vzorce by nám řekla, kolik znaků je po posledním lomítku.
• = RIGHT (A2, LEN (A2) -FIND („@“, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””))), 1 ))) - Teď by nám to jednoduše dalo řetězec po posledním lomítku.

Získání poslední pozice postavy pomocí vlastní funkce (VBA)

Přestože je výše uvedený vzorec skvělý a funguje jako kouzlo, je trochu komplikovaný.

Pokud vám vyhovuje používání jazyka VBA, můžete použít vlastní funkci (nazývanou také funkce definovaná uživatelem) vytvořenou prostřednictvím jazyka VBA. To může zjednodušit vzorec a ušetřit čas, pokud to musíte dělat často.

Použijeme stejnou datovou sadu adres URL (jak je uvedeno níže):

Pro tento případ jsem vytvořil funkci nazvanou LastPosition, která najde poslední pozici zadaného znaku (což je v tomto případě lomítko).

Zde je vzorec, který to provede:

= RIGHT (A2, LEN (A2)-LastPosition (A2, "/")+1)

Vidíte, že je to mnohem jednodušší než ten, který jsme použili výše.

Funguje to takto:

• LastPosition - což je naše vlastní funkce - vrací pozici lomítka. Tato funkce má dva argumenty - odkaz na buňku s adresou URL a znak, jehož pozici musíme najít.
• Funkce VPRAVO nám poté dává všechny znaky za lomítkem.

Zde je kód VBA, který vytvořil tuto funkci:

Funkce LastPosition (rCell As Range, rChar As String) 'Tato funkce udává poslední pozici zadaného znaku' Tento kód byl vyvinut společností Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) For i = rLen To 1 Step -1 If Mid (rCell, i - 1, 1) = rChar Then LastPosition = i Exit Function End If Next i End Function

Aby tato funkce fungovala, musíte ji umístit do editoru VB. Jakmile budete hotovi, můžete tuto funkci používat jako jakoukoli jinou běžnou funkci aplikace Excel.

Zde jsou kroky ke zkopírování a vložení tohoto kódu do back-endu VB:

Zde jsou kroky k umístění tohoto kódu do editoru VB:

1. Přejděte na kartu Vývojář.
2. Klikněte na možnost Visual Basic. Tím se otevře editor VB v backendu.
3. V podokně Průzkumník projektů v editoru VB klikněte pravým tlačítkem na libovolný objekt sešitu, do kterého chcete vložit kód. Pokud Průzkumníka nevidíte, přejděte na kartu Zobrazit a klikněte na Průzkumník projektů.
4. Přejděte na Vložit a klikněte na Modul. Tím se vloží objekt modulu pro váš sešit.
5. Zkopírujte a vložte kód do okna modulu.

Nyní by byl vzorec k dispozici ve všech listech sešitu.

Všimněte si, že sešit musíte uložit ve formátu .XLSM, protože obsahuje makro. Pokud chcete, aby byl tento vzorec k dispozici ve všech sešitech, které používáte, můžete jej buď uložit do osobního sešitu maker, nebo z něj vytvořit doplněk.

Také by se vám mohly líbit následující výukové programy pro Excel:

• Jak získat počet slov v aplikaci Excel.
• Jak používat VLOOKUP s více kritérii.
• Najděte poslední výskyt vyhledávací hodnoty v seznamu v aplikaci Excel.
• Extrahujte podřetězec v aplikaci Excel.