.st0{fill:#FFFFFF;}

Excel Esperto, Macro e VBA

Excel testo in colonne con le macro

 Maggio 25, 2020

By  Damiano Causale

  • Home
  • Excel testo in colonne con le macro

In un precedente articolo ti ho mostrato come utilizzare il comando Excel Testo in colonne. In particolare si trattava di suddividere il testo presente in un'unica cella in celle differenti.

In questo articolo affronteremo lo stesso argomento in modo differente: attraverso una macro Excel.

Excel testo in colonne: come scrivere una macro

Scommetto che ti sarai trovato almeno una volta nella situazione in cui hai inserito nella stessa cella il nome ed il cognome e ad un certo punto hai avuto l'esigenza di separare i valori in celle differenti.

Excel testo in colonne: come scrivere una macro

È una situazione normale, è capitata anche a me e per questo che voglio proporti una soluzione per risolvere questa circostanza.

In Excel puoi trovare tanti strumenti adatti alle tue soluzioni. Alcuni pronti all'uso, altri che devono essere creati. Ad esempio, mi vengono in mente le funzioni nidificate o il comando Testo in colonne o, ancora, lo strumento Power Query.

Le potenzialità di Excel sono enormi.

Il limite è solo la tua fantasia.

E quando non ti accontenti degli strumenti che Excel ti offre, te li vai a creare con il linguaggio delle macro: il VBA.

La conoscenza di Excel passa anche attraverso la curiosità. La voglia di utilizzare strumenti diversi per trovare soluzioni differenti.

Ecco perché voglio proporti questa macro Excel per permetterti di separare il testo in celle differenti.

Passo dopo passo ti aiuterò a costruire uno nuovo e utile strumento.

Ora, procediamo con la costruzione della nostra macro Excel.

Assegnare il nome alla macro e dichiarare le variabili

Il primo passo è relativo alla dichiarazione delle variabili.

La variabile Testo, di tipo Variant, farà riferimento alla cella selezionata contenente il testo da separare in colonne.

La variabile i e la variabile s sono ti tipo Integer ovvero di tipo numerico.

Assegnare il nome alla macro e dichiarare le variabili

Utilizzare l’istruzione With … End With

Per ridurre la lunghezza del codice utilizziamo l’istruzione With … End With.

Utilizzare l’istruzione With … End With

Aggiunta del ciclo For … Next

Ora inseriamo il ciclo For … Next per consentire la ripetizione delle operazioni per tutte le celle che contengono dei valori.

Aggiunta del ciclo For … Next

Separare il contenuto delle celle con la funzione Split VBA

La funzione SPLIT di Excel permette di suddividere una stringa in sotto stringhe sulla base di un delimitatore.

Il risultato sarà una matrice (Array) di sotto stringhe.

Il delimitatore nel nostro esempio è rappresentato da uno spazio.

Separare il contenuto delle celle con la funzione Split VBA

Excel testo in colonne con l’aggiunta del nuovo ciclo For … Next

Un ulteriore ciclo For … Next ci consentirà di separare il testo in colonne sulla base degli spazi.

Excel testo in colonne con l’aggiunta del nuovo ciclo For … Next

Excel testo in colonne: come funziona la macro

Cerchiamo ora di sintetizzare il percorso.

Il ragionamento è il seguente.

Partiamo dalla selezione della prima cella (nell'esempio la cella B3).

Separiamo il contenuto della cella e verifichiamo quanti spazi vi sono contenuti all'interno.

Excel testo in colonne: come funziona la macro

La variabile Testo (che rappresenta la nostra matrice) conterrà tutti gli elementi della cella separati in base agli spazi. Infatti, attraverso la funzione Split possiamo ottenere due risultati: separare le parole e contare gli spazi.

Ciascun elemento sarà identificato da un numero a partire da zero. Pertanto il primo elemento avrà un indice 0, il secondo elemento un indice 1, il terzo elemento un indice 2 e così via a seguire.

Excel testo in colonne: come funziona la macro

Gli spazi ci aiutano a capire quante parole sono contenute nella cella selezionata. Ad esempio, ad uno spazio corrispondono due parole, a due spazi corrispondono tre parole, e così via.

Tuttavia, se in una cella son contenute tre parole ovvero 2 spazi come possiamo ottenere il numero di celle necessarie per accogliere ciascuna parola?

Ma soprattutto come è possibile ottenere il numero delle celle dal conteggio degli spazi?

La risposta sta proprio nella modalità del conteggio.

Infatti, se il conteggio parte da zero ciò significa che è possibile verificare la seguente uguaglianza:

Excel testo in colonne: come funziona la macro

La ragione è semplice.

Grazie alla seguente tabella non saranno necessarie grandi spiegazioni.

Excel testo in colonne: come funziona la macro

Grazie ai due cicli For ... Next possiamo ripetere la stessa operazione per tutte le celle al di sotto di quella selezionata.

Per ottenere il conteggio del numero delle celle in basso utilizziamo la seguente porzione di codice.
Excel testo in colonne: come funziona la macro

Dove:

  • ActiveCell.Row ci consente di ottenere il numero di riga della cella attiva.
  • ActiveCell.Column ci consente di ottenere il numero di colonna della cella attiva.

Il conteggio totale degli spazi è ottenuto grazie alla funzione UBound. Quest’ultima restituisce il limite superiore della matrice Testo.

Dalle seguenti immagini potrai osservare i valori assunti durante l’esecuzione della macro. In questo modo ti sarà più semplice capirne il funzionamento.

Excel testo in colonne: come funziona la macro

Un’ultima osservazione.

All'interno del codice noterai la proprietà Offset. Essa è la corrispondente funzione SCARTO Excel nel linguaggio VBA.

La proprietà Offset consente di determinare la posizione delle celle rispetto a quella di partenza in cui inserire il testo in base a un determinato numero di righe e colonne.

Il risultato finale sarà il seguente:

Excel testo in colonne: come funziona la macro

La macro è dinamica e può essere applicata a qualsiasi intervallo.

Spero che questo articolo ti sia utile.

Buon lavoro!

Vuoi saperne di più sulle macro Excel?

Allora potrebbero interessarti i seguenti articoli:

Damiano Causale


Mi chiamo Damiano Causale, istruttore Microsoft certificato, per lavoro aiuto aziende e privati nell'utilizzo di Excel.

Damiano Causale

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}