Utilizzo della funzione di suddivisione VBA
La funzione VBA Split consente di separare le parti del componente all'interno di una stringa di testo standard in cui ogni componente utilizza un carattere delimitatore specifico, ad es. una virgola o due punti. È più facile da usare che scrivere codice per cercare i delimitatori nella stringa e quindi estrarre i valori.
Potrebbe essere usato se stai leggendo in una riga da un valore separato da virgole (file CSV) o hai un indirizzo postale che è tutto su una riga, ma vuoi vederlo come più righe.
La sintassi è:
| 1 | Espressione divisa, delimitatore[facoltativo], limite[facoltativo], confronto[facoltativo] |
La funzione VBA Split ha quattro parametri:
- Espressione - La stringa di testo che desideri suddividere in diverse parti.
- delimitatore (opzionale)- stringa o carattere non stampabile - Definisce il carattere delimitatore che verrà utilizzato per la suddivisione. Se non viene fornito alcun carattere delimitatore, viene utilizzato il valore predefinito di uno spazio.
- Limite (opzionale) - numero - Definisce quante divisioni verranno effettuate. Se vuoto, tutte le suddivisioni disponibili verranno eseguite all'interno della stringa. Se è impostato su 1, non verranno effettuate divisioni. Fondamentalmente, ti consente di separare un numero specifico di valori a partire dall'inizio della stringa, ad es. dove la corda è molto lunga e hai bisogno solo delle prime tre spaccature.
- Confrontare (opzionale) - Se il delimitatore è un carattere di testo, questo viene utilizzato per alternare se il delimitatore fa distinzione tra maiuscole e minuscole o meno. I valori sono vbBinaryCompare ( distinzione tra maiuscole e minuscole) e vbTextCompare (senza distinzione tra maiuscole e minuscole).
La funzione split restituisce sempre un array.
Semplice esempio della funzione di divisione
| 123456789101112 | Sub SplitEsempio()'Definisci variabiliDim MyArray() As String, MyString As String, I As Variant'Stringa di esempio con delimitatori di spazioMyString = "Uno Due Tre Quattro"'Usa la funzione Dividi per dividere le parti componenti della stringaMyArray = Dividi(MyString)'ripeti attraverso l'array creato per mostrare ogni valorePer ogni io in MyArrayMsgBox IAvanti ioFine sottotitolo |
In questo esempio, non viene specificato alcun delimitatore perché tutte le parole hanno uno spazio tra di loro, quindi è possibile utilizzare il delimitatore predefinito (spazio).
L'array non ha dimensioni ed è impostato come stringa. La variabile I, che viene utilizzata nel ciclo For… Next, deve essere quotata come variante.
Quando questo codice viene eseguito, mostrerà quattro finestre di messaggio, una per ciascuna delle suddivisioni, ad es. Uno due tre. Quattro.
Nota che se c'è un doppio spazio tra le parole nella stringa, questo verrà valutato come una divisione, anche se non contiene nulla. Questo potrebbe non essere il risultato che vuoi vedere.
Puoi risolvere questo problema utilizzando la funzione Sostituisci per sostituire eventuali spazi doppi con uno spazio singolo:
| 1 | MyString = Sostituisci(MyString, " ", " ") |
Uno spazio finale o iniziale può anche causare problemi producendo una divisione vuota. Questi sono spesso molto difficili da vedere. Puoi rimuovere questi spazi estranei utilizzando la funzione Taglia:
| 1 | MiaStringa = Taglia(MiaStringa) |
Utilizzo della funzione di divisione con un carattere delimitatore
Possiamo usare un delimitatore di un punto e virgola (;). Questo si trova spesso nelle stringhe degli indirizzi di posta elettronica per separare gli indirizzi. Potresti ricevere un'e-mail che è condivisa con un numero di colleghi e vuoi vedere un elenco nel tuo foglio di lavoro di chi è andato. Puoi facilmente copiare gli indirizzi e-mail dalle caselle e-mail "A" o "Copia" e nel tuo codice.
| 123456789101112131415 | Sub SplitByPunto e virgolaEsempio()'Definisci variabiliDim MyArray() As String, MyString As String, I As Variant, N As Integer'Stringa di esempio con delimitatori di punto e virgolaMyString = "[email protected];[email protected];[email protected];[email protected]"'Usa la funzione Dividi per dividere le parti componenti della stringaMyArray = Dividi(MyString, ";")"Cancella il foglio di lavoro"ActiveSheet.UsedRange.Clear'itera attraverso l'arrayPer N = 0 a UBound(MyArray)'Inserisci ogni indirizzo email nella prima colonna del foglio di lavoroIntervallo("A" & N + 1).Valore = MyArray(N)Successivo NFine sottotitolo |
Si noti che un ciclo For… Next viene utilizzato per scorrere l'array. Il primo elemento dell'array inizia sempre da zero e la funzione Upper Bound viene utilizzata per ottenere il numero massimo di elementi.
Dopo aver eseguito questo codice, il tuo foglio di lavoro sarà simile a questo:
Utilizzo di un parametro limite in una funzione di divisione
Il parametro limit consente di eseguire un numero specifico di suddivisioni dall'inizio della stringa. Sfortunatamente, non puoi fornire una posizione di partenza o una serie di divisioni da fare, quindi è abbastanza semplice. Puoi creare il tuo codice VBA per creare una funzione per farlo, e questo verrà spiegato più avanti in questo articolo.
| 123456789101112131415 | Sub SplitWithLimitExample()'Crea variabiliDim MyArray() As String, MyString As String, I As Variant, N As Integer'Stringa di esempio con delimitatori di virgolaMyString = "Uno,Due,Tre,Quattro,Cinque,Sei"'Usa la funzione Dividi per dividere le parti componenti della stringaMyArray = Dividi(MyString, ",", 4)"Cancella il foglio di lavoro"ActiveSheet.UsedRange.Clear'Itera attraverso l'arrayPer N = 0 a UBound(MyArray)'Posiziona ogni suddivisione nella prima colonna del foglio di lavoroIntervallo("A" & N + 1).Valore = MyArray(N)Successivo NFine sottotitolo |
Dopo aver eseguito questo codice, il tuo foglio di lavoro sarà simile a questo:
Solo i primi tre valori di suddivisione vengono visualizzati separatamente. Gli ultimi tre valori vengono visualizzati come una stringa lunga e non vengono divisi.
Se si sceglie un valore limite maggiore del numero di delimitatori all'interno di una stringa, non verrà generato un errore. La stringa verrà suddivisa in tutte le sue parti componenti come se il valore limite non fosse stato fornito.
Utilizzo del parametro di confronto in una funzione di divisione
Il parametro Compare determina se il delimitatore fa distinzione tra maiuscole e minuscole o meno. Questo non è applicabile se i delimitatori sono virgole, punto e virgola o due punti.
Nota: invece, puoi sempre posizionare Opzione Confronta testo <> nella parte superiore del modulo per eliminare la distinzione tra maiuscole e minuscole per l'intero modulo.
| 123456789101112131415 | Sub SplitByCompareExample()'Crea variabiliDim MyArray() As String, MyString As String, I As Variant, N As Integer'Stringa di esempio con delimitatori XMyString = "OneXTwoXthreexFourXFivexSix"'Usa la funzione Dividi per dividere le parti componenti della stringaMyArray = Split(MyString, "X", , vbBinaryCompare)"Cancella il foglio di lavoro"ActiveSheet.UsedRange.Clear'itera attraverso l'arrayPer N = 0 a UBound(MyArray)'Posiziona ogni suddivisione nella prima colonna del foglio di lavoroIntervallo("A" & N + 1).Valore = MyArray(N)Successivo NFine sottotitolo |
In questo esempio, la stringa da dividere utilizza il carattere "X" come delimitatore. Tuttavia, in questa stringa, c'è una combinazione di caratteri "X" maiuscoli e minuscoli. Il parametro Confronta nella funzione Dividi utilizza un carattere "X" maiuscolo.
Se il parametro Compare è impostato su vbBinaryCompare, i caratteri "x" minuscoli verranno ignorati e il foglio di lavoro avrà questo aspetto:
Se il parametro Compare è impostato su vbTextCompare, nella suddivisione verranno utilizzati i caratteri "x" minuscoli e il foglio di lavoro sarà simile a questo:
Si noti che il valore della cella A6 viene troncato perché contiene un carattere "x" minuscolo. Poiché la divisione non fa distinzione tra maiuscole e minuscole, qualsiasi delimitatore che fa parte di una sottostringa causerà una divisione.
Questo è un punto importante da tenere a mente quando si utilizza un delimitatore di testo e vbTextCompare. Puoi facilmente finire con il risultato sbagliato.
Utilizzo di caratteri non stampabili come carattere delimitatore
È possibile utilizzare caratteri non stampabili come delimitatore, ad esempio un ritorno a capo (un'interruzione di riga).
Qui usiamo il vbCr per specificare un ritorno a capo <>
| 123456789101112131415 | Sub SplitByNonPrintableExample()'Crea variabiliDim MyArray() As String, MyString As String, I As Variant, N As Integer'Stringa di esempio con delimitatori di ritorno a capoMyString = "Uno" & vbCr & "Due" & vbCr & "Tre" & vbCr & "Quattro" & vbCr & "Cinque" & vbCr & "Sei"'Usa la funzione Dividi per dividere le parti componenti della stringaMyArray = Split(MyString, vbCr, , vbTextCompare)"Cancella il foglio di lavoro"ActiveSheet.UsedRange.Clear'Itera attraverso l'arrayPer N = 0 a UBound(MyArray)'Posiziona ogni suddivisione nella prima colonna del foglio di lavoroIntervallo("A" & N + 1).Valore = MyArray(N)Successivo NFine sottotitolo |
In questo esempio, viene creata una stringa utilizzando vbCr (carattere di ritorno a capo) come delimitatore.
Quando questo codice viene eseguito, il tuo foglio di lavoro sarà simile a questo:
Utilizzo della funzione di unione per invertire una divisione
La funzione Join riunirà tutti gli elementi di un array, ma utilizzando un delimitatore specificato. Se non viene specificato alcun carattere di delimitazione, verrà utilizzato uno spazio.
| 123456789101112131415 | Esempio di join secondario()'Crea variabiliDim MyArray() As String, MyString As String, I As Variant, N As IntegerDim Target As String'Stringa di esempio con delimitatori di virgolaMyString = "Uno,Due,Tre,Quattro,Cinque,Sei""Posiziona MyString nella cella A1"Range("A1").Value = MyString'Usa la funzione Dividi per dividere le parti componenti della stringaMyArray = Dividi(MyString, ",")'Usa la funzione Join per ricreare la stringa originale usando un delimitatore di punti e virgolaTarget = Join(MyArray,”;”)'Posiziona la stringa del risultato nella cella A2'Intervallo ("A2"). Valore = ObiettivoFine sottotitolo |
Questo codice divide una stringa con delimitatori di virgola in una matrice e la unisce nuovamente utilizzando delimitatori di punto e virgola.
Dopo aver eseguito questo codice, il tuo foglio di lavoro sarà simile a questo:
La cella A1 ha la stringa originale con delimitatori di virgola e la cella A2 ha la nuova stringa unita con delimitatori di punto e virgola.
Utilizzo della funzione Dividi per eseguire un conteggio delle parole
Tenendo presente che una variabile stringa in Excel VBA può essere lunga fino a 2 Gb, è possibile utilizzare la funzione di divisione per eseguire il conteggio delle parole in una parte di testo. Ovviamente Microsoft Word lo fa automaticamente, ma questo potrebbe essere utile per un semplice file di testo o testo copiato da un'altra applicazione.
| 1234567891011121314 | Sub NumeroDiParoleEsempio()'Crea variabiliDim MyArray() As String, MyString As String'Stringa di esempio con delimitatori di spazioMyString = "Uno Due Tre Quattro Cinque Sei"'Rimuovi eventuali doppi spaziMyString = Sostituisci(MyString, " ", " ")'Rimuovi eventuali spazi iniziali o finaliMiaStringa = Taglia(MiaStringa)'Usa la funzione Dividi per dividere le parti componenti della stringaMyArray = Dividi(MyString)'Mostra il numero di parole usando la funzione UBoundMsgBox "Numero di parole" & UBound(MyArray) + 1Fine sottotitolo |
Uno dei pericoli di questo codice di conteggio delle parole è che verrà generato da spazi doppi e spazi iniziali e finali. Se sono presenti, verranno conteggiate come parole extra e il conteggio delle parole risulterà impreciso.
Il codice utilizza le funzioni Sostituisci e Taglia per rimuovere questi spazi aggiuntivi.
La riga di codice finale visualizza il numero di parole trovate utilizzando la funzione UBound per ottenere il numero massimo di elementi dell'array e quindi incrementandolo di 1. Questo perché il primo elemento dell'array inizia da zero.
Dividere un indirizzo in celle del foglio di lavoro
Gli indirizzi di posta sono spesso lunghe stringhe di testo con delimitatori di virgole. Potresti voler dividere ogni parte dell'indirizzo in una cella separata.
| 123456789101112131415 | SottoindirizzoEsempio()'Crea variabiliDim MyArray() As String, MyString As String, N As Integer'Imposta stringa con indirizzo Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Usa la funzione di divisione per dividere la stringa usando un delimitatore di virgolaMyArray = Dividi(MyString, ",")"Cancella il foglio di lavoro"ActiveSheet.UsedRange.Clear'itera attraverso l'arrayPer N = 0 a UBound(MyArray)'Posiziona ogni suddivisione nella prima colonna del foglio di lavoroIntervallo("A" & N + 1).Valore = MyArray(N)Successivo NFine sottotitolo |
L'esecuzione di questo codice utilizzerà il delimitatore di virgola per inserire ogni riga dell'indirizzo in una cella separata:
Se desideri restituire solo il codice postale (ultimo elemento dell'array), puoi utilizzare il codice:
| 123456789101112 | SottoindirizzoCAPEsempio()'Crea variabiliDim MyArray() As String, MyString As String, N As Integer, Temp As String'Imposta stringa con indirizzo Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Usa la funzione di divisione per dividere la stringa usando un delimitatore di virgolaMyArray = Dividi(MyString, ",")"Cancella il foglio di lavoro"ActiveSheet.UsedRange.Clear'Metti il CAP alla cella A1'Range("A1").Value = MyArray(UBound(MyArray))Fine sottotitolo |
Questo utilizzerà solo l'ultimo elemento dell'array, che si trova utilizzando la funzione UBound.
D'altra parte, potresti voler vedere tutte le righe in una cella in modo che possano essere stampate su un'etichetta di indirizzo:
| 1234567891011121314151617 | SottoindirizzoEsempio()'Crea variabiliDim MyArray() As String, MyString As String, N As Integer, Temp As String'Imposta stringa con indirizzo Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Usa la funzione di divisione per dividere la stringa usando un delimitatore di virgolaMyArray = Dividi(MyString, ",")"Cancella il foglio di lavoro"ActiveSheet.UsedRange.Clear'itera attraverso l'arrayPer N = 0 a UBound(MyArray)'inserisci ogni elemento dell'array più un carattere di avanzamento riga in una stringaTemp = Temp & MyArray(N) & vbLfSuccessivo N'Metti la stringa sul foglio di lavoroIntervallo ("A1") = TempFine sottotitolo |
Questo esempio funziona allo stesso modo del precedente, tranne per il fatto che crea una stringa temporanea di tutti gli elementi dell'array, ma inserendo un carattere di avanzamento riga dopo ogni elemento.
Il foglio di lavoro sarà simile a questo dopo l'esecuzione del codice:
Dividi stringa in celle del foglio di lavoro
Puoi copiare l'array Dividi nelle celle del foglio di lavoro <> con un solo comando:
| 12345678910 | Sub CopyToRange()'Crea variabiliDim MyArray() As String, MyString As String'Stringa di esempio con delimitatori di spazioMyString = "Uno,Due,Tre,Quattro,Cinque,Sei"'Usa la funzione Dividi per dividere le parti componenti della stringaMyArray = Dividi(MyString, ",")'Copia l'array nel foglio di lavoroRange("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)Fine sottotitolo |
Quando questo codice è stato eseguito, il tuo foglio di lavoro sarà simile a questo:
Creazione di una nuova funzione per consentire la divisione da un dato punto
Il parametro Limit nella funzione Split consente solo di specificare un limite superiore in cui si desidera interrompere la divisione. Inizia sempre dall'inizio della stringa.
Sarebbe molto utile avere una funzione simile in cui puoi specificare il punto di inizio della divisione all'interno della stringa e il numero di divisioni che vuoi vedere da quel punto in poi. Inoltre, estrarrà solo le divisioni che hai specificato nell'array, invece di avere un enorme valore di stringa come ultimo elemento nell'array.
Puoi facilmente creare una funzione (chiamata SplitSlicer) in VBA per farlo:
| 123456789101112131415161718192021222324 | Funzione SplitSlicer(Target As String, Del As String, Start As Integer, N As Integer)'Crea variabile arrayDim MyArray() As String'Cattura la suddivisione utilizzando la variabile di inizio utilizzando il carattere delimitatoreMyArray = Dividi (Obiettivo, Canc, Inizio)‘Controllare se il parametro di partenza è maggiore del numero di intertempi - questo può causare problemiSe Start > UBound(MyArray) + 1 Allora‘Visualizza errore ed esci dalla funzioneMsgBox "Il parametro di inizio è maggiore del numero di suddivisioni disponibili"SplitSlicer = MyArrayEsci dalla funzioneFinisci se'Metti l'ultimo elemento dell'array nella stringaDestinazione = MyArray(UBound(MyArray))'Dividi la stringa usando N come limite'MyArray = Dividi (Obiettivo, Del, N)'Verifica che il limite superiore sia maggiore di zero poiché il codice rimuove l'ultimo elementoSe UBound(MyArray) > 0 Allora'Usa ReDim per rimuovere l'elemento finale dell'arrayReDim Preserve MyArray(UBound(MyArray) - 1)Finisci se'Restituisci il nuovo arraySplitSlicer = MyArrayFine funzione |
Questa funzione è costruita con quattro parametri:
- Obbiettivo - stringa - questa è la stringa di input che vuoi dividere
- Del - stringa o carattere non stampabile - questo è il carattere delimitatore che usi, ad es. virgola, due punti
- Cominciare - numero - questa è la suddivisione iniziale per la tua fetta
- n - numero - questo è il numero di divisioni che vuoi fare all'interno della tua fetta
Nessuno di questi parametri è facoltativo o ha valori predefiniti, ma puoi integrarlo nel codice per la funzione se desideri estenderlo ulteriormente.
La funzione utilizza la funzione Split per creare un array utilizzando il parametro Start come Limit. Ciò significa che gli elementi dell'array manterranno le divisioni fino al parametro di inizio, ma il resto della stringa sarà l'ultimo elemento e non verrà diviso.
L'ultimo elemento dell'array viene trasferito di nuovo in una stringa utilizzando la funzione UBound per determinare di quale elemento si tratta.
La stringa viene quindi nuovamente suddivisa nell'array, utilizzando N come variabile limite. Ciò significa che le divisioni verranno eseguite per la stringa fino alla posizione N, dopodiché il resto della stringa formerà l'ultimo elemento nell'array.
L'istruzione ReDim viene utilizzata per rimuovere l'ultimo elemento poiché vogliamo solo gli elementi specifici rimasti nell'array. Si noti che viene utilizzato il parametro Preserve, altrimenti tutti i dati nell'array andranno persi.
Il nuovo array viene quindi restituito al codice da cui è stato chiamato.
Si noti che il codice è "a prova di errore". Gli utenti faranno spesso cose strane che non hai considerato. Ad esempio, se provano a utilizzare la funzione con il parametro Start o N maggiore del numero disponibile di suddivisioni nella stringa, è probabile che la funzione non riesca.
Il codice è incluso per controllare il valore Start e anche per assicurarsi che sia presente un elemento che può essere rimosso quando l'istruzione ReDim viene utilizzata nell'array.
Ecco il codice per testare la funzione:
| 123456789101112 | Sub TestSplitSlicer()'Crea variabiliDim MyArray() As String, MyString As String'Definisci stringa di esempio con delimitatori di virgolaMyString = "Uno,Due,Tre,Quattro,Cinque,Sei,Sette,Otto,Nove,Dieci"'Usa la funzione Splitslicer per definire un nuovo arrayMyArray = SplitSlicer(MyString, ",", 4, 3)'Cancella il foglio attivoActiveSheet.UsedRange.Clear'Copia l'array nel foglio di lavoroRange("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)Fine sottotitolo |
Esegui questo codice e il tuo foglio di lavoro sarà simile a questo:
