Esempi di utilizzo della funzione STRINGA.ESTRAI
Uno 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
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,
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 🙂
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
formula perfetta!!!
sei troppo il migliore
grazie ancora
Di nulla, spero sia stata chiara anche la spiegazione 🙂
Grazie per il feedback, fa piacere sapere che le formule funzionano eheh
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
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 🙂
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
Ciao, esiste un modo per farlo attraverso una formula matriciale, ponendo che la cella sia A1 scrivi la seguente formula confermandola con CTRL+MAIUSC+INVIO
=MATR.SOMMA.PRODOTTO(--STRINGA.ESTRAI(A1;RIF.RIGA(INDIRETTO("1:" & LUNGHEZZA(A1)));1))
Vedrai magicamente apparire il totale 🙂
A presto
ci provo subito e grazie ancora =D
ancora prego e fammi sapere 🙂
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
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
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
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
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
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
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
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 ?
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 🙂
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
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
Il problema era proprio uno spazio in più che non si “vedeva”.
Grazie!!
Di nulla, son qui per questo 🙂
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
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
Grazie Gianfranco, mi sembra che sia la strada giusta . La provo stasera ( finite vacanze ahimè)
Ps : se dovessi cambiare il penultimo carattere , ovvero il terzultimo, basta che nella formula sostituisco gli 0 con gli 1 ?
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")
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
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.
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
Ciao Riccardo,
ti ho scritto una mail sull’indirizzo indicato nei commenti, così facciamo prima e possiamo scambiarci i file.
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.
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
Grande come sempre. Jan.
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
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
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
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
P.S.: se sai come implementare le macro scritte in VBA posso anche scriverti una macro che semplifichi la formula 🙂
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.
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 🙂
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. 🙂
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.
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))
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. 😀
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è 😀
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
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 🙂
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
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
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
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
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
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
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)
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
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 @
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)
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
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
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
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