Esempi di utilizzo della funzione STRINGA.ESTRAI

esempioUno dei primi articoli che ho scritto riguardava la funzione STRINGA.ESTRAI di Excel, quello che, all’epoca, non immaginavo è che sarebbe diventato l’articolo più letto e commentato del blog. I commenti sono davvero tanti e spesso ci si perde tra di essi finendo per non trovare soluzioni che poi risolverebbero il problema che vi affligge.

L’uso di formule Excel basate su STRINGA.ESTRAI richiede spesso l’interazione con altre funzioni che non sono presenti nell’articolo precedente, ho deciso, quindi, di creare questo articolo con lo scopo di racchiudere un po’ tutti i casi che si sono presentati nei commenti in modo che vi sia più semplice trovare la soluzione che più si adatta al problema che si presenta.

Tenete a mente i parametri della funzione STRINGA.ESTRAI

=STRINGA.ESTRA(stringa_da_cui_estrarre;inizio;lunghezza)

La magia della funzione CODICE.CARATT()

Prima di avventurarci in questa lista di formule vorrei spiegare il perché spesso uso questa funzione. CODICE.CARATT() permette di inserire un carattere al posto di un altro basandosi sul suo codice ASCII (un numero), visto che a volte diventa difficile identificare un particolare carattere (esempio uno spazio) da cui far iniziare o in cui terminare l’estrazione, la prima cosa che faccio è usare la funzione SOSTITUISCI() per sostituire il dato carattere con CODICE.CARATT(254) che viene interpretato dal simbolo þ che difficilmente è contenuto nel testo di partenza.

Quindi, ogni volta che vedrete questa funzione nelle formule, saprete che viene usata per questo scopo.

Estrarre un stringa dopo l’ultimo spazio

La richiesta più diffusa è quella di estrarre una parte di stringa partendo dall’ultimo spazio (o trattino o qualqunque altro simbolo) che si ripete diverse volte nel testo.

La prima cosa da fare, quindi, è sapere quanti di questi simboli (nel nostro caso lo spazio) sono contenuti nel testo per identificare l’ultimo. Questo lo possiamo fare usando una semplice formula basata sulla funzione LUNGHEZZA()

Prendiamo ad esempio un testo nella cella A1 che sia “ciao a tutti i visitatori di TuttoExcel” e di voler estrarre la parola TuttoExcel.

Indentifichiamo la posizione dell’ultimo spazio cominciando con il trovare quanti spazi ci sono

=LUNGHEZZA(A1)-LUNGHEZZA(SOSTITUISCI(A1;" ";""))

Quindi la formula ci dirà quanto è lunga la stringa ( 39 caratteri ) e sottrarrà la lunghezza della stessa stringa in cui gli spazi vengono sostituiti da un carattere vuoto ( 33 caratteri ), il risultato (6) sarà il numero di caratteri “spazio” presenti nella frase ed anche il numero dell’ultimo di essi. Come usare questa informazione? Semplice, sostituiamo il sesto spazio con CODICE.CARATT(254)

=SOSTITUISCI(A1;" ";CODICE.CARATT(254);LUNGHEZZA(A1)-LUNGHEZZA(SOSTITUISCI(A1;" ";"")))

La formula, tradotta in linguaggio umano, dice sostituisci il sesto spazio del testo di A1 con il codice þ. (Vedi articolo sulla funzione SOSTITUISCI per maggiori informazioni).

Se mettiamo la formula così com’è in una cella il risultato sarà “ciao a tutti i visitatori diþTuttoExcel

Il passaggio successivo è trovare la posizione da cui iniziare l’estrazione, possiamo farlo con la funzione TROVA o la funzione RICERCA, è indifferente, la posizione è rappresentata dal simbolo þ + 1. Le funzioni appena menzionate restituiscono la posizione del carattere cercato al quale sommeremo 1.

=RICERCA(CODICE.CARATT(254);SOSTITUISCI(A1;" ";CODICE.CARATT(254);LUNGHEZZA(A1)-LUNGHEZZA(SOSTITUISCI(A1;" ";""))))+1

Il risultato della formula sarà 30. Abbiamo quasi finito, il prossimo passaggio è l’estrazione vera e propria. Come detto sopra sono necessari 3 parametri per la funzione STRINGA.ESTRAI, il primo è la cella da cui estrarre, A1, il secondo lo abbiamo ricavato con la formula che abbiamo appena scritto, il terzo è la lunghezza di caratteri da estrarre. Visto che stiamo estraendo l’ultima parola abbiamo bisogno di un numero pari o superiore al numero di caratteri che compongono la parola, la lunghezza di tutta la cella è perfetta quindi.

Scriviamo la formula finale:

=STRINGA.ESTRAI(A1;RICERCA(CODICE.CARATT(254);SOSTITUISCI(A1;" ";CODICE.CARATT(254);LUNGHEZZA(A1)-LUNGHEZZA(SOSTITUISCI(A1;" ";""))))+1;LUNGHEZZA(A1))

Ed il gioco è fatto 🙂

Estrarre una stringa dall’inizio fino ad un dato simbolo

Ora che avete seguito la procedura per estrarre l’ultima parola quella seguente vi sembrerà un gioco da ragazzi. Poniamo di voler estrarre da “Esempi di utilizzo della funzione STRINGA.ESTRAI – Tutto Excel” solo il titolo dell’articolo eliminando il nome del sito. Come sempre il primo parametro è facile, il riferimento della cella. Il secondo parametro, in questo caso, è facilissimo, 1, perché vogliamo partire dall’inizio. E l’ultimo parametro, ovvero la lunghezza? Dobbiamo calcolarlo!

Se la stringa di partenza è come quella illustrata, è facile, basta trovare la posizione del trattino con TROVA o RICERCA e sottrarre 1 (altrimenti viene incluso il trattino), la formula sarà quindi

=STRINGA.ESTRAI(A1;1;RICERCA("-";A1)-1)

