Separare il nome e il cognome in Excel (dividi i nomi usando le formule)

Guarda il video - Dividi i nomi in Excel (in nome, secondo e cognome)

Excel è uno strumento straordinario quando si tratta di affettare e sminuzzare i dati di testo.

Ci sono così tante formule utili e funzionalità che puoi usare per lavorare con i dati di testo in Excel.

Una delle domande più comuni che ricevo sulla manipolazione dei dati di testo è: "Come separare nome e cognome (o nome, secondo nome e cognome) in Excel?“.

Esistono un paio di modi semplici per dividere i nomi in Excel. Il metodo che scegli dipenderà da come sono strutturati i tuoi dati e se vuoi che il risultato sia statico o dinamico.

Quindi iniziamo e vediamo diversi modi per dividere i nomi in Excel.

Dividi i nomi usando il testo in colonne

La funzionalità Testo in colonne in Excel consente di dividere rapidamente i valori di testo in celle separate in una riga.

Ad esempio, supponi di avere il set di dati come mostrato di seguito e di voler separare il nome e il cognome e ottenerli in celle separate.

Di seguito sono riportati i passaggi per separare il nome e il cognome utilizzando Testo in colonne:

1. Seleziona tutti i nomi nella colonna (A2:A10 in questo esempio)
2. Fare clic sulla scheda "Dati"
3. Nel gruppo "Strumenti dati", fai clic sull'opzione "Testo in colonne".
4. Apportare le seguenti modifiche nella Conversione guidata testo in colonna:
   1. Passaggio 1 di 3: selezionare Delimitato (questo consente di utilizzare lo spazio come separatore) e fare clic su Avanti
   2. Passaggio 2 di 3: selezionare l'opzione Spazio e fare clic su Avanti
   3. Passaggio 3 di 3: imposta B2 come cella di destinazione (altrimenti sovrascriverà i dati esistenti)
5. Fare clic su Fine

I passaggi precedenti dividerebbero istantaneamente i nomi in nome e cognome (con i nomi nella colonna B e il cognome nella colonna C).

