Come trovare caratteri speciali in una stringa in Excel
Sappiamo che link: / excel-text-formas-excel-trim-function [TRIM]
e link: / excel-text-formas-how-to-use-the-clean-function-in-excel [CLEAN]
Le funzioni di Excel vengono utilizzate per ripulire caratteri non stampabili e spazi extra dalle stringhe, ma non aiutano molto nell’identificare le stringhe contenenti caratteri speciali come @ o! ecc. In questi casi usiamo 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 digitandoli manualmente ma non puoi sapere se ci sono simboli nella stringa o meno.
Trovare 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 da A a Z (in entrambi i casi). Se non trova caratteri speciali, restituirà FALSE.
Formula generica
|
=ContainsSpecialCharacters(string) |
Stringa: la stringa di cui si desidera controllare i caratteri speciali.
Affinché questa formula funzioni, dovrai inserire il codice seguente nel modulo della tua cartella di lavoro.
Quindi apri Excel. Premere ALT + F11 per aprire VBE. Inserisci un modulo dal menu Inserisci. Copia il codice sottostante e incollalo nel modulo.
Function ContainsSpecialCharacters(str As String) As Boolean For I = 1 To Len(str) ch = Mid(str, I, 1) Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " ContainsSpecialCharacters = False Case Else ContainsSpecialCharacters = True Exit For End Select 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:
|
=ContainsSpecialCharacters(B13) |
Restituisce TRUE per la prima stringa poiché contiene un carattere speciale. Quando copi la formula verso il basso, 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 tutti i caratteri della stringa usando link: / vba-for-loops-with-7-examples [For loop]
e la funzione intermedia di VBA. La funzione Mid estrae un carattere alla volta dalla stringa e lo memorizza nella variabile ch.
For I = 1 To Len(str) ch = Mid(str, I, 1)
Ora arriva la parte principale. Usiamo l’istruzione select case per verificare cosa contiene la variabile ch.
Diciamo a VBA di controllare se ch contiene uno dei valori definiti. Ho definito da 0 a 9, da A a Z, da aaz e “” (spazio). Se c h contiene uno di questi, impostiamo il suo valore su False.
Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " ContainsSpecialCharacters = False
Puoi vedere che non abbiamo un punto (.) Nell’elenco ed è per questo che otteniamo TRUE per la stringa che contiene il punto. È possibile aggiungere qualsiasi carattere all’elenco per essere esentati dalla formula.
Se ch contiene un carattere diverso dai caratteri elencati, impostiamo il risultato della funzione su True e terminiamo il ciclo proprio lì.
Case Else ContainsSpecialCharacters = True Exit For
Quindi sì, è così che 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(string) |
Questa funzione restituisce una versione pulita della stringa passata al suo interno.
La nuova stringa non conterrà nessuno dei caratteri speciali. Il codice di questa funzione è questo:
Function CleanSpecialCharacters(str As String) Dim nstr As String nstr = str For I = 1 To Len(str) ch = Mid(str, I, 1) Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " 'Do nothing 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 come la funzione precedente. 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 "0" To "9", "A" To "Z", "a" To "z", " " 'ContainsSpecialCharacters = False Case Else nstr = Replace(nstr, ch, "") End Select
Se vuoi escludere qualsiasi carattere dalla pulizia, puoi aggiungerlo all’elenco nel codice. Excel perdonerà quel carattere speciale.
Quindi sì ragazzi, è così che puoi 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.
Articoli correlati:
link: / excel-text-formas-excel-trim-function [Come usare 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.
link: / excel-text-formas-how-to-use-the-clean-function-in-excel [Come usare la funzione CLEAN in Excel]: La funzione Clean viene utilizzata per ripulire i caratteri non stampabili dalla stringa. Questa funzione viene utilizzata principalmente con la funzione TRIM per pulire i dati esterni importati.
link: / excel-text-replace-text-from-end-of-a-string-from-variable-position [Sostituisci il testo dalla fine di una stringa a partire dalla posizione variabile]: * Per sostituire il testo dalla fine di la stringa, usiamo la funzione REPLACE.
La funzione REPLACE utilizza la posizione del testo nella stringa per sostituirla.
link: / excel-text-formas-check-a-list-of-text-in-string [How to Check if a string contains one of many text in Excel]: * To find check if a string contains any of testo multiplo, usiamo questa formula. Usiamo la funzione SUM per sommare tutte le corrispondenze e quindi eseguiamo una logica per verificare se la stringa contiene una delle stringhe multiple.
link: / counting-count-cells-that-contain-specific-text [Count Cells that contain specific text]: * Una semplice funzione COUNTIF farà la magia. Per contare il numero di più celle che contengono una determinata stringa utilizziamo l’operatore jolly con la funzione CONTA.SE.
link: / excel-text-formas-excel-replace-vs-substitute-function [Excel REPLACE vs SUBSTITUTE function]: * Le funzioni REPLACE e SUBSTITUTE sono le funzioni più fraintese. Per trovare e sostituire un determinato testo utilizziamo la funzione SOSTITUISCI. Dove REPLACE è usato per sostituire un numero di caratteri nella stringa…
Articoli popolari:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentare-la-produttività [50 scorciatoie Excel per aumentare la produttività] | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [Come usare la funzione CERCA.VERT di Excel] | Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare valori da diversi intervalli e fogli. link: / tips-countif-in-microsoft-excel [Come usare]
link: / formule-e-funzioni-introduzione-della-funzione-vlookup [Excel]
link: / tips-countif-in-microsoft-excel [Funzione CONTA.SE] | Contare i valori con le condizioni utilizzando questa straordinaria funzione.
Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel] | Questa è un’altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.