Il problema si pone quando i trattini (o gli spazi o qualunque altro simbolo) sono più di 1 e a noi serve trovare l’ultimo, per farlo usiamo i passaggi dell’esempio precedente ma, questa volta, li mettiamo come ultimo parametro, vi metto direttamente la formula perché ora dovreste poterla capire

=STRINGA.ESTRAI(A1;1;RICERCA(CODICE.CARATT(254);SOSTITUISCI(A1;"-";CODICE.CARATT(254);LUNGHEZZA(A1)-LUNGHEZZA(SOSTITUISCI(A1;"-";""))))-1)

Estrarre una stringa compresa tra due simboli

Un altro caso che mi è capitato spesso di vedere in giro è quello rappresentato dalla necessità di estrarre una stringa compresa tra due simboli, ad esempio estrarre la parola “funzioni” dalla stringa “esempio_funzioni_excel”.

In questo caso si può semplicemente usare un STRINGA.ESTRAI nidificato in congiunzione con RICERCA e LUNGHEZZA

Per prima cosa estraiamo la stringa dalla posizione del primo underscore “_” (trattino basso per i non anglofoni) fino alla fine della riga

=STRINGA.ESTRAI(A1;RICERCA("_";A1)+1;LUNGHEZZA(A1))

Il risultato sarà “funzioni_excel”, da questa stringa dobbiamo estrarre fino al primo underscore, quindi mettendo insieme le formule

=STRINGA.ESTRAI(STRINGA.ESTRAI(A1;TROVA("_";A1)+1;LUNGHEZZA(A1));1;RICERCA("_";A1)-1)

Piccole ma importanti annotazioni

Prima di chiudere questo articolo con le richieste più frequenti vorrei soffermarmi su alcuni punti comuni a tutti questi esempi.

Spesso nelle formule uso +1 o -1 per spostarmi di 1 carattere ogni volta che uso la funzione RICERCA, questo vale se il carattere è uno solo. Se doveste usare le funzioni per cercare una parola ricordate che RICERCA restituisce la posizione del primo carattere della parola, quindi oltre a +1 dovrete aggiungere la lunghezza della parola (questo non vale per il -1).

Altra cosa che vorrei farvi notare è che se i caratteri ricercati (ad esempio l’underscore) non sono presenti nella stringa vi verrà restituito un errore, questo può e deve essere gestito con SE.ERRORE o comunque con la gestione degli errori.

Ultimo punto, se estraete un numero da una stringa ricordate che esso sarà considerato come una stringa, quindi se il vostro scopo è ottenere un numero da usare all’interno di calcoli mettete un “–” subito dopo il simbolo “=” di inizio formula come nel seguente esempio

=--STRINGA.ESTRAI(STRINGA.ESTRAI(A1;TROVA("_";A1)+1;LUNGHEZZA(A1));1;RICERCA("_";A1)-1)

La conversione in numero sarà assicurata.

Come sempre chiedete pure nei commenti, le vostre richieste potrebbero finire in questo articolo 🙂

A presto


'. '
'; $customMessages[] = "Aiutami a far conoscere questo blog ad altre persone perché possa aiutare anche loro, condividi questo articolo su Facebook, Twitter, Google+ o Linkedin, non ti costa nulla e mi aiuterà a diffonderlo dandomi l'ispirazione per scrivere altri articoli."; $customMessages[] = 'Hai trovato questo blog utile o interessante? Lascia una tua opinione sul guestbook per farmi sapere cosa ne pensi e come possa migliorarlo.'; $customMessages[] = 'Sai che puoi iscriverti anche alla newsletter e ricevere le novità direttamente nella tua email? Iscriviti adesso, riceverai solo gli aggiornamenti e niente SPAM. Il tuo indirizzo, inoltre, non sarà condiviso con nessuno.'; $id = rand(0, count($customMessages) -1); echo '
'. '
MESSAGGIO DALL\'AUTORE
'. $customMessages[$id]. '

'; ?>

Gianfranco (Admin)

Appassionato di informatica, fotografia e di tecnologia in generale. Esperto nell'uso di Excel e con tanta voglia di condividere le sue conoscenza con il mondo, sempre pronto a dare una mano (ma non più di due volte, altrimenti rimango senza).

Potrebbero interessarti anche...

