U ovom ćete vodiču naučiti kako pronaći položaj posljednjeg pojavljivanja znaka u nizu u Excelu.
Prije nekoliko dana kolega se dosjetio ovog problema.
Imao je popis URL -ova kako je prikazano u nastavku, te je morao izdvojiti sve znakove nakon posljednje kose crte ("/").
Tako, na primjer, s https://example.com/archive/Siječnja morao je izvući 'siječanj'.
Bilo bi jako jednostavno da je u URL -ovima postojala samo jedna kosa crta.
Imao je ogroman popis tisuća URL-ova različite duljine i različit broj kosih crta.
U takvim slučajevima, trik je pronaći poziciju posljednjeg pojavljivanja kose crte u URL -u.
U ovom vodiču pokazat ću vam dva načina kako to učiniti:
- Korištenje Excel formule
- Korištenje prilagođene funkcije (stvoreno putem VBA)
Dobivanje posljednje pozicije lika pomoću Excel formule
Kada imate poziciju posljednjeg pojavljivanja, jednostavno možete izvući bilo što s desne strane pomoću funkcije DESNO.
Ovdje je formula koja bi pronašla posljednju poziciju kose crte i izdvojila sav tekst desno od nje.
= DESNO (A2, LEN (A2) -FIND ("@", ZAMJENA (A2, "/", "@", LEN (A2) -LEN (ZAMJENA (A2, "/", ""))), 1 ))
Kako funkcionira ova formula?
Razložimo formulu i objasnimo kako svaki njezin dio funkcionira.
- ZAMJENA (A2, ”/”,“”) - Ovaj dio formule zamjenjuje kosu crtu praznim nizom. Na primjer, u slučaju da želite pronaći bilo koji niz osim kose crte, upotrijebite to ovdje.
- LEN (A2) -LEN (ZAMJENA (A2, ”/”,“”)) - Ovaj dio bi vam rekao koliko kosih crta ima u nizu. Jednostavno oduzima duljinu niza bez kose crte unaprijed od duljine niza s kosim crtama.
- ZAMJENA (A2, ”/”, ”@”, LEN (A2) -LEN (ZAMJENA (A2, ”/”, ””))) - Ovaj dio formule zamijenio bi zadnju kosu crtu s @. Ideja je učiniti taj lik jedinstvenim. Možete koristiti bilo koji znak koji želite. Samo provjerite je li jedinstven i već se ne pojavljuje u nizu.
- NAĐI (“@”, ZAMJENA (A2, ”/”, ”@”, LEN (A2) -LEN (ZAMJENA (A2, ”/”, ””))), 1) - Ovaj dio formule dao bi vam poziciju posljednje kose crte.
- LEN (A2) -FIND (“@”, ZAMJENA (A2, ”/”, ”@”, LEN (A2) -LEN (ZAMJENA (A2, ”/”, ””))), 1) - Ovaj dio formule bi nam rekao koliko ima znakova nakon posljednje kose crte.
- = DESNO (A2, LEN (A2) -FIND (“@”, ZAMJENA (A2, ”/”, ”@”, LEN (A2) -LEN (ZAMJENA (A2, ”/”, ””))) 1 )) - Ovo bi nam jednostavno dalo niz nakon posljednje kose crte.
Dobivanje posljednje pozicije lika pomoću prilagođene funkcije (VBA)
Iako je gornja formula sjajna i djeluje poput šarma, pomalo je komplicirana.
Ako vam je ugodno koristiti VBA, možete koristiti prilagođenu funkciju (koja se naziva i Korisnički definirana funkcija) stvorenu putem VBA. To može pojednostaviti formulu i uštedjeti vrijeme ako to morate često raditi.
Upotrijebimo isti skup podataka URL -ova (kao što je prikazano u nastavku):
Za ovaj slučaj stvorio sam funkciju pod nazivom LastPosition koja pronalazi posljednju poziciju navedenog znaka (što je u ovom slučaju kosa crta).
Evo formule koja će to učiniti:
= DESNO (A2, LEN (A2) -Posljednja pozicija (A2, "/")+1)
Možete vidjeti da je ovo mnogo jednostavnije od onog koje smo koristili gore.
Evo kako to funkcionira:
- LastPosition - što je naša prilagođena funkcija - vraća položaj kose crte. Ova funkcija ima dva argumenta - referencu ćelije koja ima URL i znak čiji položaj moramo pronaći.
- Funkcija RIGHT tada nam daje sve znakove nakon kose crte.
Evo VBA koda koji je stvorio ovu funkciju:
Funkcija LastPosition (rCell As Range, rChar As String) 'Ova funkcija daje posljednju poziciju navedenog znaka' Ovaj kôd je razvio Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) Za i = rLen do 1 korak -1 Ako je sredina (rCell, i - 1, 1) = rChar Tada je LastPosition = i Izađi iz funkcije Kraj ako je sljedeća i Kraj funkcije
Da bi ova funkcija radila, morate je postaviti u VB Editor. Nakon što završite, možete koristiti ovu funkciju kao i bilo koju drugu redovitu Excel funkciju.
Evo koraka za kopiranje i lijepljenje ovog koda u VB pozadinu:
Evo koraka za postavljanje ovog koda u VB Editor:
- Idite na karticu Developer.
- Kliknite na opciju Visual Basic. Ovo će otvoriti VB editor u pozadini.
- U oknu Project Explorer u VB Editoru desnom tipkom miša kliknite bilo koji objekt radne knjige u koji želite umetnuti kôd. Ako ne vidite Project Explorer, idite na karticu View i kliknite na Project Explorer.
- Idite na Umetni i kliknite na Modul. Ovo će umetnuti objekt modula u vašu radnu knjigu.
- Kopirajte i zalijepite kôd u prozor modula.
Sada bi formula bila dostupna na svim radnim listovima radne knjige.
Imajte na umu da morate spremiti radnu knjigu kao .XLSM format jer u njoj ima makronaredbu. Također, ako želite da ova formula bude dostupna u svim radnim knjigama koje koristite, možete je spremiti kao osobnu radnu knjigu makronaredbi ili iz nje izraditi dodatak.
Možda će vam se svidjeti i sljedeći vodiči za Excel:
- Kako postići broj riječi u Excelu.
- Kako koristiti VLOOKUP s više kriterija.
- Pronađite posljednju pojavu vrijednosti za pretraživanje na popisu u Excelu.
- Izdvojite podniz u Excelu.