Formula generica
=MATCH(1,EXACT(LEFT(A1,255),LEFT(rng,255))*EXACT(MID(A1,256,255),MID(rng,256,255)),0)
Sommario
Per abbinare testo più lungo di 255 caratteri con la funzione CONFRONTA, è possibile utilizzare le funzioni SINISTRA, MEDIA ed ESATTA per analizzare e confrontare il testo, come spiegato di seguito. Nell'esempio mostrato, la formula in G5 è:
=MATCH(1,EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255)),0)
dove i dati sono l'intervallo denominato B5: B15.
Nota: questa formula esegue un confronto con distinzione tra maiuscole e minuscole.
Spiegazione
La funzione CONFRONTA ha un limite di 255 caratteri per il valore di ricerca. Se provi a utilizzare un testo più lungo, CONFRONTA restituirà un errore #VALORE.
Per aggirare questo limite è possibile utilizzare la logica booleana e le funzioni LEFT, MID ed EXACT per analizzare e confrontare il testo.
La stringa con cui stiamo testando nella cella E5 è di 373 caratteri come segue:
Lorem ipsum dolor amet ha messo un uccellino su di esso fondo fiduciario di listicle, diritti di bicicletta unicorno vaporware probabilmente non hai sentito parlare di loro baffi. Forage helvetica crusty semiotica in realtà cimelio di famiglia. Tumblr poutine unicorn godard try-hard prima che esaurissero narvalo meditazione kitsch gilet fixie twee letteralmente felpa con cappuccio retrò. Borsa a tracolla hell of crusty green juice artisan.
In sostanza, questa è solo una formula MATCH, impostata per cercare 1 in modalità corrispondenza esatta:
=MATCH(1,array,0)
La matrice nella formula sopra contiene solo 1 e 0 e 1 rappresenta il testo corrispondente. Questo array è costruito dalla seguente espressione:
EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255))
Questa stessa espressione ha due parti. A sinistra abbiamo:
EXACT(LEFT(E5,255),LEFT(data,255)) // compare first 255 chars
Qui, la funzione SINISTRA estrae i primi 255 caratteri da E5 e da tutte le celle nei dati dell'intervallo denominato (B5: B15). Poiché i dati contengono 11 stringhe di testo, LEFT genererà 11 risultati.
La funzione EXACT confronta quindi la singola stringa da E5 con tutte le 11 stringhe restituite da LEFT. ESATTO restituisce 11 risultati in una matrice come questa:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
A destra, abbiamo un'altra espressione:
EXACT(MID(E5,256,255),MID(data,256,255) // compare next 255 chars
Questo è esattamente lo stesso approccio usato con LEFT, ma qui usiamo la funzione MID per estrarre i successivi 255 caratteri di testo. La funzione ESATTO restituisce nuovamente 11 risultati:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Quando i due array sopra vengono moltiplicati l'uno per l'altro, l'operazione matematica forza i valori VERO FALSO in 1 e 0. Seguendo le regole dell'aritmetica booleana, il risultato è un array come questo:
(0;0;0;0;0;0;0;0;0;1;0)
che viene restituito direttamente a MATCH come array di ricerca. La formula può ora essere risolta in:
=MATCH(1,(0;0;0;0;0;0;0;0;0;1;0),0)
La funzione CONFRONTA esegue una corrispondenza esatta e restituisce un risultato finale di 10, che rappresenta la decima stringa di testo in B5: B15.
Nota: la lunghezza del testo mostrata nell'esempio è calcolata con la funzione LEN. Appare solo per riferimento.
Opzione case-insensitive
La funzione ESATTO distingue tra maiuscole e minuscole, quindi la formula sopra rispetterà le maiuscole e le minuscole.
Per eseguire una corrispondenza senza distinzione tra maiuscole e minuscole con testo lungo, utilizzare le funzioni ISNUMBER e SEARCH come segue:
=MATCH(1,ISNUMBER(SEARCH(LEFT(E5,255),LEFT(data,255)))*ISNUMBER(SEARCH(MID(E5,256,255),MID(data,256,255))),0)
La struttura generale di questa formula è identica all'esempio precedente, ma per confrontare il testo viene utilizzata la funzione CERCA invece di ESATTA (spiegata in dettaglio qui).
A differenza di EXACT, la funzione SEARCH supporta anche i caratteri jolly.
Con XMATCH
La funzione XMATCH non ha lo stesso limite di 255 caratteri di MATCH. Per eseguire una corrispondenza simile su testo lungo con XMATCH, puoi utilizzare la formula molto più semplice di seguito:
=XMATCH(E5,data)
Nota: XMATCH supporta i caratteri jolly, ma non fa distinzione tra maiuscole e minuscole.
