Poiščite položaj zadnjega pojavljanja znaka v Excelu

V tej vadnici se boste naučili, kako v Excelu poiskati položaj zadnjega pojavljanja znaka v nizu.

Pred nekaj dnevi je kolega naletel na to težavo.

Imel je seznam URL -jev, kot je prikazano spodaj, in moral je izvleči vse znake po zadnji poševnici naprej (»/«).

Tako na primer iz https://example.com/archive/Januar moral je izvleči 'januar'.

Bilo bi res enostavno, če bi bila v URL -jih samo ena poševnica naprej.

Imel je ogromen seznam tisoč na URL-jih različne dolžine in različno število poševnic naprej.

V takih primerih je trik najti pozicijo zadnjega pojavljanja poševnice naprej v URL -ju.

V tej vadnici vam bom pokazal dva načina za to:

• Z uporabo formule Excel
• Uporaba funkcije po meri (ustvarjena prek VBA)

Pridobivanje zadnje pozicije znaka z uporabo Excelove formule

Ko imate položaj zadnjega pojavljanja, lahko preprosto izvlečete karkoli na desni strani s funkcijo DESNO.

Tu je formula, ki bi našla zadnji položaj poševnice naprej in izvlekla vse besedilo desno od nje.

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

Kako deluje ta formula?

Razčlenimo formulo in razložimo, kako deluje vsak njen del.

• ZAMENIK (A2, ”/”,“”) - Ta del formule zamenja poševnico naprej s praznim nizom. Na primer, če želite ugotoviti pojav katerega koli niza, razen poševnice naprej, uporabite to tukaj.
• LEN (A2) -LEN (ZAMENJA (A2, ”/”,“”)) - Ta del bi povedal, koliko poševnic naprej v nizu. Enostavno odšteje dolžino niza brez poševnice naprej od dolžine niza s poševnicami naprej.
• ZAMENJALNIK (A2, ”/”, ”@”, LEN (A2) -LEN (ZAMENJA (A2, ”/”, ””))) - Ta del formule bi zadnjo poševnico naprej nadomestil z @. Ideja je, da bi bil ta lik edinstven. Uporabite lahko kateri koli znak, ki ga želite. Prepričajte se, da je edinstven in da se že ne pojavi v nizu.
• NAJDI (“@”, ZAMENJALNIK (A2, ”/”, ”@”, LEN (A2) -LEN (ZAMENJA (A2, ”/”, ””))), 1) - Ta del formule bi vam dal položaj zadnje poševnice naprej.
• LEN (A2) -FIND (“@”, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””))), 1) - Ta del formule bi nam povedal, koliko znakov je po zadnji poševnici naprej.
• = DESNO (A2, LEN (A2) -FIND (“@”, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””))), 1 )) - To bi nam preprosto dalo niz po zadnji poševnici naprej.

Pridobivanje zadnjega položaja znaka s funkcijo po meri (VBA)

Čeprav je zgornja formula odlična in deluje kot čar, je nekoliko zapleteno.

Če vam je všeč uporaba VBA, lahko uporabite funkcijo po meri (imenovano tudi uporabniško določena funkcija), ustvarjeno prek VBA. To lahko poenostavi formulo in prihrani čas, če to morate pogosto početi.

Uporabimo isti nabor podatkov URL (kot je prikazano spodaj):

V tem primeru sem ustvaril funkcijo LastPosition, ki najde zadnji položaj podanega znaka (kar je v tem primeru poševnica naprej).

Tukaj je formula, ki bo to naredila:

= DESNO (A2, LEN (A2) -Zadnja pozicija (A2, "/")+1)

Vidite, da je to veliko preprostejše od tistega, ki smo ga uporabili zgoraj.

Takole to deluje:

• LastPosition - to je naša funkcija po meri - vrne položaj poševnice naprej. Ta funkcija ima dva argumenta - sklic na celico z URL -jem in znak, katerega položaj moramo najti.
• Funkcija RIGHT nam nato posreduje vse znake po poševnici naprej.

Tu je koda VBA, ki je ustvarila to funkcijo:

Funkcija LastPosition (rCell As Range, rChar As String) 'Ta funkcija daje zadnji položaj navedenega znaka' To kodo je razvil Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) Za i = rLen Na 1 korak -1 Če je sredina (rCell, i - 1, 1) = rChar Potem LastPosition = i Izhod iz funkcije Konec Naprej Funkcija i Konec

Če želite, da ta funkcija deluje, jo morate postaviti v urejevalnik VB. Ko končate, lahko to funkcijo uporabite kot katero koli drugo običajno funkcijo Excel.

Tu so koraki za kopiranje in lepljenje te kode v zaledje VB:

Tukaj je nekaj korakov za umestitev te kode v urejevalnik VB:

1. Pojdite na zavihek Razvijalec.
2. Kliknite možnost Visual Basic. To bo odprlo urejevalnik VB v zaledju.
3. V podoknu Raziskovalec projektov v urejevalniku VB z desno tipko miške kliknite kateri koli predmet delovnega zvezka, v katerega želite vstaviti kodo. Če ne vidite Raziskovalca, pojdite na zavihek Pogled in kliknite Raziskovalec projektov.
4. Pojdite na Vstavi in ​​kliknite Modul. S tem boste v svoj delovni zvezek vstavili objekt modula.
5. Kodo kopirajte in prilepite v okno modula.

Zdaj bi bila formula na voljo v vseh delovnih listih delovnega zvezka.

Upoštevajte, da morate delovni zvezek shraniti kot obliko .XLSM, saj ima v njem makro. Če želite, da je ta formula na voljo v vseh delovnih zvezkih, ki jih uporabljate, jo lahko shranite v osebni delovni zvezek za makre ali iz nje ustvarite dodatek.

Morda vam bodo všeč tudi naslednji vaje za Excel:

• Kako pridobiti število besed v Excelu.
• Kako uporabljati VLOOKUP z več merili.
• Poiščite zadnji pojav iskane vrednosti na seznamu v Excelu.
• Izvlecite podniz v Excelu.