Risolto Formula su stringa con aggiunta di condizione

[Luke01]

Buongiorno a tutti,
avrei l'esigenza di integrare la formula già applicata:
=SE(LUNGHEZZA(ANNULLA.SPAZI(A2))=15;STRINGA.ESTRAI(ANNULLA.SPAZI(A2);1;5)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);6;4)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);10;4)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);14;2);SE(LUNGHEZZA(ANNULLA.SPAZI(A2))=14;STRINGA.ESTRAI(ANNULLA.SPAZI(A2);1;5)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);6;3)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);9;4)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);13;2);SE(LUNGHEZZA(ANNULLA.SPAZI(A2))=13;STRINGA.ESTRAI(ANNULLA.SPAZI(A2);1;5)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);6;4)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);10;5);SE(LUNGHEZZA(ANNULLA.SPAZI(A2))=12;SE(VAL.NUMERO(--STRINGA.ESTRAI(ANNULLA.SPAZI(A2);1;8));STRINGA.ESTRAI(ANNULLA.SPAZI(A2);1;5)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);6;5)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);11;2);STRINGA.ESTRAI(ANNULLA.SPAZI(A2);1;5)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);6;3)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);9;4));STRINGA.ESTRAI(ANNULLA.SPAZI(A2);1;5)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);6;5)))))
con altra condizione (modifica di una parte specifica del codice) espressa nel file allegato:

Questo dovrebbe portare al risultato finale come riportato nel file. Purtroppo non ho idea di come procedere.
Spero sia chiaro.In caso contrario sono a disposizione
Grazie per la vs attenzione e aiuto!

[aggiunte le spiegazioni necessarie]​

 

[scossa]

Spero sia chiaro.In caso contrario sono a disposizione

Come da regolamento, la spiegazione della richiesta deve essere descritta chiaramente nel post, senza che si debba scaricare preventivamente il file per capirla.

 

[Terio]

la spiegazione della richiesta deve essere descritta chiaramente nel post

Hai perfettamente ragione, ma leggo che ci sono diverse eccezioni che sono difficili da replicare in una richiesta testuale.
@Luke01 (ciao) aggiungi la formula e lo snapshot nella richiesta,
ciao.

edit
Essendo passati i 30 minuti ho dovuto farlo io.

Inoltre per la lunghezza 13 della stringa, se non leggo male, c'è un'incongruenza con la lunghezza dei singoli pacchetti che compongono quella risultante:
SE(LUNGHEZZA(ANNULLA.SPAZI(A2))=13;STRINGA.ESTRAI(ANNULLA.SPAZI(A2);1;5)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);6;4)&"-"&STRINGA.ESTRAI(ANNULLA.SPAZI(A2);10;5)
5+4+5
non fa 13, se puoi darmi un riscontro la analizzo meglio.

Aggiungo che con il 365, ma già con le versioni precedenti, PIÙ.SE o SWITCH sono funzioni più indicate di una serie di SE nidificati.

 

[Luke01]

Ciao Terio
hai ragione! la condizione lunghezza=13 era un refuso e l'ho eliminata (i codici da verificare sono migliaia!). Inoltre, anzichè Annulla. Spazi ho preferito aggiungere Sostituisci " "-->"" così sono sicuro di non aver nessuno spazio sia nel che alla fine del codice.
Mi scuso per non aver incollato al formula che ora è questa:
=SE(LUNGHEZZA(SOSTITUISCI(A2;" ";""))=15;STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");1;5)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");6;4)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");10;4)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");14;2);SE(LUNGHEZZA(SOSTITUISCI(A2;" ";""))=14;STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");1;5)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");6;3)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");9;4)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");13;2);SE(LUNGHEZZA(SOSTITUISCI(A2;" ";""))=12;SE(VAL.NUMERO(--STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");1;8));STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");1;5)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");6;5)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");11;2);STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");1;5)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");6;3)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");9;4));STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");1;5)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");6;5))))

Per quanto riguarda le altre soluzioni proposte se gentilmente le potresti proporre , se ritieni siano migliori, perché al momento non le conosco ma mi piacerebbe vederle all'opera! Riallego il file modificato.
Di nuovo grazie per qualunque contributo

 

[Terio]

Soluzione 365 che esce un po' dagli schemi:
Gestione Nomi
trattini=LAMBDA(str;pos;SE(VAL.ERRORE(RIGHE(pos));str;trattini(RIMPIAZZA(str;INDICE(pos;1);;"-");ESCLUDI(pos;1))))
creando così una LAMBDA ricorsiva.
D2
=LET(codice;SOSTITUISCI(A2;" ";"");posizioni;SWITCH(LUNGHEZZA(codice);12;SE(REGEX.TEST(codice;"^\d{8}");{6.12};{6.10});14;{6.10.15};15;{6.11.16};{6});nuovoCodice;@trattini(codice;posizioni);splitCode;DIVIDI.TESTO(nuovoCodice;"-");SE(O(C2="";VAL.ERRORE(INDICE(splitCode;3));REGEX.TEST(codice;"^[\d]+$"));nuovoCodice;TESTO.UNISCI("-";;INDICE(splitCode;{1.2});C2;SE.ERRORE(INDICE(splitCode;4);""))))
controlla se il match è positivo, ma immagino debba essere spiegata.

