Come dividere le celle in Excel (separare in più colonne)

Ci possono essere situazioni in cui devi dividere le celle in Excel. Questi potrebbero essere quando ottieni i dati da un database o li copi da Internet o li ottieni da un collega.

Un semplice esempio in cui è necessario dividere le celle in Excel è quando si hanno nomi completi e si desidera dividerli in nome e cognome.

Oppure ottieni l'indirizzo e vuoi dividere l'indirizzo in modo da poter analizzare le città o il codice pin separatamente.

Come dividere le celle in Excel

In questo tutorial imparerai come dividere le celle in Excel utilizzando le seguenti tecniche:

• Utilizzo della funzione Testo su colonna.
• Utilizzo delle funzioni di testo di Excel.
• Utilizzo di Flash Fill (disponibile nel 2013 e nel 2016).

Cominciamo!

Dividi celle in Excel utilizzando testo in colonna

Di seguito ho un elenco di nomi di alcuni dei miei personaggi di fantasia preferiti e voglio dividere questi nomi in celle separate.:

Ecco i passaggi per dividere questi nomi in nome e cognome:

• Seleziona le celle in cui hai il testo che vuoi dividere (in questo caso A2: A7).
• Fare clic sulla scheda Dati
• Nel gruppo "Strumenti dati", fai clic su "Testo in colonne".
• Nella Conversione guidata testo in colonne:
  • Passaggio 1 di 3 della procedura guidata da testo a colonne: assicurarsi che sia selezionato Delimitato (è la selezione predefinita). Ciò ti consentirebbe di separare il nome e il cognome in base a un separatore specificato (barra spaziatrice in questo caso).
  • Fare clic su Avanti.
  • Passaggio 2 di 3 Procedura guidata da testo a colonne: seleziona Spazio come delimitatore e deseleziona tutto il resto. Puoi vedere come apparirebbe il tuo risultato nella sezione Anteprima dati della finestra di dialogo.
  • Fare clic su Avanti.
  • Passaggio 3 di 3 Procedura guidata da testo a colonne: In questo passaggio è possibile specificare il formato dei dati e dove si desidera ottenere il risultato. Manterrò il formato dei dati come Generale poiché ho dati di testo da dividere. La destinazione predefinita è A2 e se continui con questa, sostituirà il set di dati originale. Se vuoi mantenere intatti i dati originali, seleziona un'altra cella come destinazione. In questo caso, viene selezionato B2.
  • Fare clic su Fine.

Questo dividerà istantaneamente il testo della cella in due colonne diverse.

Nota:

• La funzione Text to Column divide il contenuto delle celle in base al delimitatore. Anche se questo funziona bene se vuoi separare il nome e il cognome, nel caso di nome, secondo nome e cognome lo dividerà in tre parti.
• Il risultato che ottieni dall'utilizzo della funzione Testo su colonna è statico. Ciò significa che se ci sono modifiche ai dati originali, dovrai ripetere il processo per ottenere risultati aggiornati.

Dividi celle in Excel utilizzando le funzioni di testo

Le funzioni di testo di Excel sono ottime quando vuoi tagliare e tagliare a dadini le stringhe di testo.

Mentre la funzione Testo su colonna fornisce un risultato statico, il risultato che si ottiene utilizzando le funzioni è dinamico e si aggiornerebbe automaticamente quando si modificano i dati originali.

Dividere i nomi che hanno un nome e un cognome

Supponiamo di avere gli stessi dati mostrati di seguito:

Estrazione del nome

Per ottenere il nome da questo elenco, utilizzare la seguente formula:

=SINISTRA(A2,RICERCA(" ",A2)-1)

Questa formula individuerebbe il primo carattere spazio e quindi restituirebbe tutto il testo prima di quel carattere spazio:

Questa formula utilizza la funzione CERCA per ottenere la posizione del carattere spazio. Nel caso di Bruce Wayne, il carattere spazio è in sesta posizione. Quindi estrae tutti i caratteri alla sua sinistra utilizzando la funzione LEFT.

Estrazione del cognome

Allo stesso modo, per ottenere il cognome, utilizzare la seguente formula:

=DESTRA(A2,LEN(A2)-RICERCA(" ",A2))

Questa formula utilizza la funzione di ricerca per trovare la posizione della barra spaziatrice utilizzando la funzione CERCA. Quindi sottrae quel numero dalla lunghezza totale del nome (che è data dalla funzione LEN). Questo dà il numero di caratteri nel cognome.

Questo cognome viene poi estratto utilizzando la funzione RIGHT.

Nota: Queste funzioni potrebbero non funzionare correttamente se nei nomi sono presenti spazi iniziali, finali o doppi. Fare clic qui per sapere come rimuovere gli spazi iniziali/finali/doppi in Excel.

Dividere i nomi che hanno un nome, un secondo nome e un cognome

