Podeu utilitzar Excel per dividir la informació en peces més petites. Trobar les dades que necessiteu i manipular-les és un objectiu important per a molts usuaris d'Excel.
Si teniu el nom complet d'una persona, potser haureu de centrar-vos només en el seu nom o cognom. Per exemple, si envieu un correu electrònic automatitzat amigable als vostres clients, haureu d'utilitzar els seus noms per evitar que soni impersonal. Si esteu consultant una llista d'enquestats, pot ser important que només utilitzeu els seus cognoms o que amagueu els seus cognoms per mantenir l'anonimat.
Excel fa que aquest procés sigui senzill i hi ha molts enfocaments diferents que podeu adoptar. Aquí teniu un tutorial que us ajudarà a crear columnes de Nom i Cognom separades mitjançant fórmules. També cobrim el tema dels segons noms.
Fórmules d'Excel per dividir noms en parts
Per on comences?
Separació dels noms
Aquesta és la fórmula genèrica:
=ESQUERRA(cel·la, TROBAR(” “,cel·la,1)-1)
Per executar-lo, substituïu cel·la amb el punter de cel·la que conté el primer nom complet que voleu dividir. En aquest exemple, voleu seleccionar B2 i introduir la fórmula:
=ESQUERRA(A2,TROBAR(” “,A2,1)-1)
Tanmateix, és important tenir en compte que en alguns dispositius, aquesta fórmula utilitza punts i coma en lloc de comes. Per tant, si la fórmula anterior no us funciona, potser haureu d'utilitzar la versió següent:
=ESQUERRA(cel·la;TROBAR(” “;cel·la;1)-1)
A l'exemple, utilitzaríeu:
=ESQUERRA(A2;TROBAR(” “;A2;1)-1)
Ara només podeu arrossegar el mànec d'emplenament fins al final de la columna Nom.
La funció ESQUERRA us permet separar una cadena, començant per l'extrem esquerre del text. La part TROBA d'aquesta fórmula localitza el primer espai del nom complet, de manera que obtindreu la part del vostre nom complet que ve abans d'un espai buit.
Per tant, els noms amb guionet es mantenen units, i també els noms que contenen caràcters especials. Però la columna del nom complet no contindrà els segon noms ni les inicials del segon segon pla.
Coma o punt i coma?
Per què la fórmula no és la mateixa per a tothom?
Per a molts usuaris d'Excel, les funcions d'Excel utilitzen comes per separar les dades d'entrada. Però en alguns dispositius, la configuració regional és diferent.
Per descobrir quin símbol utilitza el vostre Excel, només heu de començar a escriure la fórmula. Quan comences a entrar =ESQUERRA(, veureu un text que us suggerirà el format adequat.
Separació dels cognoms
Feu el mateix enfocament per separar els cognoms. Aquesta vegada, hauríeu d'utilitzar la fórmula DRET, que separa les cadenes començant pel costat dret.
La fórmula que necessiteu és:
=DRETA(cel·la, LEN(cel·la) – CERCAR(“#”, SUBSTITUCIÓ(cel·la”, “, “#”, LEN(cel·la) – LEN(SUBSTITUCIÓ(cel·la, ” “, “”))))
A l'exemple anterior, utilitzaríeu la fórmula següent a la cel·la C2:
=DRETA(A2, LEN(A2) – CERCAR(“#”, SUBSTITUT(A2,” “, “#”, LEN(A2) – LEN(SUBSTITUT(A2, ” “, “”)))))
Una vegada més, potser haureu de canviar de la coma a un punt i coma, el que significa que haureu d'utilitzar:
=DRETA(A2; LEN(A2) – CERCAR(“#”; SUBSTITUCIÓ(A2;” “; “#”; LEN(A2) – LEN(SUBSTITUCIÓ(A2; ” “; “”)))))
Els cognoms amb guionet i els cognoms amb caràcters especials es mantenen intactes.
Per què aquesta fórmula és més complexa que la dels noms de pila? És més difícil separar el segon nom i les inicials del cognom dels cognoms.
Si voleu que els segons noms i les inicials apareguin amb els cognoms, podeu utilitzar la fórmula:
=DRETA(cel·la, LEN(cel·la) – CERCAR(” “, cel·la))
o:
=DRETA(A2, LEN(A2) – CERCAR(” “, A2))
o:
=DRETA(A2; LEN(A2) – CERCAR(” “; A2))
Però, què passa si voleu separar els noms secundaris? Això és menys comú, però pot ser útil saber-ho.
Separació dels noms secundaris
La fórmula dels segons noms és la següent:
=MID(cel·la, CERCAR(” “, cel·la) + 1, CERCAR(” “, cel·la, CERCAR(” “, cel·la)+1) – CERCAR(” “, cel·la)-1)
A l'exemple anterior, obteniu:
=MID(A2, CERCAR(” “, A2) + 1, CERCAR(” “, A2, CERCAR(” “, A2)+1) – CERCAR(” “, A2)-1)
Si el vostre Excel utilitza punt i coma, la fórmula és:
=MID(A2; CERCAR(” “; A2) + 1; CERCAR(” “; A2; CERCAR(” “; A2)+1) – CERCAR(” “; A2)-1)
Després d'introduir la fórmula, arrossegueu el mànec d'emplenament cap avall. Aquí hi ha una columna del segon nom afegida a l'exemple anterior:
Si el nom complet no conté el segon nom ni la inicial, obtindreu valors zero en aquesta columna, que es poden mostrar com a #VALOR!. Per obtenir cel·les en blanc en lloc de #VALOR!, podeu utilitzar la funció SI ERROR.
Aleshores, la teva fórmula es converteix en:
=SIERROR(MID(cel·la, CERCAR(” “, cel·la) + 1, CERCAR(” “, cel·la, CERCAR(” “, cel·la)+1) – CERCAR(” “, cel·la)-1),0)
o:
=SIERROR(MID(A2, CERCAR(” “, A2) + 1, CERCAR(” “, A2, CERCAR(” “, A2)+1) – CERCAR(” “, A2)-1),0)
o:
=SIERROR(MID(A2; CERCAR(” “; A2) + 1; CERCAR(” “; A2; CERCAR(” “; A2)+1) – CERCAR(” “; A2)-1);0)
Un enfocament per separar diversos noms secundaris
Què passa si algú de la vostra llista té diversos noms secundaris? Utilitzant la fórmula anterior, només es recuperarà el seu segon nom.
Per resoldre aquest problema, podeu provar un enfocament diferent per separar els segons noms. Si teniu les columnes de nom i cognoms creades, simplement podeu tallar-les. Tot el que quedi comptarà com a segon nom.
Aquesta fórmula és:
=TRIM(MID(cel·la1, LEN (cel·la2)+1, LEN (cel·la1)-LEN (cel·la2 i cel·la3)))
Aquí, la cel·la1 fa referència al punter de cel·la de la columna Nom complet, la cel·la2 fa referència al punter de la cel·la de la columna Nom, mentre que la cel·la3 fa referència al punter de la cel·la de la columna Cognoms. En l'exemple anterior, obtenim:
=TALLA(MITJÀ(A2;LEN(B2)+1;LEN(A2)-LEN(B2&D2)))
o:
=TALLA(MID(A2;LEN(B2)+1;LEN(A2)-LEN(B2&D2)))
Si aneu amb aquesta fórmula, no us haureu de preocupar pels valors zero.
Recapitulació ràpida
Aquí teniu les fórmules que podeu utilitzar per dividir els noms complets en parts:
Primers noms: =ESQUERRA(cel·la, TROBAR(” “,cel·la,1)-1)
Cognoms: =DRETA(cel·la, LEN(cel·la) – CERCAR(“#”, SUBSTITUCIÓ(cel·la”, “, “#”, LEN(cel·la) – LEN(SUBSTITUCIÓ(cel·la, ” “, “”))))
Segon nom: =SIERROR(MID(cel·la, CERCAR(” “, cel·la) + 1, CERCAR(” “, cel·la, CERCAR(” “, cel·la)+1) – CERCAR(” “, cel·la)-1),0)
Fórmula alternativa per als noms secundaris: =TRIM(MID(cel·la1, LEN (cel·la2)+1, LEN (cel·la1)-LEN (cel·la2 i cel·la3)))
Separant noms i cognoms sense utilitzar fórmules
Si no teniu ganes d'escriure un munt de fórmules que es poden introduir malament, aprofiteu l'assistent de conversió de text a columnes integrat d'Excel.
- Assegureu-vos que el Dades la pestanya es selecciona al menú de la part superior i ressalteu la columna que voleu convertir.
- A continuació, feu clic a Text a columnes.
- A continuació, assegureu-vos Delimitat està seleccionat i feu clic Pròxim
. - Ara, selecciona Espai de les opcions i feu clic Pròxim.
- A continuació, canvieu el Destinació a “$ B$ 2” i feu clic Acabar.
El resultat final hauria de ser així. 
.
El resultat final hauria de ser així. 
Una Paraula Final
Hi ha moltes altres maneres de resoldre aquest problema a Excel. Si cap de les opcions disponibles fa el que necessiteu, feu una investigació més.
L'ús de fórmules és relativament senzill i no depèn de la versió d'Excel que utilitzeu. Però, malauradament, encara podeu trobar errors.
Per exemple, si el nom complet d'una persona comença amb el seu cognom, es dividirà de manera equivocada. Les fórmules també tindran problemes amb els cognoms que continguin prefixos o sufixos, com ara le Carré o van Gogh. Si el nom d'una persona acaba en Jr., apareixerà com a cognom.
Tanmateix, hi ha modificacions que podeu afegir per resoldre aquests problemes tal com apareixen. Treballar amb fórmules us ofereix la flexibilitat que necessiteu per fer front a aquestes complexitats.