Ciao.

 

[Luke01]

=LET(codice;SOSTITUISCI(A2;" ";"");posizioni;SWITCH(LUNGHEZZA(codice);12;SE(REGEX.TEST(codice;"^\d{8}");{6.12};{6.10});14;{6.10.15};15;{6.11.16};{6});nuovoCodice;@trattini(codice;posizioni);splitCode;DIVIDI.TESTO(nuovoCodice;"-");SE(O(C2="";VAL.ERRORE(INDICE(splitCode;3));REGEX.TEST(codice;"^[\d]+$"));nuovoCodice;TESTO.UNISCI("-";;INDICE(splitCode;{1.2});C2;SE.ERRORE(INDICE(splitCode;4);""))))

Grazie Terio, ma dà errore "#NOME?", a te funziona?

 

[ClaudioFu]

Buongiorno,
scusate ma io sono rimasto un po' indietro. Non posso provare la soluzione dell'oracolo @Terio perchè non ho 365 ma non mi torna qualcosa. La formula sul file di esempio non prende mai in considerazione un confronto fra i 4 digit in colonna C e il codice in colonna A. Però poi in colonna D dove dovrebbe esserci il risultato atteso se questi valori sono diversi li applica e di conseguenza diventa l'eccezione 3 ?
Allego un PDF perchè non riesco ad aggiungere una immagine.

 

[alfrimpa]

@Luke01

Quando Excel dà "#NOME?" vuol dire che non riconosce qualche funzione ma se tu hai il 365 le funzioni utilizzate dovresti averle tutte.

Hai inserito "trattini" (che compare nella formula) in Gestione Nomi?

Quasi sicuramente è quello.

Se hai fatto tutto allega il file non funzionante

 

[alfrimpa]

Allego un PDF perchè non riesco ad aggiungere una immagine.

Tutorial - Come inserire un'immagine nel post…

Per inserire un immagine bisogna ricorrere a dei servizi (gratuiti) di hosting di immagini, in pratica permettono di caricare ed archiviare online le proprie immagini per poi condividerle in rete su forum o pagine web. SEGUIRE LE ISTRUZIONI PER I DIVERSI SERVIZI WEB SOTTOINDICATI

www.forumexcel.it

 

[ClaudioFu]

Grazie mille @alfrimpa vado a leggere le istruzioni.

 

[Luke01]

non riesco ad inserire immagini perché non posso utilizzare i servizi di hosting (policy aziendali) pertanto, riallego il file con la formula proposta che dà errore...
Grazie

 

[ClaudioFu]

L'immagine relativa al mio post precedente ...
Ho evidenziato in rosa quanto mi sembra di capire sia il desiderata che non trovo in formula o spiegato in eccezione.

 

[Terio]

ma dà errore "#NOME?", a te funziona?

Premesso che a me funziona, non hai creato la udf come richiesto:

Gestione Nomi
trattini=LAMBDA(str;pos;SE(VAL.ERRORE(RIGHE(pos));str;trattini(RIMPIAZZA(str;INDICE(pos;1);;"-");ESCLUDI(pos;1))))

per cui trattini non viene vista come una funzione; inseriscila e questo è il risultato:

La formula sul file di esempio non prende mai in considerazione un confronto fra i 4 digit in colonna C e il codice in colonna A

Per come l'ho capita, la discriminante è (anche) la presenza del correttivo in C, come vedi sopra.

Ciao.

 

[gianfranco55]

ciao
verificato che la parola da cambiare è sempre la terza

=SE(C2="";B2;SE(VAL.NUMERO(SOSTITUISCI(B2;"-";"")*1);B2;SOSTITUISCI(B2;ANNULLA.SPAZI(STRINGA.ESTRAI(SOSTITUISCI(B2;"-";RIPETI(" ";30));2*30;30));C2)))

RISULTATO FINALE ATTESO CODICE	RISULTATO FORMULA
3803Z-MMM-DA02	3803Z-MMM-DA02
3831Z-KYK-J300	3831Z-KYK-J300
8750Z-KXX-E801	8750Z-KXX-E801
KKKNF-F02	KKKNF-F02
16700-K00-T012-M1	16700-K00-T012-M1
16703-K00-T010-M1	16703-K00-T010-M1
3214Z-GGA-6001	3214Z-GGA-6001
17505-K00-9001-H1	17505-K00-9001-H1
94001-06090-0S	94001-06090-0S
94061-05001	94061-05001
86152-GFF-9021-Y2	86152-GFF-9021-Y2
90601-00100-00	90601-00100-00
90084-04100-00	90084-04100-00
94031-08000-DT	94031-08000-DT
95701-080-4500-DT	95701-080-4500-DT