Potrebbero esserci casi in cui ottieni una combinazione di nomi in cui alcuni nomi hanno anche un secondo nome.

La formula in questi casi è un po' complessa.

Estrazione del nome

Per ottenere il nome:

=SINISTRA(A2,RICERCA(" ",A2)-1)

Questa è la stessa formula che usavamo quando non c'era il secondo nome. Cerca semplicemente il primo carattere spazio e restituisce tutti i caratteri prima dello spazio.

Estrazione del secondo nome

Per ottenere il secondo nome:

=IFERROR(MID(A2,CERCA(" ",A2)+1,CERCA(" ",A2,CERCA(" ",A2)+1)-CERCA(" ",A2)),"")

La funzione MID parte dal primo carattere spazio ed estrae il secondo nome utilizzando la differenza di posizione del primo e del secondo carattere spazio.

Nei casi in cui non è presente un secondo nome, la funzione MID restituisce un errore. Per evitare l'errore, è racchiuso all'interno della funzione IFERROR.

Estrazione del cognome

Per ottenere il cognome, utilizzare la formula seguente:

=SE(LUNGHEZZA(A2)-LUNGHEZZA(SOSTITUTO(A2," ",""))=1,DESTRA(A2,LUNGHEZZA(A2)-RICERCA(" ",A2)),DESTRA(A2,LUNGHEZZA(A2) -CERCA(" ",A2,CERCA(" ",A2)+1)))

Questa formula controlla se c'è o meno un secondo nome (contando il numero di caratteri spazio). Se è presente un solo carattere spazio, restituisce semplicemente tutto il testo a destra del carattere spazio.

Ma se ce ne sono 2, individua il secondo carattere spazio e restituisce il numero di caratteri dopo il secondo spazio.

Nota: questa formula funziona bene quando si hanno nomi che hanno solo il nome e il cognome oppure il nome, il secondo e il cognome. Tuttavia, se hai un mix in cui hai suffissi o saluti, dovrai modificare ulteriormente le formule.

Dividi celle in Excel utilizzando Flash Fill

Flash Fill è una nuova funzionalità introdotta in Excel 2013.

Potrebbe essere davvero utile quando hai un modello e vuoi estrarne rapidamente una parte.

Ad esempio, prendiamo i dati del nome e del cognome:

Il riempimento flash funziona identificando i modelli e replicandoli per tutte le altre celle.

Ecco come estrarre il nome dall'elenco utilizzando Flash Fill:

• Nella cella B2, inserisci il nome di Bruce Wayne (cioè Bruce).
• Con la cella selezionata, noterai un quadratino all'estremità destra della selezione della cella. Fare doppio clic su di esso. Questo riempirà lo stesso nome in tutte le celle.
• Quando le celle sono piene, in basso a destra vedrai l'icona Opzioni di riempimento automatico. Cliccaci sopra.
• Seleziona Riempimento flash dall'elenco.
• Non appena selezioni Flash Fill, noterai che tutte le celle si aggiornano e ora mostrano il nome per ogni nome.

Come funziona il riempimento flash?

Flash Fill cerca i modelli nel set di dati e replica il modello.

Flash Fill è una funzionalità sorprendentemente intelligente e funziona come previsto nella maggior parte dei casi. Ma anche in alcuni casi fallisce.

Ad esempio, se ho un elenco di nomi che ha una combinazione di nomi con alcuni con un secondo nome e altri no.

Se estraggo il secondo nome in tal caso, Flash Fill restituirà erroneamente il cognome nel caso in cui non ci sia il nome.

Ad essere onesti, questa è ancora una buona approssimazione della tendenza. Tuttavia, non è quello che volevo.

Ma è comunque uno strumento abbastanza buono da tenere nel tuo arsenale e da usare ogni volta che se ne presenta la necessità.

Ecco un altro esempio in cui Flash Fill funziona brillantemente.

Ho una serie di indirizzi da cui voglio estrarre velocemente la città.

Per ottenere rapidamente la città, inserisci il nome della città per il primo indirizzo (inserisci Londra nella cella B2 in questo esempio) e usa la compilazione automatica per riempire tutte le celle. Ora usa Flash Fill e ti darà immediatamente il nome della città da ogni indirizzo.

Allo stesso modo, puoi dividere l'indirizzo ed estrarre qualsiasi parte dell'indirizzo.

Si noti che questo richiederebbe che l'indirizzo sia un set di dati omogeneo con lo stesso delimitatore (virgola in questo caso).

Nel caso in cui provi a utilizzare Flash Fill quando non c'è alcun motivo, ti mostrerà un errore come mostrato di seguito:

In questo tutorial, ho trattato tre diversi modi per dividere le celle in Excel in più colonne (usando Testo su colonne, formule e riempimento Flash)

Spero che tu abbia trovato utile questo tutorial di Excel.