Găsiți poziția ultimei apariții a unui caracter în Excel

În acest tutorial, veți afla cum să găsiți poziția ultimei apariții a unui caracter într-un șir în Excel.

Acum câteva zile, un coleg a venit cu această problemă.

Avea o listă de adrese URL așa cum se arată mai jos și trebuia să extragă toate caracterele după ultima bară înainte („/”).

De exemplu, de la https://example.com/archive/ianuarie a trebuit să extragă „ianuarie”.

Ar fi fost foarte ușor dacă a existat o singură bară directă în adresele URL.

Avea o listă uriașă de mii de adrese URL de lungime variabilă și un număr variabil de bare oblice.

În astfel de cazuri, trucul este să găsiți poziția ultimei apariții a barei oblice în URL.

În acest tutorial, vă voi arăta două moduri de a face acest lucru:

• Folosind o formulă Excel
• Utilizarea unei funcții personalizate (creată prin VBA)

Obținerea ultimei poziții a unui personaj folosind Formula Excel

Când aveți poziția ultimei apariții, puteți extrage pur și simplu orice din dreapta acesteia folosind funcția DREAPTA.

Iată formula care ar găsi ultima poziție a unei bare oblice înainte și ar extrage tot textul din dreapta acesteia.

= DREAPTA (A2, LEN (A2) -FIND ("@", SUBSTITUT (A2, "/", "@", LEN (A2) -LEN (SUBSTITUT (A2, "/", ""))), 1 ))) 

Cum funcționează această formulă?

Să descompunem formula și să explicăm cum funcționează fiecare parte a acesteia.

• SUBSTITUT (A2, ”/”,“”) - Această parte a formulei înlocuiește linia directă înainte cu un șir gol. Deci, de exemplu, în cazul în care doriți să găsiți apariția oricărui șir altul decât linia directă, utilizați-l aici.
• LEN (A2) -LEN (SUBSTITUT (A2, ”/”,“”)) - Această parte vă va spune câte bare oblice sunt în șir. Pur și simplu scade lungimea șirului fără bară directă din lungimea șirului cu bare oblice înainte.
• SUBSTITUT (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUT (A2, ”/”, ””))) - Această parte a formulei ar înlocui ultima bară înainte cu @. Ideea este de a face acel personaj unic. Puteți folosi orice personaj doriți. Asigurați-vă că este unic și nu apare deja în șir.
• Găsiți („@”, SUBSTITUT (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUT (A2, ”/”, ””))), 1) - Această parte a formulei vă va oferi poziția ultimei bare oblice.
• LEN (A2) -FIND („@”, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””))), 1) - Această parte a formulei ne-ar spune câte caractere există după ultima bară înainte.
• = DREAPTA (A2, LEN (A2) -FIND („@”, SUBSTITUT (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUT (A2, ”/”, ””))), 1 ))) - Acum, acest lucru ne-ar da pur și simplu șirul după ultima bară înainte.

Obținerea ultimei poziții a unui personaj folosind funcția personalizată (VBA)

Deși formula de mai sus este excelentă și funcționează ca un farmec, este un pic complicată.

Dacă vă simțiți confortabil folosind VBA, puteți utiliza o funcție personalizată (numită și Funcție definită de utilizator) creată prin VBA. Acest lucru poate simplifica formula și poate economisi timp dacă trebuie să faceți acest lucru des.

Să folosim același set de date de adrese URL (așa cum se arată mai jos):

Pentru acest caz, am creat o funcție numită LastPosition, care găsește ultima poziție a caracterului specificat (care este o bară directă în acest caz).

Iată formula care va face acest lucru:

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

Puteți vedea că acest lucru este mult mai simplu decât cel pe care l-am folosit mai sus.

Iată cum funcționează:

• LastPosition - care este funcția noastră personalizată - returnează poziția liniei oblice înainte. Această funcție ia două argumente - referința celulei care are adresa URL și caracterul a cărui poziție trebuie să o găsim.
• Funcția DREAPTA ne oferă apoi toate personajele după linia directă înainte.

Iată codul VBA care a creat această funcție:

Funcția LastPosition (rCell As Range, rChar As String) „Această funcție oferă ultima poziție a caracterului specificat” Acest cod a fost dezvoltat de 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

Pentru ca această funcție să funcționeze, trebuie să o plasați în Editorul VB. După ce ați terminat, puteți utiliza această funcție ca orice altă funcție Excel obișnuită.

Iată pașii pentru copierea și lipirea acestui cod în back-end-ul VB:

Iată pașii pentru plasarea acestui cod în Editorul VB:

1. Accesați fila Dezvoltator.
2. Faceți clic pe opțiunea Visual Basic. Aceasta va deschide editorul VB în backend.
3. În panoul Explorator de proiecte din Editorul VB, faceți clic dreapta pe orice obiect pentru registrul de lucru în care doriți să inserați codul. Dacă nu vedeți Project Explorer, accesați fila View și faceți clic pe Project Explorer.
4. Accesați Insert și faceți clic pe Module. Aceasta va insera un obiect modul pentru registrul dvs. de lucru.
5. Copiați și lipiți codul în fereastra modulului.

Acum formula va fi disponibilă în toate foile de lucru ale registrului de lucru.

Rețineți că trebuie să salvați registrul de lucru ca format .XLSM, deoarece are o macrocomandă. De asemenea, dacă doriți ca această formulă să fie disponibilă în toate registrele de lucru pe care le utilizați, puteți să o salvați personal în Macro Workbook sau să creați un supliment din acesta.

Vă pot plăcea, de asemenea, următoarele tutoriale Excel:

• Cum să obțineți numărul de cuvinte în Excel.
• Cum se folosește VLOOKUP cu mai multe criterii.
• Găsiți ultima apariție a unei valori de căutare a unei liste în Excel.
• Extrageți șirul de caractere în Excel.