si potrebbe accorciare usando come verifica numero la colonna A al posto della B
=SE(C2="";B2;SE(VAL.NUMERO(A2*1);B2;SOSTITUISCI(B2;ANNULLA.SPAZI(STRINGA.ESTRAI(SOSTITUISCI(B2;"-";RIPETI(" ";30));2*30;30));C2)))

 

[Terio]

=SE(C2="";B2;SE(VAL.NUMERO(SOSTITUISCI(B2;"-";"")*1);B2;SOSTITUISCI(B2;ANNULLA.SPAZI(STRINGA.ESTRAI(SOSTITUISCI(B2;"-";RIPETI(" ";30));2*30;30));C2)))

Io ho capito che la richiesta era relativa a sostituire la formula in B:

avrei l'esigenza di integrare la formula già applicata
...
con altra condizione

ecco perché mi sono adoperato per realizzare una soluzione che trasformasse il codice della prima colonna in quello della seconda senza passaggi intermedi
Stavo rispolverando un po' di aramaico che a te piace tanto

Ciao.

 

[gianfranco55]

ciao
io invece sono andato a caso
Eccezione 1: nel caso di D4 vuoto = C4 (lasciare il codice così comè
visto le spiegazioni non mi tornava niente................D4 è sempre vuoto o ha la formula di sostituzione
C4 è la chiave di sostituzione

 

[Luke01]

Io ho capito che la richiesta era relativa a sostituire la formula in B:

è corretto, la mia era una richiesta di integrazione formula in B tenendo conto del risultato atteso.
Purtroppo non vedo le immagini (forse ho restrizioni qui).
perdonami ma non capisco dove inserire questa formula
Gestione Nomi
trattini=LAMBDA(str;pos;SE(VAL.ERRORE(RIGHE(pos));str;trattini(RIMPIAZZA(str;INDICE(pos;1);;"-");ESCLUDI(pos;1))))

 

[Terio]

non capisco dove inserire questa formula
Gestione Nomi
trattini=LAMBDA(str;pos;SE(VAL.ERRORE(RIGHE(pos));str;trattini(RIMPIAZZA(str;INDICE(pos;1);;"-");ESCLUDI(pos;1))))

Ribbon Formule
vai su Gestione Nomi
click su Nuovo
in Nome metti trattini
in Si riferisce a metti =LAMBDA(str;pos;SE(VAL.ERRORE(RIGHE(pos));str;trattini(RIMPIAZZA(str;INDICE(pos;1);;"-");ESCLUDI(pos;1))))
finito.

Purtroppo non vedo le immagini (forse ho restrizioni qui)

Sicuramente,
ciao.

 

[Luke01]

=SE(C2="";B2;SE(VAL.NUMERO(SOSTITUISCI(B2;"-";"")*1);B2;SOSTITUISCI(B2;ANNULLA.SPAZI(STRINGA.ESTRAI(SOSTITUISCI(B2;"-";RIPETI(" ";30));2*30;30));C2)))

questa formula aggiungendo una colonna in più funziona ! grazie

Ribbon Formule
vai su Gestione Nomi
click su Nuovo
in Nome metti trattini
in Si riferisce a metti =LAMBDA(str;pos;SE(VAL.ERRORE(RIGHE(pos));str;trattini(RIMPIAZZA(str;INDICE(pos;1);;"-");ESCLUDI(pos;1))))
finito.

scusa la domanda banale: ma se rinomino o cambio foglio di lavoro questa resta sempre valida?

[riuniti i messaggi]​

 

[gianfranco55]

ciao
se integri le tue formule con un LET non ti serve la colonna in più
=LET(X;SE(LUNGHEZZA(SOSTITUISCI(A2;" ";""))=15;STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");1;5)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");6;4)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");10;4)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");14;2);SE(LUNGHEZZA(SOSTITUISCI(A2;" ";""))=14;STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");1;5)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");6;3)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");9;4)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");13;2);SE(LUNGHEZZA(SOSTITUISCI(A2;" ";""))=12;SE(VAL.NUMERO(--STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");1;8));STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");1;5)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");6;5)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");11;2);STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");1;5)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");6;3)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");9;4));STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");1;5)&"-"&STRINGA.ESTRAI(SOSTITUISCI(A2;" ";"");6;5))));SE(C2="";X;SE(VAL.NUMERO(SOSTITUISCI(X;"-";"")*1);X;SOSTITUISCI(X;ANNULLA.SPAZI(STRINGA.ESTRAI(SOSTITUISCI(X;"-";RIPETI(" ";30));2*30;30));C2))))

anche se ti consiglierei di valutare RIMPIAZZA() per rendere più leggibile la tua mega formula