Podeu utilitzar Excel per dividir la informació en trossos més petits. 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, és possible que hàgiu de posar a zero només el seu nom o cognom. Per exemple, si envieu un correu electrònic automàtic amigable als vostres clients, heu d’utilitzar els seus noms per evitar que semblin impersonals. Si busqueu una llista dels enquestats, pot ser que sigui important només fer servir els seus cognoms o amagar els seus cognoms per mantenir l’anonimat.
Excel facilita aquest procés i hi ha molts enfocaments diferents que podeu adoptar. Aquí teniu un tutorial que us ajudarà a crear columnes separades de nom i cognom mitjançant fórmules. També tractem la qüestió dels segons noms.
Fórmules d'Excel per dividir noms en parts
Per on comenceu?
Separació de noms
Aquesta és la fórmula genèrica:
= ESQUERRA (cel·la, FIND (, cel·la, 1) -1)
Per executar-lo, substituïu cel·la amb el punter de cel·la que conté el primer nom complet que vulgueu dividir. En aquest exemple, voleu seleccionar B2 i introduir la fórmula:
= ESQUERRA (A2, FIND (, A2,1) -1)
Tot i això, és important tenir en compte que en alguns dispositius, aquesta fórmula fa servir punts i coma en lloc de comes. Per tant, si la fórmula anterior no us funciona, és possible que hàgiu d'utilitzar la versió següent:
= ESQUERRA (cel·la; FIND (; cel·la; 1) -1)
A l'exemple, utilitzeu:
= ESQUERRA (A2; FIND (; A2; 1) -1)
Ara només podeu arrossegar el mànec de farciment cap al final de la columna Nom.
La funció ESQUERRA us permet separar una cadena, començant per l’extrem esquerre del text. La part FIND d’aquesta fórmula localitza el primer espai del nom complet, de manera que obtindreu la part del nom complet que apareix abans que un espai buit.
Per tant, els noms amb guionet es mantenen units, així com els noms que contenen caràcters especials. Però la vostra columna Nom complet no inclourà noms intermedis ni inicials intermèdies.
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 cal que escriviu la fórmula. Quan comenceu a entrar = ESQUERRA ( , veureu un text del cursor que us suggerirà el format adequat.
Separació de cognoms
Seguiu el mateix enfocament per separar els cognoms. Aquesta vegada, heu d’utilitzar la fórmula RIGHT, que separa les cadenes que comencen pel costat dret.
La fórmula que necessiteu és:
= DRETA (cel·la, LEN (cel·la) - CERCA (#, SUBSTITUT (cel·la,, #, LEN (cel·la) - LEN (SUBSTITUT (cel·la,)))))
A l'exemple anterior, utilitzeu la fórmula següent a la cel·la C2:
= DRETA (A2, LEN (A2) - CERCA (#, SUBSTITUT (A2 ,, #, LEN (A2) - LEN (SUBSTITUT (A2 ,,)))))
Una vegada més, potser haureu de canviar de coma a punt i coma, és a dir, que haureu d’utilitzar:
= DRETA (A2; LEN (A2) - CERCA (#; SUBSTITUT (A2 ;; #; LEN (A2) - LEN (SUBSTITUT (A2 ;;)))))
Els cognoms i els cognoms amb caràcters especials es mantenen intactes.
Per què aquesta fórmula és més complexa que la dels noms? És més difícil separar els segons noms i les inicials mitjanes dels cognoms.
Si voleu que apareguin els primers noms i les inicials amb els cognoms, podeu utilitzar la fórmula:
= DRETA (cel·la, LEN (cel·la) - CERCA (, cel·la))
o:
= DRETA (A2, LEN (A2) - CERCA (, A2))
o:
= DRETA (A2; LEN (A2) - CERCA (; A2))
Però, i si voleu separar els segons noms? Això és menys comú, però pot ser útil saber-ho.
Separació de noms mitjans
La fórmula per als segons noms és la següent:
moure els comptes de Google Authenticator a un telèfon nou
= MID (cel·la, SEARCH (, cel·la) + 1, SEARCH (, cel·la, SEARCH (, cel·la) +1) - SEARCH (, cel·la) -1)
A l'exemple anterior, obtindreu:
= MID (A2, SEARCH (, A2) + 1, SEARCH (, A2, SEARCH (, A2) +1) - SEARCH (, A2) -1)
Si el vostre Excel utilitza punts i coma, la fórmula és:
= MID (A2; SEARCH (; A2) + 1; SEARCH (; A2; SEARCH (; A2) +1) - SEARCH (; A2) -1)
Després d’introduir la fórmula, arrossegueu la palanca de farciment cap avall. A continuació, es mostra una columna de segon nom que s’afegeix a l’exemple anterior:
Si el nom complet no conté cap segon nom ni inicial, obtindreu valors zero en aquesta columna, que es pot mostrar com a #VALOR !. Per obtenir cel·les en blanc en lloc de #VALUE !, podeu utilitzar la funció IFERROR.
A continuació, la vostra fórmula es convertirà en:
= IFERROR (MID (cel·la, SEARCH (, cel·la) + 1, SEARCH (, cel·la, SEARCH (, cel·la) +1) - SEARCH (, cel·la) -1), 0)
o:
= IFERROR (MID (A2, SEARCH (, A2) + 1, SEARCH (, A2, SEARCH (, A2) +1) - SEARCH (, A2) -1), 0)
o:
= IFERROR (MID (A2; SEARCH (; A2) + 1; SEARCH (; A2; SEARCH (; A2) +1) - SEARCH (; A2) -1); 0)
Un enfocament per separar diversos noms intermedis
Què passa si algú de la vostra llista té diversos segons noms? Utilitzant la fórmula anterior, només es recuperarà el seu primer cognom.
Per resoldre aquest problema, podeu provar un enfocament diferent per separar els segons noms. Si teniu creades les columnes de nom i cognom, només podeu tallar-les. Tot el que queda comptarà com a segon nom.
Aquesta fórmula és:
= TRIM (MID (cel·la1, LEN (cel·la2) + 1, LEN (cel·la1) -LEN (cel·la2 i cel3)))
Aquí, la cel·la 1 fa referència al punter de cel·la a la columna Nom complet, la cel·la 2 fa referència al punter de cel·la a la columna Nom, mentre que la cel·la 3 fa referència al punter de cel·la a la columna Nom. A l'exemple anterior, obtenim:
= TRIM (MID (A2, LEN (B2) + 1, LEN (A2) -LEN (B2 i D2))))
o:
= TRIM (MID (A2; LEN (B2) +1; LEN (A2) -LEN (B2 i D2)))
Si seguiu 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, FIND (, cel·la, 1) -1)
Cognoms: = DRETA (cel·la, LEN (cel·la) - CERCA (#, SUBSTITUT (cel·la,, #, LEN (cel·la) - LEN (SUBSTITUT (cel·la,)))))
Segon nom: = IFERROR (MID (cel·la, SEARCH (, cel·la) + 1, SEARCH (, cel·la, SEARCH (, cel·la) +1) - SEARCH (, cel·la) -1), 0)
Fórmula alternativa per als segons noms: = TRIM (MID (cel·la1, LEN (cel·la2) + 1, LEN (cel·la1) -LEN (cel·la2 i cel3)))
Separar noms i cognoms sense fer servir fórmules
Si no teniu ganes d’escriure un munt de fórmules que poden introduir-se malament, aprofiteu l’assistent de conversió de text a columnes integrat per Excel.
- Assegureu-vos que Dades La pestanya està seleccionada 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 a Pròxim
. - Ara seleccioneu Espai des de les opcions i feu clic a Pròxim .
- A continuació, canvieu el fitxer Destinació a$ B $ 2i feu clic a 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 no fa el que necessiteu, investigueu més.
L’ús de fórmules és relativament senzill i no depèn de la versió d’Excel que utilitzeu. Però, malauradament, és possible que encara tingueu errors.
Per exemple, si el nom complet d'una persona comença pel seu cognom, es dividirà de manera equivocada. Les fórmules també tindran problemes amb els cognoms que contenen prefixos o sufixos, com ara le Carré o van Gogh. Si el nom d'algú acaba en Jr., apareixerà com a cognom.
No obstant això, hi ha modificacions que podeu afegir per resoldre aquests problemes a mesura que apareixen. Treballar amb fórmules us proporciona la flexibilitat necessària per afrontar aquestes complexitats.