Nota: nel caso in cui ci siano già dati nelle celle (quelle in cui è previsto l'output da Testo a colonne), Excel ti mostrerà un avviso che ti informa che ci sono già alcuni dati nelle celle. Puoi scegliere di sovrascrivere i dati o annullare Testo in colonne e rimuoverlo prima manualmente.

Una volta fatto, puoi eliminare i dati del nome completo se lo desideri.

Alcune cose da sapere quando si utilizza Testo in colonne per separare nomi e cognomi in Excel:

1. Il risultato di questo è statico. Ciò significa che nel caso in cui tu abbia nuovi dati o i dati originali abbiano alcune modifiche, devi ripetere tutto da capo per dividere i nomi.
2. Se desideri solo il Nome o solo il Cognome, puoi saltare le colonne che non desideri nel passaggio 3 della finestra di dialogo "Procedura guidata da testo a colonna". Per fare ciò, seleziona la colonna nell'anteprima che desideri saltare e quindi seleziona l'opzione "Non importare colonna (salta)".
3. Nel caso in cui non specifichi la cella di destinazione, Text to Column sovrascriverà la colonna corrente

L'opzione Testo su colonne è più adatta quando si dispone di dati coerenti (ad esempio, tutti i nomi hanno solo nome e cognome o tutti i nomi hanno un nome, un secondo nome e un cognome).

In questo esempio, ti ho mostrato come separare i nomi che hanno uno spazio come delimitatore. Nel caso in cui il delimitatore sia una virgola o una combinazione di virgola e spazio, puoi comunque utilizzare gli stessi passaggi. In questo caso, è possibile specificare il delimitatore nel passaggio 2 di 3 della procedura guidata. Esiste già un'opzione per utilizzare la virgola come delimitatore, oppure è possibile selezionare l'opzione "Altro" e specificare anche un delimitatore personalizzato.

Sebbene Text to Columns sia un modo rapido ed efficiente per dividere i nomi, è adatto solo quando si desidera che l'output sia un risultato statico. Nel caso in cui disponi di un set di dati che può espandersi o cambiare, è meglio utilizzare le formule per separare i nomi.

Separare il nome, il secondo e il cognome usando le formule

Le formule ti consentono di suddividere e tagliare a cubetti i dati di testo ed estrarre ciò che desideri.

In questa sezione, condividerò varie formule che puoi utilizzare per separare i dati dei nomi (in base a come sono strutturati i tuoi dati).

Di seguito sono riportate le tre formule che è possibile utilizzare per separare nome, secondo nome e cognome (spiegate in dettaglio più avanti nelle sezioni seguenti).

Formula per ottenere il nome:

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

Formula per ottenere il secondo nome:

=MID(A2, CERCA(" ",A2)+1,CERCA(" ",SOSTITUTO(A2," ","@",1))-CERCA(" ",A2))

Formula per ottenere il cognome:

=DESTRA(A15,LEN(A15)-RICERCA("@",SOSTITUISCI(A15," ","@",LEN(A15)-LEN(SOSTITUTO(A15,"",","")))))

Ottieni il nome

Supponiamo di avere il set di dati come mostrato di seguito e di voler separare rapidamente il nome in una cella e il cognome in una cella.

La formula seguente ti darà il nome:

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

La formula precedente utilizza la funzione CERCA per ottenere la posizione del carattere spazio tra il nome e il cognome. La funzione SINISTRA utilizza quindi questo numero di posizione dello spazio per estrarre tutto il testo prima di esso.

Questo è un uso abbastanza semplice dell'estrazione di una parte del valore del testo. Poiché tutto ciò che dobbiamo fare è identificare la posizione del primo carattere spazio, non importa se il nome ha un secondo nome o meno. La formula di cui sopra funzionerà bene.

Ora, andiamo un po' più avanti con ogni esempio.

Ottieni il cognome

Supponiamo che tu abbia lo stesso set di dati e questa volta devi ottenere il cognome.

La formula seguente estrarrà il cognome dal set di dati sopra:

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

Di nuovo, abbastanza semplice.

Questa volta, troviamo prima la posizione del carattere spazio, che viene quindi utilizzata per scoprire il numero di caratteri rimasti dopo lo spazio (che sarebbe il cognome).

Ciò si ottiene sottraendo il valore della posizione del carattere spazio con il numero totale di caratteri nel nome.

Questo numero viene quindi utilizzato nella funzione RIGHT per recuperare tutti questi caratteri a destra del nome.

Sebbene questa formula funzioni alla grande quando ci sono solo il nome e il cognome, non funzionerebbe nel caso in cui tu abbia anche un secondo nome. Questo perché abbiamo considerato solo un carattere spazio (tra il nome e il cognome). Avere un secondo nome aggiunge più caratteri di spazio al nome.

Per recuperare il cognome quando hai anche un secondo nome, usa la formula seguente:

=DESTRA(A15,LEN(A15)-RICERCA("@",SOSTITUISCI(A15," ","@",LEN(A15)-LEN(SOSTITUTO(A15,"",","")))))

Ora, questo ha iniziato a diventare un po' complesso… non è vero?

Lascia che ti spieghi come funziona.

La formula sopra trova prima il numero totale di caratteri spazio nel nome. Questo viene fatto ottenendo la lunghezza del nome con e senza il carattere spazio e quindi sottraendo quello senza spazio da quello con spazio. Questo fornisce il numero totale di caratteri spazio.

La funzione SOSTITUISCI viene quindi utilizzata per sostituire l'ultimo carattere di spazio con un simbolo "@" (puoi usare qualsiasi simbolo - qualcosa che è improbabile che si verifichi come parte del nome).

Una volta che il simbolo @ è stato sostituito al posto dell'ultimo carattere spazio, puoi facilmente trovare la posizione di questo simbolo @. Questo viene fatto usando la funzione CERCA.

Ora tutto ciò che devi fare è estrarre tutti i caratteri a destra di questo simbolo @. Questo viene fatto usando la funzione DESTRA.

Ottieni il secondo nome

Supponiamo di avere il set di dati come mostrato di seguito e di voler estrarre il secondo nome.

La seguente formula farà questo:

=MID(A2, CERCA(" ",A2)+1,CERCA(" ",SOSTITUTO(A2," ","@",1))-CERCA(" ",A2))

La formula precedente utilizza la funzione MID, che consente di specificare una posizione iniziale e il numero di caratteri da estrarre da quella posizione.

La posizione di partenza è facile da trovare utilizzando la funzione CERCA.

La parte difficile è trovare quanti caratteri estrarre dopo questa posizione iniziale. Per ottenere questo, è necessario identificare quanti caratteri ci sono dalla posizione iniziale all'ultimo carattere spazio.

Questo può essere fatto usando la funzione SOSTITUISCI e sostituisci l'ultimo carattere di spazio con un simbolo '@'. Fatto ciò, puoi facilmente utilizzare la funzione CERCA per trovare la posizione di quest'ultimo carattere spazio.

Ora che hai la posizione di partenza e la posizione dell'ultimo spazio, puoi facilmente recuperare il secondo nome usando la funzione MID.

Uno dei vantaggi dell'utilizzo di una formula per separare i nomi è che il risultato è dinamico. Quindi, nel caso in cui il tuo set di dati si espanda e vengano aggiunti più nomi o se alcuni nomi cambiano nel set di dati originale, non devi preoccuparti dei dati risultanti.

Separare i nomi utilizzando Trova e sostituisci

Adoro la flessibilità offerta da "Trova e sostituisci", perché puoi utilizzare caratteri jolly al suo interno.

Lasciami prima spiegare cos'è un carattere jolly.

Un carattere jolly è qualcosa che puoi usare al posto di qualsiasi testo. Ad esempio, puoi utilizzare un simbolo asterisco (*) e rappresenterà qualsiasi numero di caratteri in Excel. Per farti un esempio, se voglio trovare tutti i nomi che iniziano con l'alfabeto A, posso usare A* in trova e sostituisci. Questo troverà e selezionerà tutte le celle in cui il nome inizia con A.

Se non sei ancora chiaro, non preoccuparti. Continua a leggere e i prossimi esempi chiariranno quali sono i caratteri jolly e come usarli per separare rapidamente i nomi (o qualsiasi valore di testo in Excel).

In tutti gli esempi trattati di seguito, assicurati di creare una copia di backup del set di dati. Trova e sostituisci modifica i dati su cui viene utilizzato. È meglio copiare e incollare prima i dati, quindi utilizzare Trova e sostituisci sul set di dati copiato.

Ottieni il nome

Supponiamo di avere un set di dati come mostrato di seguito e di voler ottenere solo il nome.

Di seguito sono riportati i passaggi per eseguire questa operazione:

1. Copia i dati del nome nella colonna A e incollali nella colonna B.
2. Con i dati nella colonna B selezionati, fai clic sulla scheda Home
3. Nel gruppo Modifica, fai clic su Trova e seleziona.
4. Fare clic su Sostituisci. Si aprirà la finestra di dialogo "Trova e sostituisci".
5. Nella finestra di dialogo "Trova e sostituisci", inserisci quanto segue
   1. Trova cosa: * (spazio seguito dal simbolo asterisco)
   2. Sostituisci con: lascia questo vuoto
6. Fare clic su Sostituisci tutto.

I passaggi precedenti ti darebbero il nome e rimuoverebbero tutto dopo il nome.

Funziona anche se hai nomi con un secondo nome.

Suggerimento professionale: la scorciatoia da tastiera per aprire la finestra di dialogo Trova e sostituisci è Controllo + H (tieni premuto il tasto Ctrl e poi premi il tasto H).

Ottieni il cognome

Supponiamo di avere un set di dati come mostrato di seguito e di voler ottenere solo il cognome.

Di seguito sono riportati i passaggi per eseguire questa operazione:

1. Copia i dati del nome nella colonna A e incollali nella colonna B.
2. Con i dati nella colonna B selezionati, fai clic sulla scheda Home
3. Nel gruppo Modifica, fai clic su Trova e seleziona.
4. Fare clic su Sostituisci. Si aprirà la finestra di dialogo "Trova e sostituisci".
5. Nella finestra di dialogo "Trova e sostituisci", inserisci quanto segue
   1. Trova cosa: * (simbolo di asterisco seguito da uno spazio)
   2. Sostituisci con: lascia questo vuoto
6. Fare clic su Sostituisci tutto.

I passaggi precedenti ti darebbero il cognome e rimuoverebbero tutto prima del nome.

Funziona anche se hai nomi con un secondo nome.

Rimuovi il secondo nome

Nel caso in cui desideri eliminare solo il secondo nome e avere solo il nome e il cognome, puoi farlo utilizzando Trova e sostituisci.

Supponiamo di avere un set di dati come mostrato di seguito e di voler rimuovere il secondo nome da questi.

Di seguito sono riportati i passaggi per eseguire questa operazione:

1. Copia i dati del nome nella colonna A e incollali nella colonna B.
2. Con i dati nella colonna B selezionati, fai clic sulla scheda Home
3. Nel gruppo Modifica, fai clic su Trova e seleziona.
4. Fare clic su Sostituisci. Si aprirà la finestra di dialogo "Trova e sostituisci".
5. Nella finestra di dialogo "Trova e sostituisci", inserisci quanto segue
   1. Trova cosa: * (spazio seguito dal simbolo asterisco seguito dal carattere spazio)
   2. Sostituisci con: (metti qui uno spazio)
6. Fare clic su Sostituisci tutto.

I passaggi precedenti rimuoverebbero il secondo nome da un nome completo. Nel caso in cui alcuni nomi non abbiano alcun secondo nome, non verrebbero modificati.

Separare i nomi usando il riempimento flash

Il riempimento flash è stato introdotto in Excel 2013 e semplifica la modifica o la pulizia di un set di dati di testo.

E quando si tratta di separare i dati dei nomi, è proprio nel vicolo di Flash Fill.

La cosa più importante da sapere quando si utilizza Flash Fill è che è necessario un modello che il riempimento flash possa identificare. Una volta identificato il modello, ti aiuterà facilmente a dividere i nomi in Excel (otterrai maggiore chiarezza su questo quando esaminerai alcuni esempi di seguito).

Ottieni il nome o il cognome dal nome completo

Supponiamo di avere un set di dati come mostrato di seguito e di voler ottenere solo il nome.

1. Nella cella adiacente, digita manualmente il nome dal nome completo. In questo esempio, digiterei Rick.
2. Nella seconda cella, digita manualmente il nome dal nome della cella adiacente. Mentre stai digitando, vedrai che Flash Fill ti mostra automaticamente un elenco del nome (in grigio).
3. Quando vedi i nomi in grigio, dai un'occhiata veloce per assicurarti che mostri i nomi giusti. Se sono corretti, premi il tasto Invio e Flash Fill riempirà automaticamente il resto delle celle con il nome.

Flash Fill ha bisogno che tu gli dia uno schema che può seguire quando ti dà i dati modificati. Nel nostro esempio, quando digiti il ​​nome nella prima cella, Flash Fill non riesce a capire il modello.

Ma non appena inizi a inserire il Nome nella seconda cella, Flash Fill comprende lo schema e ti mostra un suggerimento. Se il suggerimento è corretto, premi il tasto Invio.

E se non è corretto, puoi provare a inserire manualmente alcune celle in più e verificare se Flash Fill è in grado di discernere il motivo o meno.

A volte, potresti non vedere il motivo in grigio (come mostrato nel passaggio 2 sopra). In tal caso, segui i passaggi seguenti per ottenere il risultato di riempimento flash:

1. Immettere il testo manualmente in due celle.
2. Seleziona entrambe queste celle
3. Passa il cursore sulla parte in basso a destra della selezione. Noterai che il cursore si trasforma in un'icona più
4. Fare doppio clic su di esso (tasto sinistro del mouse). Questo riempirà tutte le celle. A questo punto, i risultati sono probabilmente errati e non quelli che ti aspettavi.
5. In basso a destra dei dati risultanti, vedrai una piccola icona di riempimento automatico. Fai clic su questa icona di riempimento automatico
6. Fare clic su Riempimento flash

I passaggi precedenti ti darebbero il risultato di Flash Fill (in base al modello che ha dedotto).

Puoi anche usare Flash Fill per ottenere il cognome o il secondo nome. Nelle prime due celle, inserisci il cognome (o il secondo nome) e il riempimento rapido sarà in grado di comprendere lo schema

Riorganizzare il nome usando il riempimento flash

Flash Fill è uno strumento intelligente e può anche decifrare modelli leggermente complessi

Ad esempio, supponiamo di avere un set di dati come mostrato di seguito e di voler riorganizzare il nome da Rick Novak a Novak, Rick (dove viene prima il cognome seguito da una virgola e poi il nome).

Di seguito sono riportati i passaggi per eseguire questa operazione:

1. Nella cella adiacente, digita manualmente Novak, Rick
2. Nella seconda cella, digita manualmente Connor, Susan. Mentre stai digitando, vedrai che Flash Fill ti mostra un elenco dei nomi nello stesso formato (in grigio).
3. Quando vedi i nomi in grigio, dai un'occhiata veloce per assicurarti che mostri i nomi giusti. Se sono corretti, premi il tasto Invio e Flash Fill riempirà automaticamente il resto delle celle con i nomi nello stesso formato.

Rimuovi il secondo nome (o prendi semplicemente il secondo nome)

Puoi anche usare Flash Fill per eliminare il secondo nome o ottenere solo il secondo nome.

Ad esempio, supponiamo di avere un set di dati come mostrato di seguito e di voler ottenere solo il nome e il cognome e non il secondo nome.

Di seguito sono riportati i passaggi per eseguire questa operazione:

1. Nella cella adiacente, digita manualmente Rick Novak
2. Nella seconda cella, digita manualmente Susan Connor. Mentre stai digitando, vedrai che Flash Fill ti mostra un elenco dei nomi nello stesso formato (in grigio).
3. Quando vedi i nomi in grigio, dai un'occhiata veloce per assicurarti che mostri i nomi giusti. Se questi sono corretti, premi il tasto Invio e Flash Fill riempirà automaticamente il resto delle celle con i nomi senza il secondo nome.

Allo stesso modo, se vuoi ottenere solo i secondi nomi, digita il secondo nome nelle prime due celle e usa Flash Fill per ottenere il secondo nome da tutti i nomi rimanenti.

Gli esempi mostrati in questo tutorial utilizzano i nomi durante la manipolazione dei dati di testo. Puoi utilizzare gli stessi concetti per lavorare anche con altri formati di dati (come indirizzi, nomi di prodotti, ecc.)