Sostituire il testo con SUBSTITUTE

Spesso i dati richiedono operazioni di ricerca e sostituzione all'interno delle formule: rimuovere i trattini dai numeri di telefono, sostituire una parola o eliminare simboli indesiderati.

La funzione SUBSTITUTE trova una parte di testo all'interno di una cella e sostituisce ogni occorrenza con un nuovo testo, senza modificare la cella originale.

È l'equivalente basato su formule della funzione Trova e sostituisci, ma si aggiorna automaticamente quando cambiano i dati.

SUBSTITUTE accetta fino a quattro argomenti:

• text la stringa o la cella originale
• old_text il testo da trovare
• new_text il testo da inserire al suo posto
• instance_num (facoltativo) l'occorrenza da sostituire

La struttura è =SUBSTITUTE(text, old_text, new_text, [instance_num]). Se omette l'ultimo argomento, vengono sostituite tutte le occorrenze.

Un metodo comune consiste nel sostituire qualcosa con nulla per eliminarlo. Per farlo, imposti new_text su una stringa vuota "".

Se A2 contiene 555-123-4567, la formula seguente rimuove ogni trattino e restituisce 5551234567.

Questo schema è utile per ripulire numeri di telefono, ID e altri valori contenenti segni di punteggiatura.

SUBSTITUTE funziona anche con parole intere, non solo con singoli caratteri.

Se A2 contiene Rapporto annuale 2023, può aggiornare l'anno sostituendo la parola:

Il risultato è Rapporto annuale 2024. Ogni occorrenza corrispondente nella cella viene modificata.

Importante: SUBSTITUTE trova il testo in modo esatto, comprese le maiuscole.

Se A2 contiene Cat cat CAT, allora =SUBSTITUTE(A2, "cat", "dog") modifica solo cat in minuscolo e restituisce Cat dog CAT.

Se deve eseguire sostituzioni senza distinzione tra maiuscole e minuscole, normalizzi prima il testo con LOWER o UPPER, oppure usi le funzioni regex di Sheets nella lezione successiva.

Il quarto argomento facoltativo, instance_num, consente di scegliere una singola occorrenza invece di sostituirle tutte.

Se A2 contiene a-b-c-d e desidera modificare solo il secondo trattino, imposti instance_num su 2.

Il risultato è a-b/c-d: solo il secondo trattino è diventato una barra.

Immagini un importo importato come testo: $1,250.00. Per trasformarlo in un numero utilizzabile, deve rimuovere il simbolo del dollaro e la virgola.

Annidi due chiamate a SUBSTITUTE, una dentro l'altra, per rimuovere entrambi i caratteri.

La chiamata interna rimuove la virgola; quella esterna rimuove il simbolo del dollaro, lasciando 1250.00.

Può annidare tutte le chiamate a SUBSTITUTE necessarie, ciascuna per rimuovere un carattere diverso.

Per normalizzare un codice disordinato contenente spazi, punti e barre e trasformarlo in una sequenza di trattini, concateni tre sostituzioni.

Ogni livello passa il proprio risultato a quello successivo, creando progressivamente la stringa finale ripulita.

Non confonda SUBSTITUTE con la funzione simile REPLACE.

• SUBSTITUTE sostituisce il testo cercandone il contenuto (trovi questa parola e la sostituisca).
• REPLACE sostituisce il testo in base alla posizione (sostituisca 4 caratteri a partire dalla posizione 5).

Usi SUBSTITUTE quando sa che cosa trovare e REPLACE quando sa dove si trova.

Un vantaggio interessante consiste nel combinare SUBSTITUTE con LEN per contare quante volte compare qualcosa.

Sottragga dalla lunghezza originale la lunghezza ottenuta dopo aver rimosso un carattere. Se A2 contiene a,b,c,d, in questo modo conta le virgole e restituisce 3.

Questo trucco è ottimo per contare gli elementi di un elenco delimitato.

Supponga che A2 contenga numeri disordinati con punti e spazi, come 555.123 4567. Desidera ottenere un formato uniforme con trattini: 555-123-4567.

Per prima cosa rimuova i punti e gli spazi, poi potrebbe reinserire i trattini; per ora, si limiti a uniformare i separatori sostituendoli con trattini.

L'annidamento delle sostituzioni uniforma qualsiasi separatore indesiderato sia presente.

Ha imparato a trovare e sostituire il testo nelle formule:

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Usi "" come new_text per eliminare i caratteri.
• La funzione distingue tra maiuscole e minuscole; se necessario, normalizzi prima il testo.
• L'argomento facoltativo instance_num consente di scegliere una singola occorrenza.
• Annidi le chiamate per ripulire più caratteri; combini la funzione con LEN per contare le occorrenze.

Ha completato la sezione Concatenazione e modifica del testo. Ottimo lavoro nella creazione di dati puliti e coerenti!