Sappiamo che le funzioni TRIM e CLEAN Excel vengono utilizzate per ripulire i caratteri non stampabili e gli spazi extra dalle stringhe, ma non aiutano molto nell'identificare le stringhe contenenti caratteri speciali come @ o ! ecc. In questi casi utilizziamo le UDF.
Quindi, se vuoi sapere se una stringa contiene caratteri speciali, non troverai alcuna formula o funzione di Excel per farlo. Possiamo trovare i caratteri speciali che sono sulla tastiera digitando manualmente ma non puoi sapere se ci sono simboli nella stringa o meno.
La ricerca di caratteri speciali può essere molto importante per la pulizia dei dati. E in alcuni casi, deve essere fatto. Quindi, come lo facciamo in Excel? Come possiamo sapere se una stringa contiene caratteri speciali? Bene, possiamo usare UDF per farlo.
La formula seguente restituirà VERO se una cella contiene caratteri diversi da 1 a 0 e dalla A alla Z (in entrambi i casi). Se non trova alcun carattere speciale, restituirà FALSE.
Formula generica
| =ContieneCaratteri Speciali(stringa) |
Corda: La stringa che si desidera controllare per i caratteri speciali.
Affinché questa formula funzioni, dovrai inserire il codice qui sotto nel modulo della tua cartella di lavoro.
Quindi apri Excel. Premi ALT+F11 per aprire VBE. Inserisci un modulo dal menu Inserisci. Copia il codice qui sotto e incollalo nel modulo.
Funzione Contiene caratteri speciali (str As String) As Boolean For I = 1 To Len(str) ch = Mid(str, I, 1) Seleziona Case ch Case da "0" a "9", da "A" a "Z", "a " A "z", " " ContieneSpecialCharacters = False Case Else ContieneSpecialCharacters = True Exit For End Seleziona Next End Function
Ora la funzione è pronta per essere utilizzata.
Vai al foglio di lavoro nella cartella di lavoro che contiene le stringhe che vuoi controllare.
Scrivi la seguente formula nella cella C2:
| =ContieneCaratteri Speciali(B13) |
Restituisce TRUE per la prima stringa poiché contiene un carattere speciale. Quando copi la formula, mostra FALSE per la stringa B14 e così via.
Ma stranamente mostra TRUE per l'ultima stringa "Exceltip.com". È perché contiene un punto (.). Ma perché così? Esaminiamo il codice per capire.
Come funziona la funzione?
Stiamo iterando attraverso tutti i caratteri della stringa usando il ciclo For e la funzione mid di VBA. La funzione Mid estrae un carattere alla volta dalla stringa e lo memorizza nella variabile ch.
Per I = 1 A Len(str) ch = Mid(str, I, 1)
Ora arriva la parte principale. Usiamo l'istruzione select case per controllare cosa contiene la variabile ch.
Diciamo a VBA di verificare se ch contiene uno dei valori definiti. Ho definito da 0 a 9, dalla A alla Z, dalla a alla z e " " (spazio). Se c
h contiene uno di questi, impostiamo il suo valore su False.
Selezionare Case ch Case da "0" a "9", da "A" a "Z", da "a" a "z", " " Contiene caratteri speciali = False
Puoi vedere che non abbiamo un punto (.) nell'elenco ed è per questo che otteniamo TRUE per la stringa che contiene punto. Puoi aggiungere qualsiasi carattere all'elenco per essere esentato dalla formula.
Se ch contiene un carattere diverso da quelli elencati, impostiamo il risultato della funzione su True e terminiamo il ciclo proprio lì.
Case Else ContieneSpecialCharacters = True Exit For
Quindi sì, ecco come funziona. Ma se vuoi pulire i caratteri speciali, dovrai apportare alcune modifiche alla funzione.
Come pulire i caratteri speciali dalle stringhe in Excel?
Per pulire i caratteri speciali da una stringa in Excel, ho creato un'altra funzione personalizzata in VBA. La sintassi della funzione è:
| =CleanSpecialCharacters(stringa) |
Questa funzione restituisce una versione pulita della stringa passata in essa. La nuova stringa non conterrà nessuno dei caratteri speciali.
Il codice di questa funzione è il seguente:
Funzione CleanSpecialCharacters(str As String) Dim nstr As String nstr = str For I = 1 To Len(str) ch = Mid(str, I, 1) Seleziona Case ch Case "0" To "9", "A" To " Z", "a" A "z", " " 'Non fare nulla Case Else nstr = Replace(nstr, ch, "") End Select Next CleanSpecialCharacters = nstr End Function
Copia questo nello stesso modulo che hai copiato nel codice sopra.
Quando usi questa formula sulle stringhe, ecco come saranno i risultati:
Puoi vedere che ogni carattere speciale viene rimosso dalla stringa incluso il punto.
Come funziona?
Funziona allo stesso modo della funzione sopra. L'unica differenza è che questa funzione sostituisce ogni carattere speciale con un carattere nullo. Forma una nuova stringa e restituisce questa stringa.
Select Case ch Case da "0" a "9", da "A" a "Z", da "a" a "z", " " 'ContainsSpecialCharacters = False Case Else nstr = Replace(nstr, ch, "") End Select
Se vuoi escludere qualsiasi personaggio dalla pulizia, puoi aggiungerlo all'elenco nel codice. Excel perdonerà quel carattere speciale.
Quindi sì ragazzi, ecco come potete trovare e sostituire caratteri speciali da una stringa in Excel. Spero che questo sia stato abbastanza esplicativo e utile. Se non ti aiuta a risolvere il tuo problema, faccelo sapere nella sezione commenti qui sotto.
Come utilizzare la funzione TRIM in Excel: La funzione TRIM viene utilizzata per tagliare le stringhe e pulire eventuali spazi finali o iniziali dalla stringa. Questo ci aiuta molto nel processo di pulizia dei dati.
Come utilizzare la funzione CLEAN in Excel: La funzione Clean viene utilizzata per eliminare i caratteri non stampabili dalla stringa. Questa funzione viene utilizzata principalmente con la funzione TRIM per ripulire i dati estranei importati.
Sostituisci il testo dalla fine di una stringa a partire dalla posizione variabile: Per sostituire il testo dalla fine della stringa, usiamo la funzione REPLACE. La funzione REPLACE utilizza la posizione del testo nella stringa per sostituirlo.
Come verificare se una stringa contiene uno dei tanti testi in Excel: Per verificare se una stringa contiene più testo, usiamo questa formula. Usiamo la funzione SOMMA per sommare tutte le corrispondenze e quindi eseguiamo una logica per verificare se la stringa contiene una delle stringhe multiple.
Conta le celle che contengono testo specifico: Una semplice funzione CONTA.SE farà la magia. Per contare il numero di celle multiple che contengono una data stringa usiamo l'operatore jolly con la funzione CONTA.SE.
Excel SOSTITUISCI vs funzione SOSTITUISCI: Le funzioni SOSTITUISCI e SOSTITUISCI sono le funzioni più fraintese. Per trovare e sostituire un dato testo utilizziamo la funzione SOSTITUISCI. Dove SOSTITUISCI viene utilizzato per sostituire un numero di caratteri nella stringa…
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come utilizzare la funzione CERCA.VERT di Excel| Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come usare Excel Funzione CONTA.SE| Conta i valori con le condizioni usando questa fantastica funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel | Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.