In questo articolo, esploreremo 9 metodi diversi per estrarre il nome e il cognome in colonne separate in Excel. Ognuno di questi approcci ha i suoi vantaggi e svantaggi, quindi sceglierai quello più adatto alle tue esigenze specifiche.
I dataset contenenti i nomi delle persone di solito hanno il nome e il cognome in un’unica cella, separati da uno spazio. Sebbene questo non sia necessariamente un problema, può essere più utile avere il nome e il cognome in colonne diverse. In questo modo, si possono eseguire più facilmente ulteriori analisi sui dati, come ordinare e filtrare in base al solo cognome.
1. Separare Nomi e Cognomi con Text to Columns
Quando devi dividere il nome e il cognome nei tuoi dati su base occasionale, “Text to Columns” dovrebbe essere il tuo metodo preferito. La funzione “Text to Columns” ti permetterà di dividere i nomi in un nuovo intervallo o di sostituire i dati attuali. Qualunque tu preferisca.
Questo è il più indicato per i casi di utilizzo una tantum, poiché dovrai eseguire nuovamente la divisione del testo in colonne per ogni nuovo nome aggiunto all’elenco.
Ecco come usare “Text to Columns” per separare nome e cognome:
- Seleziona l’intervallo di nomi che vuoi dividere.
- Vai alla scheda “Dati”.
- Fai clic sul comando “Converti in Colonne” nella sezione “Strumenti Dati”.
- Seleziona l’opzione “Delimitato” per dividere i tuoi nomi.
- Deseleziona l’opzione predefinita “Tabulazione” e seleziona “Spazio”.
- Seleziona una cella per la “Destinazione”.
- Fai clic su “Fine”.
Excel dividerà i nomi in base allo spazio che li separa e posizionerà i risultati nella posizione desiderata.
2. Separare Nomi e Cognomi con Formula di Testo
Un altro modo per ottenere il nome e il cognome dal tuo insieme di nomi completi è utilizzare una combinazione di funzioni di testo per analizzare ogni nome.
Puoi utilizzare le funzioni “LEFT” e “RIGHT” per ottenere il numero desiderato di caratteri dall’inizio o dalla fine di qualsiasi valore di testo. Puoi utilizzarle in combinazione con la funzione “FIND” per ottenere la posizione del carattere di spazio e la funzione “LEN” per ottenere il numero totale di caratteri nel nome.
Ottenere il Nome
= LEFT ( B3, FIND ( " ", B3 ) - 1 )
La formula di cui sopra estrarrà il nome dalla descrizione completa del nome nella cella B3. La funzione “FIND” troverà la posizione del primo carattere di spazio. Quando sottrai 1 da questo risultato, otterrai la posizione dell’ultimo carattere del nome. La funzione “LEFT” quindi utilizza questo valore per restituire questi primi caratteri dal nome.
Ottenere il Cognome
= RIGHT ( B3, LEN ( B3 ) - FIND ( " ", B3 ) )
La formula di cui sopra otterrà il cognome dalla descrizione completa del nome nella cella B3. La funzione “LEN” ottiene il conteggio totale dei caratteri dal nome completo. La funzione “FIND” ottiene la posizione del carattere di spazio che separa il nome e il cognome. Quando sottrai il numero totale di caratteri del nome dalla posizione del carattere di spazio, ottieni il numero di caratteri del cognome. Questa informazione viene quindi utilizzata nella funzione “RIGHT” per estrarre il cognome.
3. Separare Nomi e Cognomi con Funzione FILTERXML
Un modo astuto per dividere il testo in base al carattere di spazio è utilizzare la funzione “FILTERXML”. Questa è pensata per consentire l’estrazione di parti specifiche dei dati XML in base al nome del nodo.
Ma puoi usarla per estrarre i nomi creando prima i dati XML dal tuo nome.
= TRANSPOSE ( FILTERXML ( "<t><s>" & SUBSTITUTE ( B3," ","</s><s>")&"</s></t>","//s"))
La formula di cui sopra dividerà il nome completo nella cella B3 in nome e cognome.
La funzione “SUBSTITUTE” viene utilizzata per sostituire lo spazio con un testo come quello sopra. Questo crea una struttura di dati XML e puoi utilizzare la funzione “FILTERXML” per estrarre ciascuno dei nomi.
Ottenere solo il Nome
= FILTERXML ( "<t><s>" & SUBSTITUTE ( B3, " ", "</s><s>" ) & "</s></t>", "//s[1]" )
La versione precedente della formula FILTERXML restituirà solo il nome. Il filtro //s[1] restituirà solo il primo elemento in un tag s.
Ottenere solo il Cognome
= FILTERXML ( "<t><s>" & SUBSTITUTE ( B3, " ", "</s><s>" ) & "</s></t>", "//s[2]" )
Allo stesso modo, la formula precedente utilizza il filtro //s[2] per restituire il secondo elemento in un tag s, che è il cognome.
4. Separare Nomi e Cognomi con Funzione TEXTSPLIT
Se hai una versione più recente di Excel, puoi evitare le complesse soluzioni con formule perché c’è una funzione dedicata per dividere il testo.
Puoi utilizzare la funzione “TEXTSPLIT” per dividere un valore di testo in base a un carattere delimitatore.
Ciò significa che puoi utilizzare il carattere di spazio come delimitatore per separare il nome e il cognome.
= TEXTSPLIT ( B3, " " )
La formula di cui sopra dividerà il nome nella cella B3 in base al carattere di spazio. È uno dei modi più semplici che si possano ottenere!
5. Separare Nomi e Cognomi con Flash Fill
“Flash Fill” è un modo pratico per trasformare i tuoi dati in base agli esempi. Fornisci alcuni esempi dei risultati che vuoi nella colonna adiacente e “Flash Fill” determinerà il modello e riempirà il resto.
Questo può facilmente separare i tuoi nomi quando fornisci i risultati degli esempi.
Ecco come funziona:
- Digita alcuni nomi da soli nelle celle alla destra del nome completo corrispondente.
- Questo dovrebbe attivare “Flash Fill” e vedrai in grigio chiaro i risultati suggeriti. Premi “Invio” per accettare questi risultati e inserirli nelle celle.
- Se “Flash Fill” non si attiva automaticamente dopo i primi due o tre inserimenti, dovrai attivarlo manualmente.
- Seleziona la prima cella vuota sotto i tuoi esempi di nomi.
- Vai alla scheda “Dati” e fai clic sul comando “Flash Fill” nella sezione “Strumenti Dati”.
- Ripeti il processo nella colonna successiva per ottenere i cognomi.
6. Separare Nomi e Cognomi con Power Query
Power Query è uno strumento straordinario per trasformare i tuoi dati in praticamente qualsiasi modo tu possa immaginare. Dividere i tuoi nomi è un compito semplice per Power Query!
Power Query sarà sicuramente la strada da seguire per il tuo compito di ottenere nome e cognome in celle separate se vuoi un processo ripetibile per importare e trasformare i tuoi dati in Excel.
Prima di utilizzare Power Query, dovrai aggiungere i tuoi dati sui nomi a una Tabella Excel.
Ecco come utilizzare Power Query per dividere i nomi nella tua tabella:
- Vai alla scheda “Dati”.
- Fai clic sul comando “Da Tabella/Intervallo”.
- Questo aprirà l’editor di Power Query.
- Fai clic con il pulsante destro del mouse sull’intestazione della colonna contenente i nomi da dividere e scegli “Dividi Colonna”.
- Scegli l’opzione “Per Delimitatore” nel sottomenu.
- Seleziona “Spazio” dal menu a discesa “Seleziona o inserisci delimitatore”.
- Premi il pulsante “OK”.
Ora avrai due nuove colonne con il nome e il cognome separati.
7. Separare Nomi e Cognomi con Power Pivot
Se stai analizzando dati che includono un nome completo, potresti voler dividere i nomi utilizzando Power Pivot.
Il modello dati di Power Pivot può contenere un gran numero di righe in memoria, quindi è perfetto per lavorare con qualsiasi enorme set di dati.
Dopo aver caricato il tuo set di dati nel modello dati, puoi aggiungere altre colonne con calcoli utilizzando il linguaggio di formule DAX.
Il linguaggio di formule DAX ha molte funzioni comuni con Excel e in questo caso le funzioni utilizzate saranno esattamente le stesse delle funzioni di testo viste in precedenza.
Puoi aggiungere i tuoi dati al modello dati. Seleziona una cella all’interno della tabella che contiene i tuoi nomi e vai alla scheda “Power Pivot”, quindi fai clic sull’opzione “Aggiungi al Modello Dati”.
Questo aprirà l’editor di Power Pivot con la tabella caricata nel modello.
Potrai aggiungere nuove colonne facendo clic sull’intestazione “Aggiungi Colonna” a destra dei tuoi dati.
= LEFT ( Names[Full Name], FIND ( " ", Names[Full Name] ) - 1 )
La formula di cui sopra otterrà il nome per l’intera colonna.
= RIGHT ( Names[Full Name], LEN ( Names[Full Name] ) - FIND ( " ", Names[Full Name] ) )
La formula di cui sopra restituirà il cognome per l’intera colonna.
Ora potrai utilizzare queste colonne calcolate in una Pivot Table.
8. Separare Nomi e Cognomi con VBA
Forse devi separare i nomi spesso e vuoi una soluzione con un solo clic per questo.
VBA potrebbe essere la migliore soluzione per te.
Puoi aggiungere una macro al tuo foglio di lavoro che dividerà i nomi in colonne adiacenti.
Questo può essere eseguito aggiungendo la macro alla Barra di Accesso Rapido in modo che sia sempre disponibile da utilizzare con un solo clic.
Ecco come:
- Vai alla scheda “Sviluppo” e fai clic sul comando “Visual Basic”.
- Crea un nuovo modulo nell’editor Visual Basic e aggiungi il seguente codice:
Sub SplitNames()
Dim rng As Range
Dim arrNames() As String
Dim colCount As Integer
colCount = Selection.Columns.Count
If colCount <> 1 Then
MsgBox ("Select a single column!")
End
End If
For Each rng In Selection
arrNames = Split(rng.Value, " ")
rng.Offset(0, 1).Value = arrNames(0)
rng.Offset(0, 2).Value = arrNames(1)
Next rng
End Sub
Questo codice prima verifica che tu abbia selezionato solo un intervallo con una singola colonna. Se hai selezionato più colonne, ti avviserà di selezionare una singola colonna e interromperà l’esecuzione.
Quando viene selezionata una singola colonna, il codice scorrerà l’intervallo selezionato e dividerà ogni cella in base al carattere di spazio.
La prima e la seconda parte dei nomi divisi vengono inserite nelle celle direttamente a destra della tua selezione originale.
Dopo aver selezionato i tuoi nomi ed eseguito il codice, avrai il nome e il cognome divisi in Excel.
9. Separare Nomi e Cognomi con Office Scripts
Se utilizzi principalmente Excel online, dovrai utilizzare Office Scripts per creare un codice personalizzato per separare i tuoi nomi.
Puoi creare uno script Office simile:
- Vai alla scheda “Automatizza” e fai clic sul comando “Nuovo Script” per aprire l’editor “Code Editor” per Office Scripts.
- Aggiungi il seguente codice all’editor:
function main(workbook: ExcelScript.Workbook) { //getselected range let rng = workbook.getSelectedRange(); let rows = rng.getRowCount(); let cols = rng.getColumnCount(); if (cols != 1) { return; }; //loop through selected cells for (let i = 0; i < rows; i++) { for (let j = 0; j < cols; j++) { //split name based on space let txtName = rng.getCell(i, j).getValue().toString(); let arrName = txtName.split(" "); //enter names in adjacent cells rng.getCell(i, j + 1).setValue(arrName[0]); rng.getCell(i, j + 2).setValue(arrName[1]); }; }; };
Aggiungi il codice all’editor “Code Editor” e premi il pulsante “Salva”.
- Ora puoi selezionare un intervallo di colonna singola di nomi nel tuo foglio Excel e premere il pulsante “Esegui”.
- Il codice verificherà se la tua selezione è una singola colonna e interromperà l’esecuzione del codice in caso contrario.
In caso contrario, il codice continuerà e scorrerà le tue celle selezionate per dividere i nomi. Il nome e il cognome vengono aggiunti nelle colonne a destra della tua selezione.
Conclusioni
Molti set di dati includeranno un campo nome. A volte questo verrà fornito come nome completo in una singola cella e sarà necessario separare il nome nelle componenti nome e cognome. “Text to Column” o “Flash Fill” risolveranno il problema con pochi semplici passaggi e sono perfetti per quegli usi una tantum.
Una soluzione con formula rappresenta una soluzione più dinamica, più adatta quando il set di dati sorgente aggiungerà o modificherà i nomi. La formula che userai dipenderà dalle funzioni disponibili nella tua versione di Excel. Per set di dati più grandi o set di dati esterni che devono essere importati in Excel per primi, la soluzione Power Query o Power Pivot funzionerà meglio. Hai mai avuto la necessità di separare i nomi? Come l’hai risolto? Fammi sapere nei commenti qui sotto!
Pubblicato in Excel
Commenta per primo