Trova la posizione dell'ultima occorrenza di un carattere in Excel

In questo tutorial imparerai come trovare la posizione dell'ultima occorrenza di un carattere in una stringa in Excel.

Qualche giorno fa un collega ha riscontrato questo problema.

Aveva un elenco di URL come mostrato di seguito e aveva bisogno di estrarre tutti i caratteri dopo l'ultima barra ("/").

Quindi, ad esempio, da https://example.com/archive/Gennaio ha dovuto estrarre "gennaio".

Sarebbe stato davvero facile se ci fosse stata una sola barra negli URL.

Quello che aveva era un enorme elenco di migliaia di URL di lunghezza variabile e un numero variabile di barre in avanti.

In questi casi, il trucco consiste nel trovare la posizione dell'ultima occorrenza della barra nell'URL.

In questo tutorial, ti mostrerò due modi per farlo:

• Usando una formula di Excel
• Utilizzo di una funzione personalizzata (creata tramite VBA)

Ottenere l'ultima posizione di un carattere utilizzando la formula di Excel

Quando hai la posizione dell'ultima occorrenza, puoi semplicemente estrarre qualsiasi cosa a destra di essa usando la funzione DESTRA.

Ecco la formula che troverebbe l'ultima posizione di una barra ed estrae tutto il testo alla sua destra.

=DESTRA(A2,LEN(A2)-Trova("@",SOSTITUTO(A2,"/","@",LEN(A2)-LEN(SOSTITUTO(A2,"/",""))),1 )) 

Come funziona questa formula?

Analizziamo la formula e spieghiamo come funziona ogni sua parte.

• SOSTITUTO(LA2,”/”,“”) - Questa parte della formula sostituisce la barra con una stringa vuota. Quindi, ad esempio, nel caso in cui desideri trovare l'occorrenza di qualsiasi stringa diversa dalla barra, usala qui.
• LEN(A2)-LEN(SOSTITUTO(A2,”/”,“”)) - Questa parte ti direbbe quante barre in avanti ci sono nella stringa. Sottrae semplicemente la lunghezza della stringa senza la barra dalla lunghezza della stringa con le barre.
• SOSTITUTO(A2,”/”,”@”,LEN(A2)-LEN(SOSTITUTO(A2,”/”,””))) - Questa parte della formula sostituirà l'ultima barra con @. L'idea è di rendere unico quel personaggio. Puoi usare qualsiasi personaggio tu voglia. Assicurati solo che sia univoco e non appaia già nella stringa.
• TROVA(“@”,SOSTITUTO(A2,”/”,”@”,LEN(A2)-LEN(SOSTITUTO(A2,”/”,””))),1) - Questa parte della formula ti darebbe la posizione dell'ultima barra in avanti.
• LEN(A2)-FIND(“@”,SOSTITUTO(A2,”/”,”@”,LEN(A2)-LEN(SOSTITUTO(A2,”/”,””))),1) - Questa parte della formula ci direbbe quanti caratteri ci sono dopo l'ultima barra in avanti.
• =DESTRA(A2,LEN(A2)-TROVA(“@”,SOSTITUISCI(A2,”/”,”@”,LEN(A2)-LEN(SOSTITUTO(A2,”/”,””))),1 )) - Ora questo ci darebbe semplicemente la stringa dopo l'ultima barra in avanti.

Ottenere l'ultima posizione di un personaggio utilizzando la funzione personalizzata (VBA)

Mentre la formula sopra è ottima e funziona come un incantesimo, è un po' complicata.

Se hai dimestichezza con VBA, puoi utilizzare una funzione personalizzata (chiamata anche Funzione definita dall'utente) creata tramite VBA. Questo può semplificare la formula e risparmiare tempo se devi farlo spesso.

Usiamo lo stesso set di dati di URL (come mostrato di seguito):

Per questo caso, ho creato una funzione chiamata LastPosition, che trova l'ultima posizione del carattere specificato (che è una barra in questo caso).

Ecco la formula che lo farà:

=DESTRA(A2,LEN(A2)-UltimaPosizione(A2,"/")+1)

Puoi vedere che questo è molto più semplice di quello che abbiamo usato sopra.

Ecco come funziona:

• LastPosition, che è la nostra funzione personalizzata, restituisce la posizione della barra. Questa funzione accetta due argomenti: il riferimento di cella che ha l'URL e il carattere di cui dobbiamo trovare la posizione.
• La funzione RIGHT ci fornisce quindi tutti i caratteri dopo la barra.

Ecco il codice VBA che ha creato questa funzione:

Function LastPosition(rCell As Range, rChar As String) 'Questa funzione fornisce l'ultima posizione del carattere specificato 'Questo codice è stato sviluppato da 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

Per far funzionare questa funzione, devi inserirla nell'editor VB. Una volta terminato, puoi utilizzare questa funzione come qualsiasi altra normale funzione di Excel.

Ecco i passaggi per copiare e incollare questo codice nel back-end VB:

Ecco i passaggi per inserire questo codice nell'editor VB:

1. Vai alla scheda Sviluppatore.
2. Fare clic sull'opzione Visual Basic. Questo aprirà l'editor VB nel backend.
3. Nel riquadro Project Explorer nell'editor VB, fare clic con il pulsante destro del mouse su qualsiasi oggetto per la cartella di lavoro in cui si desidera inserire il codice. Se non vedi Esplora progetti, vai alla scheda Visualizza e fai clic su Esplora progetti.
4. Vai su Inserisci e fai clic su Modulo. Questo inserirà un oggetto modulo per la tua cartella di lavoro.
5. Copia e incolla il codice nella finestra del modulo.

Ora la formula sarebbe disponibile in tutti i fogli di lavoro della cartella di lavoro.

Si noti che è necessario salvare la cartella di lavoro come formato .XLSM poiché contiene una macro. Inoltre, se desideri che questa formula sia disponibile in tutte le cartelle di lavoro che utilizzi, puoi salvarla nella cartella di lavoro macro personale o creare un componente aggiuntivo da essa.

Potrebbero piacerti anche i seguenti tutorial di Excel:

• Come ottenere il conteggio delle parole in Excel.
• Come utilizzare CERCA.VERT con più criteri.
• Trova l'ultima occorrenza di un valore di ricerca un elenco in Excel.
• Estrai sottostringa in Excel.