64 risposte

  1. Marco Tonini ha detto:

    buon giorno,

    OGGETTO: Estrarre una stringa compresa tra due simboli

    io ho l’esigenza di estrapolare parte di testo di una cella che è compreso tra due “-”
    tutte le parti di testo hanno lunghezza variabile.
    ho applicato la formula e per quanto riguarda la prima parte del testo lo cancella correttamente, anche con lunghezza differente.
    mentre non mi restituisce corretta la parte centrale, ovvero o mi toglie parte di testo quando è lungo o mi lascia il trattino e qualche carattere,
    ecco cosa succede:
    ciclomotore – X6LGDK – 1003647 X6LGDK – 10
    autovettura – DE560ZT – OMESSO9172057 DE560ZT – O
    autocarro – CL641YV – WF0TXXTTPT3523643 CL641YV –
    autovettura – CN895137 – ZFA17600002001026 CN895137 –
    autovettura – AK011YSTER – OMESSO9016112 AK011YSTER
    autovettura – AN882HB – OMESSO9039686 AN882HB – O
    autovettura – TO32125S – ZFA16000002584679 TO32125S –

    questa la formula utilizzata:
    =STRINGA.ESTRAI(STRINGA.ESTRAI(A1;TROVA(“-“;A1)+1;LUNGHEZZA(A1));1;RICERCA(“-“;A1)-1)

    mi date un aiuto? io ho provato a fare un po di prove variando il valore numerico (-1, -2 ecc ecc) ma variando la lunghezza della parte centrale, non si adegua alla lunghezza stessa.

    spero di essere stato chiaro

    grazie,

    • gianfranco ha detto:

      Buongiorno Marco,
      potresti inviarmi anche un paio di esempi di come era la stringa e di quale parte del testo vorresti estrarre? Così riesco a capire meglio il problema e darti la soluzione corretta.

      Grazie 🙂

    • gianfranco ha detto:

      P.S.: con questa formula estrai il numero della targa (penso sia quello)


      =ANNULLA.SPAZI(STRINGA.ESTRAI(STRINGA.ESTRAI(A1;TROVA("-";A1)+1;LUNGHEZZA(A1));1;TROVA("-";A1;TROVA("-";A1)+1)-TROVA("-";A1)-1))

      La tua formula è corretta tranne che alla fine, stringa.estrai vuole come ultimo parametro la lunghezza della stringa da estrarre. Tu stai passando come lunghezza la posizione del primo trattino ( – ) della stringa in A1 che è variabile, come vedi dalla mia formula c’è qualche calcolo in più da fare ovvero la posizione del trattino partendo dalla posizione del primo trattino più 1 (così ottieni la posizione del secondo) meno la posizione del primo trattino, il risultato è la lunghezza del testo fra i 2 trattini

    • tonimarconi ha detto:

      Ciao Gianfranco,
      ti scrivo da qui x’ nn son riuscito a trovare nel sito una tua mail per porti qusto quesito:

      ho la necessità di gestire un file con listino prezzi di fornitori contenenete le anangrafihe e i relativi prezzi,
      Ho anche la necessita di generare una maschera di riepilogo che contenga anagrafica + varei voci di prodotti con prezzi,
      Il tutto deve andare a generare una mail a ciascuna anagrafica.

      spero di essere stato abbastana chiaro,

      hai già trattao o sviluppato soluzioni simili???

      grazie , a presto spero++ciao MArco

      • gianfranco ha detto:

        Ciao Marco,
        non ho sviluppato applicazioni simili ma non credo sia complicato realizzarla. Mi spiace per i problemi con il contatto ma un plugin non sta funzionando come deve. Ti invio una mail io per chiederti maggiori dettagli 🙂

  2. Emanuela ha detto:

    Buongiorno, ho letto il forum e ho necessita’ in excel di scopattare i numeri inseriti in una cella e farne fare la loro somma, per esempio 13 in A1 deve diventare 1+3 e quindi inserire il suo valore in automatico. E’ possibile farlo? La ringrazio in anticipo

  3. angel ha detto:

    Buongiorno,

    a partire da una cella, per esempio contenente la stringa “12345PIPPO6789….”

    necessiterei di una formula che mi permetta di fare una estrazione creando ulteriori due campi di cui il primo arriva fino alla stringa PIPPO (esclusa) e il secondo a partire dalla stringa PIPPO (compresa).

    cioé:

    la 1a cella prodotta contiene “12345”
    la 2a cella prodotta contiene “PIPPO6789…”

    Grazie mille a chi può aiutarmi

    • gianfranco ha detto:

      Buongiorno,
      mi servirebbe un’informazione in più, i caratteri della prima parte della stringa (12345) sono sempre 5? Perché estrarli non è un problema se il loro numero è fisso, diverso è se sono in numero variabile

    • gianfranco ha detto:

      In ogni caso, ecco 2 formule matriciali che, in ordine, estraggono tutto il testo fino al primo carattere non numerico e estraggono dal primo carattere non numerico in poi, essendo matriciali vanno confermate con CTRL+MAIUSC+INVIO

      Ponendo che la stringa “12345PIPPO6789” sia in A1


      =STRINGA.ESTRAI(A1;1;CONFRONTA(VERO;VAL.ERRORE(VALORE(STRINGA.ESTRAI(A1;RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1)));1)));0)-1)

      Restituisce 12345 nell’esempio


      =STRINGA.ESTRAI(A1;CONFRONTA(VERO;VAL.ERRORE(VALORE(STRINGA.ESTRAI(A1;RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1)));1)));0);LUNGHEZZA(A1))

      Restituisce “PIPPO6789…”

      ATTENZIONE: Non funziona se la cella contiene solo numeri o se è vuota

      Spero ti sia utile, per qualunque aiuto non esitare a contattarmi

  4. BenedettaC ha detto:

    Ciao Buongiorno,
    ho un codice alfanumerico formato da 7 numeri (1141009) seguiti da un testo (s) e poi da un numero seriale (9).
    Es: 1141009s9
    Avrei bisogno di ordinare i vari codici dal solo seriale dal più piccolo al più grande.
    avevo pensato di procedere così:
    estrarre la parte finale del codice (il seriale) e poi ordinarlo con ORDINA
    il seriale è stato estratto con questa funzione
    =STRINGA.ESTRAI(AI2;9;LUNGHEZZA(AI2))
    e mi riesce 🙂
    ma poi quando chiedo a excel di ordinare i valori estratti dal più piccolo al più grande vengono messi a caso non in ordine crescente.
    spero di essere stata chiara.
    Benedetta

    • gianfranco ha detto:

      Buongiorno Benedetta,
      grazie per avermi scritto. Per cominciare ti consiglio di cambiare la tua formula aggiungendo due trattini ( — ) prima della funzione STRINGA.ESTRAI, in questo modo il valore viene convertito in numero invece che rimanere testo ed è più facile ordinarlo


      =--STRINGA.ESTRAI(AI2;9;LUNGHEZZA(AI2))

      Detto questo, non so che procedura tu stia usando per ordinare, la più semplice è posizionarsi nella cella corrispondente alla prima riga della colonna in cui hai i codici numerici (es. B12) e cliccare sul pulsante A-Z che trovi nella scheda DATI della barra degli strumenti, assicurandoti che dopo il blocco di righe che vuoi ordinare ci sia almeno 1 riga vuota. Se selezioni tutte le colonne, invece, i dati vengono ordinati in base alla prima colonna, probabilmente per questo ti ritrovi con un ordine sparso.

      Spero di esser stato chiaro, altrimenti chiedimi pure ulteriori lumi 🙂

      Buona giornata

      • BenedettaC ha detto:

        ciao, grazie per il suggerimento 🙂
        ordinavo col pulsante AZ ma i numeri eri ordinati a casaccio.
        Ho corretto la formula come mi hai suggerito, poi fatto Ordina AZ e finalmente sono riuscita nel mio intento.
        Penso che non ordinasse in ordine crescente perchè riconosceva il dato come numero ma come testo, infatti non appena ho fatto le correzioni ha funzionato tutto.
        grazie per le dritte
        ciao
        Benedetta

        • gianfranco ha detto:

          Di nulla, felice di esserti stato d’aiuto.

          Giusto perché ti rimanga come traccia, l’ordinamento dei numeri memorizzato come testo avviene mettendo prima tutti quelli che iniziano con 0 (0, o1, 02, 023, etc) poi quelli che iniziano con 1 (1, 11, 113, 12) e così via.

          Trasformandoli in numeri, invece, li ordina per la loro sequenza naturale.

          A presto

  5. Italia ha detto:

    Ciao ragazzi, sto cercando di fare una cosa simile, con l’unica differenza che la riga che devo estrarre si trova in diversi file di testo.
    Ho circa mille file di testo da cui devo estrarre un numero di serie dei PC, questo numero si trova in una riga ben definita, ma pensare di mettermi a farlo uno a uno mi pare folle.
    Inoltre se trovo il sistema di automatizzare questa estrazione, potrei portarmi fuori più colonne con altri dati come nome del pc, indirizzo ip ecc.
    Sapete aiutarmi ?

    • gianfranco ha detto:

      Ciao,
      quello che chiedi si può fare ma con una macro che prenda uno ad uno i file, li legga ed estragga i dati che ti servono per poi metterli in colonne. Per aiutarti, però, ho bisogno di vedere come sono strutturati i file e come vorresti che fosse il risultato. Se vuoi puoi mandarmi un file di esempio del sorgente all’indirizzo email che trovi nella sezione “Contattami” del sito 🙂

  6. Livia ha detto:

    Ciao Gianfranco,
    grazie! Questo articolo mi è stato estremamente utile.
    In dettaglio ho sfruttato la funzione stringa.estrai unita a ricerca per recuperare il testo inserito prima di un certo carattere speciale (es Pippo | Commento, per ottenere “Pippo” e usando ‘|’ come carattere speciale).
    Ora ho un problema (simile a quello che hai specificato nelle precisazioni relativo ai numeri):
    il risultato viene interpretato da excel come una stringa e non come un testo (o almeno credo).
    Mi spiego meglio: in alcuni casi ho recuperato l’informazione utilizzando la funzione, in altri ho inserito manualmente il testo di interesse. Ora mi trovo che se faccio una tabella pivot per sommare i dati di un’altra colonna in base a questo campo, risultano due differenti righe anche se contengono lo stesso testo.
    Pippo 4
    Pippo 2
    Hai qualche suggerimento su come posso risolvere questo problema?

    Grazie,
    Livia

    • gianfranco ha detto:

      Ciao Livia,
      innanzitutto sono contento che l’articolo ti sia stato utile 🙂

      Io vedo 2 problemi in quello che mi scrivi, se non ho capito male, uno è il fatto che Excel ti restutuisce un numero come testo e l’altro è il conteggio di parole apparentemente uguali ma che vengono interpretate in modo divero, non potendo vedere il file ti do entrambe le soluzioni:

      PRIMA: Se hai usato STRINGA.ESTRAI() per estrarre un numero da un testo il risultato è appunto una stringa e non un numero, per risolvere velocemente il problema aggiungi due trattini prima della formula, questo fa si che Excel converta il testo in numero


      =--STRINGA.ESTRAI(......)

      SECONDO: A volte con STRINGA.ESTRAI può capitare che venga preso anche uno spazio all’inizio o alla fine dell’estrazione, dipende da come è scritta la formula, solitamente succede alla fine e noi non ci accorgiamo che c’è ma Excel si, quindi “Pippo” e “Pippo ” vengono interpretati come testi diversi, in questo caso puoi racchiudere STRINGA.ESTRAI nella funzione ANNULLA.SPAZI che elimina gli spazi all’inizio ed alla fine


      =ANNULLA.SPAZI(STRINGA.ESTRAI(......))

      Spero che una delle due sia la soluzione che stavi cercando, fammi sapere.

      A presto

  7. riccardo ha detto:

    Salve, ho provato di tutto.

    ho queste celle:
    UNITTS_1_0
    UNITX_1_0
    UNITY_1_0
    UNITROT_1_0
    UNITTEAM_1_0
    UNITFIX_1_0
    UNITST_1_0
    UNITCAR_1_0

    che si ripetono per 65 volte tutte ed 8
    devo CAMBIARE il finale da 0 in 1 o viceversa….
    non ci riesco in nessun modo …
    Mi puoi aiutare ?
    se serve mando il file…

    Grazie
    Ric

    • gianfranco ha detto:

      Buongiorno Riccardo,
      ponendo che il valore che vuole modificare sia in A1 può inserire in B1 questa formula:

      =SE(DESTRA(A1)="0";SINISTRA(A1;LUNGHEZZA(A1)-1) & "1";SINISTRA(A1;LUNGHEZZA(A1)-1) & "0")

      e trascinare giù per tutte le righe necessarie

      Sono a disposizione per qualunque chiarimento sulla formula

      • riccard ha detto:

        Grazie Gianfranco, mi sembra che sia la strada giusta . La provo stasera ( finite vacanze ahimè)

        • riccard ha detto:

          Ps : se dovessi cambiare il penultimo carattere , ovvero il terzultimo, basta che nella formula sostituisco gli 0 con gli 1 ?

          • gianfranco ha detto:

            Purtroppo no 🙂

            in questo caso bisogna usare questa formula:

            =SINISTRA(A1;LUNGHEZZA(A1)-3) & SE(SINISTRA(DESTRA(A1;3);1)="0";"1";"0") & DESTRA(A1;2)

            e già che ci sono ti accorcio anche quella di prima (son tornato anche io oggi, devo carburare eheh)


            =SINISTRA(A1;LUNGHEZZA(A1)-1) & SE(DESTRA(A1;1)="0";"1";"0")

            • riccardo ha detto:

              Gianfranco, non so come ringraziarti… ci stiamo arrivando.
              ti do il contesto :
              i 65 gruppi di 8 celle hanno come identificativo 4 numeri finali
              _X_Y dove : (solo 0 e 1)
              X = è il gruppo di appartenenza
              y = identifica il numero del gruppo di celle.

              esempio :
              Gruppo dove X=0:

              UNITTS_0_0
              UNITX_0_0
              UNITY_0_0
              UNITROT_0_0
              UNITTEAM_0_0
              UNITFIX_0_0
              UNITST_0_0
              UNITCAR_0_0
              UNITREINF_0_0
              UNITTS_0_1
              UNITX_0_1
              UNITY_0_1
              UNITROT_0_1
              UNITTEAM_0_1
              UNITFIX_0_1
              UNITST_0_1
              UNITCAR_0_1
              UNITREINF_0_1
              UNITTS_0_2
              UNITX_0_2
              UNITY_0_2
              UNITROT_0_2
              UNITTEAM_0_2
              UNITFIX_0_2
              UNITST_0_2
              UNITCAR_0_2
              UNITREINF_0_2

              quindi rimane fisso lo 0 e cambiano le y fino a 65.

              analogamente il gruppo dove X è = 1 :

              UNITTS_1_0
              UNITX_1_0
              UNITY_1_0
              UNITROT_1_0
              UNITTEAM_1_0
              UNITFIX_1_0
              UNITST_1_0
              UNITCAR_1_0
              UNITREINF_1_0
              UNITTS_1_1
              UNITX_1_1
              UNITY_1_1
              UNITROT_1_1
              UNITTEAM_1_1
              UNITFIX_1_1
              UNITST_1_1
              UNITCAR_1_1
              UNITREINF_1_1
              UNITTS_1_2
              UNITX_1_2
              UNITY_1_2
              UNITROT_1_2
              UNITTEAM_1_2
              UNITFIX_1_2
              UNITST_1_2
              UNITCAR_1_2
              UNITREINF_1_2
              UNITTS_1_3
              UNITX_1_3
              UNITY_1_3
              UNITROT_1_3
              UNITTEAM_1_3
              UNITFIX_1_3
              UNITST_1_3
              UNITCAR_1_3
              UNITREINF_1_3

              Quello che devo fare è invertire Le X mantenendo ferme le Y… non so se mi sono spiegato bene perchè la cosa è molto complicata.
              quindi i 65 gruppi da 8 con lo 0 iniziale devono avere l’1 e successivi 65 gruppi da 8 con lo 1 iniziale devono avere lo zero…..
              NB il numero di 65 gruppi può variare in minore mai in maggiore…

              Le tue due formule vanno bene ma non tengono conto di questo che ho ora scritto. Ma si può fare , soprattutto ?

              quand’è il tuo compleanno che ti mando un regalo via amazon ? 😉

              grazie 100000000
              Ric

              • gianfranco ha detto:

                Ciao Riccardo,
                per fare si può far tutto ma, onestamente, mi sono perso nella spiegazione, un esempio del risultato (magari mandandomi un file via email) mi aiuterebbe a capire meglio cosa vuoi fare.

                Per quanto riguarda i gruppi, mi hai parlato di gruppi da 8 ma quelli che mi hai messo nell’esempio sono da 9:

                UNITTS_1_0
                UNITX_1_0
                UNITY_1_0
                UNITROT_1_0
                UNITTEAM_1_0
                UNITFIX_1_0
                UNITST_1_0
                UNITCAR_1_0
                UNITREINF_1_0

                se non ho capito male il risultato che vuoi ottenere è:

                UNITTS_1_0
                UNITX_1_0
                UNITY_1_0
                UNITROT_1_0
                UNITTEAM_1_0
                UNITFIX_1_0
                UNITST_1_0
                UNITCAR_1_0
                UNITREINF_1_0
                [….]
                UNITTS_1_65
                UNITX_1_65
                UNITY_1_65
                UNITROT_1_65
                UNITTEAM_1_65
                UNITFIX_1_65
                UNITST_1_65
                UNITCAR_1_65
                UNITREINF_1_65

                Se non hai già dei dati di partenza forse sarebbe più semplice scrivere una macro che te li crei al volo.

                • riccardo ha detto:

                  Ciao Gianfranco e grazie, innanzitutto.
                  1) si, ho sbagliato, sono 9 e non 8 i gruppi di celle; ma questo alla fine conta poco.
                  2) l’esempio che hai messo è abbastanza giusto…. la variabile che si muove ( sennò che variabile è? 😉 ) è X ovvero il terzultimo carattere.
                  3) come e dove ti invio il file ? ( l’ho messo in cloud): è un csv molto grosso ( non è un.xls ) è un text ma lo apro in Excel altrimenti divento matto peggio.

                  4) scusa l’ignoranza ma non so bene che intendi quando dici di fare una macro al volo….. 🙁

                  grazie
                  Ric

  8. Nicola ha detto:

    Ciao, avrei un problema da risolvere. Da una cella contenete lettere e numeri devo individuare “l’intruso”. Per esempio la stringa deve contenere solo 0 e/o 1. Nel caso ci siano cifre diverse nella cella affianco deve comparire la scritta “l’intruso è il 2”. Esempio: cella A1 contiene il numero 101110 in A2 deve comparire la scritta “NUMERO BINARIO OK” ; se in A1 c’è scritto 1021 in A2 deve comparire la scritta “L’INTRUSO E’ il 2”. Grazie mille.

  9. Lucia ha detto:

    Ciao
    Intanto grazie di esistere!!!
    Con le tue formule sto risolvendo diversi problemi.
    Da due giorni sto combattendo con stringa.estrai…. Ed ho trovato quasi tutto sul tuo blog.
    Manca una cosina, ci sto provando ma….
    Nelle celle A1, A2, A3 e A4 ho i seguenti valori:
    aaaa-b
    aaaaaa-bbbb-cc
    A-b-c–d
    AA
    Devo estrarre tutte quello che si trova dopo l’ultimo trattino, indipendentemente dal numero dei “-” presenti nella cella. Quindi il risultato sarà:
    B
    Cc
    D
    vuoto

    Come faccio?
    Grazie

    Lucia

  10. Adolfo ha detto:

    Grande come sempre. Jan.

  11. Michele ha detto:

    Ciao, sto cercando di fare un estrazione massiva di tutti i domini da un elenco di mail, di una stringa quindi a partire dal carattere “@”, hai per caso già una formula pronta per fare ciò?

    Ti ringrazio e resto in attesa.
    Fai un ottimo lavoro 🙂

    Michele

    • gianfranco ha detto:

      Ciao Michele,
      certo che ce l’ho, semplicemente:


      =STRINGA.ESTRAI(A1;TROVA("@";A1)+1;LUNGHEZZA(A1))

      Se vuoi includere anche “@” elimina semplicemente il +1.

      Grazie per “l’ottimo lavoro”, felice che ti piaccia

  12. GIUSEPPE ha detto:

    Ciao Scusami per il disturbo, ho una cella contenente i seguenti valo: ESEMPIO 081/8962563; ARD; vorrei che in un altra cella mi restituisse solo i numeri, senza lettere, vocali o caratteri speciali, ESEMPIO: 0818962563. GRAZIE

    • gianfranco ha detto:

      Ciao Giuseppe,
      nessun disturbo, anzi, mi fa piacere che tu sia passato e mi abbia scritto. Per il tuo problema esistono due soluzioni. La prima, e più semplice, è installare il mio plugin che puoi scaricare qui ed utilizzare la seguente funzione:

      =TE.ESTRAI.NUMERI(A1)

      Se devi però distribuire il file ad altre persone, lasciando le formule, devi far installare anche a loro il plugin oppure trasformarle in valori.

      La seconda soluzione è quella di usare una formula matriciale che va confermata con CTRL+MAIUSC+INVIO, decisamente più complessa ma funziona con Excel senza bisogno di ulteriori addin


      =SOMMA(STRINGA.ESTRAI(0&A1;GRANDE(VAL.NUMERO(--STRINGA.ESTRAI(A1;RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1)));1))*RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1)));RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1))))+1;1)*10^RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1)))/10)

      Fammi sapere poi quale hai deciso di adottare 🙂

      A presto

  13. Emanuele ha detto:

    Ciao ho un problema con Excel e non riesco a trovare una soluzione.
    devo estrarre delle misure da una serie di stringhe che non si trovano necessariamente in una posizione definita.
    Es.
    Abrasivo in tessuto 75mm
    Bit Max Grip T10 25mm 3pz
    Cilindro e perno abrasivo 13mm G60 407
    etc..
    Io devo estrarre sempre la misura facendo ricercare la stringa “mm” e se la trova, in qualcunque posizione essa sia, estrapolarmi sia mm sia il numero adiacente. la cifra è sempre attaccata alla stringa “mm”.

    Spero di essere stato chiaro e grazi dell’aiuto.

    • gianfranco ha detto:

      Ciao Emanuele,
      come per il commento qui sopra esistono due soluzioni. La prima, e più semplice, è installare il mio plugin che puoi scaricare qui ed utilizzare la seguente funzione:

      =TE.REGEX.ESTRAI(A1;"[0-9]*mm";1)

      Come puoi vedere è nata proprio per queste esigenze e sfrutta le espressioni regolari per individuare un testo, in questo caso estrare la parte di stringa che contiene un qualunque numero composto da una o più cifre seguito dalle lettere “mm”. Il problema è che se devi distribuire il file sei costretto a far installare l’addin anche a tutti coloro che lo leggono o convertire il risultato delle formule in valori.

      Se vuoi invece usare solo le formule puoi utilizzare questa (un po’ complessa)

      =ANNULLA.SPAZI(STRINGA.ESTRAI(A1;TROVA(CODICE.CARATT(255);SOSTITUISCI(A1;" ";CODICE.CARATT(255);LUNGHEZZA(STRINGA.ESTRAI(A1;1;TROVA("mm";A1)))-LUNGHEZZA(SOSTITUISCI(STRINGA.ESTRAI(A1;1;TROVA("mm";A1));" ";""))));TROVA("mm";A1)-TROVA(CODICE.CARATT(255);SOSTITUISCI(A1;" ";CODICE.CARATT(255);LUNGHEZZA(STRINGA.ESTRAI(A1;1;TROVA("mm";A1)))-LUNGHEZZA(SOSTITUISCI(STRINGA.ESTRAI(A1;1;TROVA("mm";A1));" ";""))))+2))

      Esiste in realtà anche una terza alternativa usando il VBA (le macro, per intenderci), se sai come implementarle nel foglio posso scrivertene una 🙂

      • Emanuele ha detto:

        Grazie mille, installerò il plug in non ci sono problemi perchè gestisco solo io il database. Le VBA sono ancora un pò ostiche per me e preferisco prima conoscere bene le funzioni.

        Sei super performante, ti offfrirò un caffè. 😉

        PS quando hai tempo, visto che sono un appassionato (anche se non un super esperto) di excel, puoi farmi capire la formula che hai utilizzato?

        IN questo modo miglioro le mie conoscenze, non guasta mai. 🙂

      • Emanuele ha detto:

        Ciao Gianfranco il plugin funziona alla grande. solo un dubbio che non ti ho specificato io.

        Dovrei farlo funzionare anche per stringhe di questo tipo qui:

        Adattatore esagonale Power Change 8×19-159mm
        Albero flessibile ausiliario 6x1250mm
        In questo caso, giustamente il plug mi da come info solo il numero vicino alla stringa “mm” ma io ho bisogno di tutta la stringa, per intenderci fino allo spazio che precede la misura. Come posso modificare il tutto?

        Grazie, Emanuele.

        • gianfranco ha detto:

          Ciao Emanuele, vuoi rendermi la vita difficile, eh? 😀

          Ecco la stringa da usare in modo che prenda una stringa che inizi con uno spazio, contenga qualunque cosa in mezzo e finisca con mm. L’annulla spazi serve ad eliminare lo spazio iniziale che, con questo sistema, viene incluso nella stringa finale.


          =ANNULLA.SPAZI(TE.REGEX.ESTRAI(A1;"\s*\w*-*\w*(?:mm)";1))

          • Emanuele ha detto:

            Grande, non ci sarei arrivato neanche se avessi sbattuto la testa per un anno!

            Nel caso avessi modo di spiegarmi sia questa che la funzione relativa che mi hai trascritto prima te ne sarei grato!

            P.S Caffè offerto. 😀

            • gianfranco ha detto:

              Ciao Emanuele,
              ti scrivo qui ma, se vuoi, approfondiamo via email. TE.REGEX.ESTRAI fa parte di una serie di funzioni che ho realizzato per Excel che sfruttano le potentissime Espressioni Regolari. Queste ultime sono utilizzate in programmazione per estrarre delle stringhe da testi non strutturati (come nel tuo caso).

              Per approfondire le Espressioni Regolari ti consiglio di leggere questo testo che le spiega perfettamente (qui avrei bisogno di 2 giorni per spiegarle).

              Prima di procedere ricorda che * vuol dire 0 o più occorrenze.

              La mia, brevemente, cerca una stringa che inizi con uno spazio (\s*) contenga una o più parole (\w* ovvero word ovvero un’insieme di lettere e numeri) contenga eventualmente un – (-*) contenga eventualmente una parola (\w*) e finisca con mm (?:mm).

              Per quanto riguarda la formula, invece, la cosa si fa più complicata, questa è meglio che la discutiamo via email o non mi basta lo spazio dei commenti per spiegartela. Forse se segui passo passo lo sviluppo della formula riesci a capirne bene tutti i passaggi, per farlo ti basta usare il tasto “Valuta Formule” nella scheda “Formule”. In ogni caso ho utilizzato i sistemi che ho spiegato in alto in questo articolo combinandoli tra di loro.

              Se hai ancora bisogno mi trovi qui 🙂

              P.S.: grazie per il caffè 😀

  14. francesco ha detto:

    Ciao!

    Sto lavorando su delle stringhe a lunghezza variabile dalle quali dovrei estrarre solo la parte di testo fuori di parentesi.

    le stringhe son fatte così:

    [CFI_FA.Q.S11.IT.F.S1.W0.111.101.2] Totale attività delle società non finanziarie

    tenendo conto che sia la parte in parentesi che quella di testo sono di lunghezza variabile, ed io ho bisogno di fare l’operazione su di un migliaio di celle, volevo sapere se era possibile dire ad excel di prendere tutto il testo dopo la parentesi.

    grazie a presto

    • gianfranco ha detto:

      Ciao Francesco,
      scusandomi per il ritardo ecco la tua soluzione, ponendo che la stringa sia in A1 puoi usare


      =ANNULLA.SPAZI(STRINGA.ESTRAI(A1;TROVA("]";A1)+1;LUNGHEZZA(A1)))

      A presto 🙂

  15. Massimo M. ha detto:

    Buongiorno Gianfranco,
    avrei bisogno di estrarre solo il valore numerico (negli esempi che seguono la quota 1000) da una cella e restituirla in un’altra come solo numero.
    Esempio 1:
    1000 H8
    Esempio 2:
    ø1000
    Ti ringrazio tantissimo

    • gianfranco ha detto:

      Ciao Massimo,
      purtroppo con le sole formule non riesco a fare quanto chiedi perché, dagli esempi che mi hai postato, esistono altri numeri nella stringa (vedi H8) e, in questo caso, verrebbe estratto anche l’8.

      La soluzione può essere soltanto quella di scrivere una funzione ad hoc in VBA ed usarla sul foglio in cui è scritta.

      Ovviamente, in tal caso, avrei bisogno di qualche dato in più, ti chiederei pertanto di contattarmi privatamente usando la mail che trovi nel menu contatti 🙂

      A presto

      Gianfranco

  16. Carmine ha detto:

    Buongiorno GIanfranco, sto provando a crearmi una maschera su “foglio!scheda” in cui in D,3 ho un filtro che mi fa cercare il cliente da un elenco che si trova in “elenco!A1:A1000” , bene fin qui tutto ok ed una volta selezionato il cliente, mi riporti in tutti I vari campi che ho predisposto I relativi riferimenti, ADESSO ARRIVA IL PROBLEMA!!!!! Visto che per alcuni clienti ho piu di un articolo con diversi dati, ho pensato di farmi riportare alla base della scheda una lista dei nominativi che appunto il risultato della ricerca di D3 , visto che per distinguerli ho usati (1)-(2)-ecc… , con STRINGA.ESTRAI in altra cella elimino (1)-(2)-ecc… ma mn riesco ad ottenere il listato dei soli clienti in rilievo, provando con filtro avamzato mi restituisce tutta la lista dell’elenco “elenco!A1:A1000” , x favore potresti aiitarmi ? Ti ringrazio anticipatamente x l’attenzione CARMINE

  17. Gibo ha detto:

    Articolo molto interessante e completo, complimenti all’autore.
    In determinati casi però la produzione di queste formule possono essere rapidamente sostituite da applicazioni più rapide.

    porto un esempio per chiarire:

    Prendiamo ad esempio un testo nella cella A1 che sia “ciao a tutti i visitatori di TuttoExcel” e di voler estrarre la parola TuttoExcel.

    io semplicemente prenderei la stringa (o le colonne) la incollerei in word come solo testo.
    Sostituirei agli spazi il tab (lo potete fare agilmente abilitando il Mostra Tutto).
    Poi ti basta un copia e incolla di tutto il testo in un foglio nuovo e ti basta estrarre la colonna interessata.

    Questo solo per questioni di praticità e rapidità. L’articolo invece è incredibilmente più complesso e copre casistiche non previste dal metodo che ho indicato.

    super Kudos

  18. Sergio ha detto:

    Buongiorno Gianfranco,
    Avrei il seguente problema, ho delle celle con all’interno un codice formato sa due lettere e poi 3 numeri, esempio AA123, avrei bisogno di copiarlo in una cella dove rimane AA 123, praticamente mi serve lo spazio tra le lettere e i numeri.
    Sapresti aiutarmi
    Grazie

  19. Damiano ha detto:

    Buongiorno ho un problema su Excel in una casella ho un cognome nome vorrei dividerlo in due campi, ho applicato le vostre formule tutto va bene per nel caso di un cognome “DI NAPOLI VITTORIO” non funziona, come posso fare. ti ringrazio

  20. francesco ha detto:

    Perdonami
    Grazie alle tue indicazioni riesco ad estrarre comune
    VIA MATTEOTTI, 1 , CICCIANO (NA)
    =STRINGA.ESTRAI(A3;RICERCA(” , “;A3)+3;LUNGHEZZA(A3))
    volendo estrarre il solo comune senza (prov)
    VIA MATTEOTTI, 1 , CICCIANO (NA)

  21. Pasquale ha detto:

    Buongiorno,
    vi chiedo un aiuto su una funzione che vorrei adottare.
    Nel dettaglio,
    Vorrei confrontare i prime 3 caratteri di una cella della colonna A con i primi 3 caratteri di una cella della Colonna B

    Grazie a tutti per l’eventuale supporto

  22. Luca Quinti ha detto:

    Ciao
    in una stringa di testo di questo tipo:

    prova uno due tre @quattro cinque
    prova nove sei sette @dieci venti

    vorrei estrarre la singola parola dopo la @

    • gianfranco ha detto:

      Ciao Luca,
      ecco la formula che fa per te (ponendo che la frase sia in A1)


      =STRINGA.ESTRAI(
      A1;TROVA("@";A1;1)+1;
      TROVA(" ";STRINGA.ESTRAI(A1;TROVA("@";A1;1)+1;
      LUNGHEZZA(A1)))-1)

  23. Costantino ha detto:

    Ciao e grazie per il tuo articolo,
    Se ho un testo del tipo:
    “CAAP “Del Duca” (Italy,sid:14327,aid:108684);EL BUEN (Spain,sid:67118,aid:403830);İMKB MESLAKİ VANALOLU LİSESİ (Italy,sid:235443,aid:1790543)”
    e voglio estrare solamene il numero che c’è fra “Italy,sid:” fino alla “,” (In questo caso quindi il numero 14327)) come potrei fare?
    Utilizzando =STRINGA.ESTRAI(STRINGA.ESTRAI(A1;TROVA(“Italy,sid”;A1)+10;LUNGHEZZA(A1));1;RICERCA(“,”;A1)-1)
    mi parte bene ma il testo estratto non finisce alla virgola. Non saprei cosa fare.

    Poi, forse un po’ più complicato, se ho dentro il testo più di uno Italy,sid:” come faccio ad estrarli tutti?

    grazie ancora

  24. Paolo ha detto:

    Purtroppo le tue istruzioni non funzionano del tutto. Espongo il mio problema:
    ho una serie di celle, miste tra parole e numeri, che hanno degli elementi tra parentesi. Io vorrei creare, al termine della sequenza di celle, una cella unica che raccolga tutte le parole tra parentesi.
    Esempio: nella cella a6 c’è la somma delle scritte tra parentesi delle altre
    cella a1: dolce (ga)
    cella a2: mamma (n)
    cella a3: eternità (zi)
    cella a4: filosofia (s)
    cella a5: scuola (simo)
    cella a6: ganzissimo

    • gianfranco ha detto:

      Ciao Paolo,
      scusa se rispondo in ritardo ma sono parecchio preso con il lavoro. Le istruzioni funzionano, solo che non sono adatte a fare quello che serve a te. Nel tuo caso servirebbe una formula matriciale.

      Nella cella A7 del tuo esempio inserisci la seguente formula

      =CONCAT(STRINGA.ESTRAI(A1:A6;TROVA(“(“;A1:A6)+1;TROVA(“)”;STRINGA.ESTRAI(A1:A6;TROVA(“(“;A1:A6)+1;100))-1))

      e conferma con CTRL+MAIUS+INVIO e vedrai che farà quanto chiedi 🙂

      A presto

  25. Claudia ha detto:

    Buongiorno, ho bisogno di un aiuto “diverso”.. devo estrapolare le ultime 3 cifre di ogni cella, ma le cifre che estrapolo, devono cancellarsi nella cella di origine, tipo:
    1/00
    10/00
    110/00
    Devo estrapolare le ultime 3 cifre “/00” e lo faccio con la formula =DESTRA, ma dovrei far si che nelle celle di origine, le cifre che estrapolo si cancellassero e rimanesse:
    1
    10
    100
    Spero di essermi spiegata ed attendo aiuto..grazieeee!!
    Claudia

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Questo sito utilizza Akismet per ridurre lo spam. Scopri come vengono elaborati i dati derivati dai commenti.