A Excel, és molt fàcil dividir el nom, el segon i el cognom que apareixen a la mateixa columna en columnes separades utilitzant diferents maneres.
Suposem que heu rebut una llista de contactes amb tots els noms que figuren en noms complets en una sola columna i probablement haureu de separar el nom, el segon i els cognoms i dividir-los en columnes separades. Hi ha poques maneres diferents que podeu utilitzar per separar els noms: utilitzant la funció Text a columnes, l'emplenament Flash i les fórmules.
A Excel, és molt fàcil dividir els noms d'una columna en dues o més columnes. En aquest article, us mostrarem com dividir els noms en diferents columnes a Excel utilitzant diferents maneres.
Com dividir els noms a Excel
Hi ha tres maneres senzilles de dividir els noms a Excel. En funció de l'estructura de dades i de si voleu que els noms dividits siguin estàtics o dinàmics, trieu un dels mètodes següents:
- Dividiu els noms amb la funció Text a columnes
- Separa els noms mitjançant fórmules
- Separeu els noms mitjançant Flash Fill
Separa els noms mitjançant l'assistent de text a columna
L'assistent de text a columna és una de les maneres més habituals de separar els noms i els cognoms a Excel, així com els segons noms. Assegura't que hi hagi columnes buides al costat dels noms que dividiràs perquè els noms complets es dividiran en columnes separades.
Per exemple, teniu el conjunt de dades següent amb noms complets i voleu dividir/separar el nom i els cognoms i emmagatzemar-los en cel·les separades.
Primer, ressalteu la columna de noms complets que voleu separar. A continuació, aneu a la pestanya "Dades" i feu clic a l'opció "Text a columnes" a la secció "Eines de dades".
S'obrirà l'assistent per convertir text en columnes. Al primer pas de l'assistent, seleccioneu l'opció "Delimitat" i feu clic a "Següent".
Al pas 2 de 3 de l'assistent per convertir text en columnes, seleccioneu el delimitador que separa les vostres dades, elimineu qualsevol altra marca de verificació i feu clic a "Següent". En el nostre cas, "espai" separa els noms i els cognoms, de manera que triem aquest delimitador.
La secció de previsualització de dades que hi ha a continuació a la finestra mostra com s'analitzen els vostres noms.
Al pas 3 de 3, seleccioneu el format i la destinació de les dades i feu clic a "Finalitza".
Normalment, el "General" predeterminat funcionarà bé per a la majoria de tipus de dades. Al camp "Destinació", especifiqueu la ubicació on voleu que es mostri la sortida. Heu d'especificar l'adreça de la primera cel·la a la columna on voleu els resultats (B2, en el nostre cas).
Recordeu que si no especifiqueu la cel·la de destinació, l'assistent sobreescriurà les dades originals, així que assegureu-vos de triar una columna buida.
Un cop feu clic al botó "Finalitzar", separaria instantàniament els noms complets en dues columnes separades (Nom i Cognom).
Seguiu els mateixos passos si teniu nom, segon nom i cognoms, i els vostres noms es dividiran en tres columnes en lloc de dues.
Nota: El resultat d'aquest mètode és estàtic. Vol dir que si canvieu el nom original, haureu de tornar a fer-ho per dividir els noms.
Dividiu els noms separats per comes
Si el nom i els cognoms estan separats per comes, seguiu els passos següents per eliminar la coma i dividir el nom i els cognoms.
A l'exemple següent, els noms tenen un format invers (Cognoms, Nom), on el cognom apareix primer seguit d'una coma i després el primer nom.
Seleccioneu els noms i aneu a Dades -> Text a columna. Al pas 1, trieu el "Delimitador" i feu clic a "Següent". Al pas 2, a Delimitadors, marqueu "Coma" (,) com a delimitador perquè els vostres noms estan separats per una coma.
A l'últim pas, trieu el format de dades com a "General", especifiqueu la destinació i feu clic a "Finalitza".
Ara, tindreu noms en columnes separades.
Noms separats mitjançant fórmules
L'assistent de text a columnes és ràpid i fàcil per separar noms. Tanmateix, si voleu editar els noms originals i voleu un mètode dinàmic que s'actualitzi automàticament cada vegada que canvieu els noms, dividir els noms amb fórmules és l'opció correcta. Podeu utilitzar les funcions ESQUERRA, DRET, MITJÀ, LEN i CERCAR o TROBAR per separar els noms.
Separeu el nom i el cognom a Excel mitjançant fórmules
Obteniu el primer nom
Suposem que teniu el conjunt de dades següent i voleu separar el primer nom en una cel·la separada. Heu de combinar les funcions FIND i LEFT en una fórmula per obtenir el primer nom.
Utilitzeu la fórmula següent per obtenir el primer nom:
=ESQUERRA(A2,TROBAR(" ",A2)-1)Aquesta fórmula utilitza la funció FIND per trobar la posició del caràcter d'espai (" ") entre el nom i el cognom i resta 1 per excloure l'espai. A continuació, aquest número es subministra a la funció ESQUERRA, que utilitza aquest número de posició per extreure tot el text anterior. També podeu utilitzar la funció CERCAR en comptes de la funció TROBAR.
Després d'introduir la fórmula en una cel·la buida (B2), arrossegueu el mànec d'emplenament cap a altres cel·les per aplicar aquesta fórmula i tots els noms s'han dividit a la columna B tal com es mostra a continuació:
Podeu niuar la funció CERCAR i TROBAR dins de la funció ESQUERRA per extreure el primer nom. La principal diferència entre les dues funcions és que FIND distingeix entre majúscules i minúscules, mentre que SEARCH no distingeix.
Si teniu coma (,) en lloc d'espai entre el nom i el cognom, feu servir la coma com a primer argument a la funció FIND:
=ESQUERRA(A2;TROBAR(",";A2)-1)Obteniu el Cognom
Ara, si necessiteu extreure el cognom, utilitzeu la funció DRET. La fórmula següent extreu el cognom del mateix conjunt de dades:
=DRETA(A2,LEN(A2)-TROBAR(" ",A2))La fórmula primer troba la posició del caràcter d'espai, resta aquest nombre de la longitud total de la cadena (que és retornada per la funció LEN) i després aquest número es subministra a la funció DRET per extreure tants caràcters del final de la cadena. (nom).
Separeu el nom, el segon i el cognom a Excel mitjançant fórmules
Dividir noms que inclouen un segon nom necessita fórmules diferents, segons el format de nom que tingueu.
Per aconseguir el nom quan tingueu un segon nom o una inicial del segon nom, utilitzeu la mateixa fórmula LEFT FIND que ja coneixeu.
Obteniu el Cognom
La fórmula RIGHT FIND anterior funciona bé quan només hi ha el nom i el cognom, no servirà de gaire en cas que els vostres noms originals continguin un segon nom o una inicial. Això es deu al fet que no heu tingut en compte dos espais al nom.
Per obtenir el cognom quan també teniu un segon nom, feu servir aquesta fórmula:
=DRETA(A2,LEN(A2)-CERCAR(" ",A2,CERCAR(" ",A2,1)+1))Per extreure el cognom, primer determineu la posició del segon caràcter d'espai utilitzant les funcions de cerca imbricades, afegiu 1 a SEARCH(” “,A2,1) per iniciar l'extracció amb el caràcter següent. A continuació, resteu la posició del segon espai de la longitud total de la cadena i obteniu la longitud del cognom com a número de resultat. A continuació, doneu aquest número resultant a la funció DRET per extreure el nombre de caràcters del final de la cadena.
Obteniu el segon nom
La funció MID utilitza tres arguments, el primer argument especifica el text o l'adreça de la cel·la, el segon especifica la posició inicial i l'últim argument indica nombres de caràcters per extreure el segon nom d'aquesta posició.
La sintaxi:
=MID(text, nombre_inici, nombre_caracters)Per obtenir el segon nom, introduïu aquesta fórmula en una cel·la en blanc:
=MID(A2,CERCAR(" ",A2)+1,CERCAR(" ",A2,CERCAR(" ",A2)+1)-CERCAR(" ",A2)-1)Vegem com funciona aquesta fórmula complexa:
Per extreure el segon nom o la inicial del segon nom, heu de determinar la posició dels dos espais al nom complet. Per trobar la posició del primer caràcter espai, introduïu-ho CERCAR (" ",A2) funció a l'argument "número_inici" i afegiu 1 per iniciar l'extracció del caràcter següent.
Aleshores, per saber la longitud del segon nom, poseu-ho CERCAR(" ",A2,CERCAR(" ",A2)+1)-CERCAR(" ",A2)-1 funció imbricada a l'argument "num_chars", que resta la posició del primer espai de la posició del segon espai i resta 1 del resultat per eliminar un espai final. El resultat final us indicarà quants caràcters heu d'extreure.
Ara, la funció MID amb la posició inicial del segon nom i els caràcters numèrics per extreure separa el segon nom del nom complet (A2).
Separeu els noms a Excel mitjançant Flash Fill
L'emplenament flash s'utilitza principalment per omplir automàticament les dades d'un patró específic. També es pot utilitzar per dividir noms. Només està disponible a Excel 2013, 2016, 2019 i 365.
Suposem que teniu el conjunt de dades següent i que només voleu obtenir els primers noms dels noms complets.
A la cel·la adjacent al nom original, escriviu el primer nom. En aquest cas, escriviu "Steve" a la cel·la B2.
A continuació, comenceu a escriure el primer nom a la segona cel·la de la columna. Mentre escriviu, si Excel detecta un patró, Flash Fill us mostrarà automàticament una llista del primer nom a les altres cel·les (en gris).
Quan veieu la llista de noms en gris i si aquests noms són correctes, només heu de prémer la tecla "Enter" i Flash Fill omplirà automàticament la resta de la columna amb els noms.
Seguiu el mateix procediment per separar els cognoms en una columna separada.
El resultat:
Flash Fill funciona detectant un patró en aquestes dades i seguint aquest patró mentre us proporciona les dades modificades. Al principi, quan introduïu el primer nom a la primera cel·la, Flash Fill no reconeix el patró. Però quan torneu a escriure el primer nom a la segona cel·la, Flash Fill reconeix el patró i us mostra el suggeriment per dividir els noms. A continuació, només cal que premeu la tecla "Enter".
Generalment, la funció d'emplenament Flash està activada de manera predeterminada. Si no funciona al vostre Excel, després d'escriure el primer nom a la primera cel·la, només podeu seleccionar la segona cel·la i fer clic al botó "Flash Fill" del grup Eines de dades a la pestanya "Dades".
Alternativament, també podeu prémer "Ctrl" + "E" per obtenir els mateixos resultats.
Ara, la resta de cel·les s'ompliran amb els noms de pila.
De vegades, és possible que no vegeu el suggeriment del patró en gris; en aquest cas, podeu utilitzar el mànec d'emplenament per obtenir el resultat de Flash Fill.
Primer, escriviu els noms manualment en dues cel·les i seleccioneu aquestes dues cel·les. A continuació, passeu el cursor a l'extrem inferior dret de la selecció. Notareu que el cursor canvia d'un petit quadrat verd (icona de farciment) a una icona més.
A continuació, feu doble clic a la icona més. Això omplirà la resta de cel·les. En aquest punt, els resultats són incorrectes, veureu que els dos noms es repeteixen una i altra vegada. Aleshores, a l'extrem inferior dret de les dades resultants, veureu una petita icona d'emplenament automàtic com es mostra a continuació. Feu clic a aquesta icona d'"Emplenar automàticament" i seleccioneu "Flash Fill".
Això ompliria els noms a totes les cel·les:
Elimina el segon nom
També podeu utilitzar l'eina Flash Fill per eliminar el segon nom del nom complet.
Per exemple, suposem que teniu el conjunt de dades següent i voleu obtenir només el primer i el cognom sense el segon nom o la inicial del segon nom.
Per obtenir noms sense segon nom o inicial, escriviu manualment "Lord Stark" a la cel·la adjacent. A continuació, a la segona cel·la adjacent, escriviu "Daenerys Targaryen". Mentre escriviu, Flash Fill reconeixerà un patró i us mostrarà una llista de noms sense segon nom (en gris).
Si el suggeriment és correcte, premeu la tecla "Enter" i Flash Fill omplirà automàticament la resta de cel·les amb els noms sense el segon nom.
Si només voleu obtenir els segons noms sense nom i cognoms, introduïu el segon nom a les dues primeres cel·les i utilitzeu l'eina Flash Fill per obtenir els segons noms de tots els noms complets d'una columna.
Aquest tutorial mostra com separar els noms mentre es manipulen les dades de text. Aquests mètodes també poden ser útils quan es treballa amb altres formes de dades, com ara adreces, noms de productes, marques, etc.