Separați numele și prenumele în Excel (Împărțiți numele folosind formule)

Vizionați videoclipul - Împărțiți numele în Excel (în prenume, mijloc și prenume)

Excel este un instrument uimitor atunci când vine vorba de felierea și tăierea cuburilor de date text.

Există atât de multe formule utile, precum și funcționalități pe care le puteți utiliza pentru a lucra cu date text în Excel.

Una dintre întrebările foarte frecvente pe care le primesc despre manipularea datelor text este - „Cum se separă numele și prenumele (sau numele, prenumele și prenumele) în Excel?“.

Există câteva modalități ușoare de a împărți numele în Excel. Metoda pe care o alegeți va depinde de modul în care sunt structurate datele dvs. și dacă doriți ca rezultatul să fie static sau dinamic.

Deci, să începem și să vedem diferite moduri de a împărți numele în Excel.

Împărțiți numele folosind textul în coloane

Funcționalitatea text către coloane din Excel vă permite să împărțiți rapid valorile textului în celule separate la rând.

De exemplu, să presupunem că aveți setul de date așa cum se arată mai jos și doriți să separați numele și prenumele și să le obțineți în celule separate.

Mai jos sunt pașii pentru a separa numele și prenumele folosind Textul în coloane:

1. Selectați toate numele din coloană (A2: A10 în acest exemplu)
2. Faceți clic pe fila „Date”
3. În grupul „Instrumente de date”, faceți clic pe opțiunea „Text în coloane”.
4. Efectuați următoarele modificări în Expertul Conversie text în coloană:
   1. Pasul 1 din 3: Selectați Delimitat (acest lucru vă permite să utilizați spațiul ca separator) și faceți clic pe Următorul
   2. Pasul 2 din 3: Selectați opțiunea Spațiu și faceți clic pe Următorul
   3. Pasul 3 din 3: Setați B2 ca celulă de destinație (altfel va suprascrie datele existente)
5. Faceți clic pe Finalizare

Pașii de mai sus ar împărți instantaneu numele în prenume și prenume (cu prenume în coloana B și nume de familie în coloana C).

Notă: În cazul în care există deja date în celule (cele în care se așteaptă ieșirea textului în coloane), Excel vă va afișa un avertisment care vă anunță că există deja câteva date în celule. Puteți alege să suprascrieți datele sau să anulați Textul în coloane și să îl eliminați mai întâi manual.

După ce ați terminat, puteți șterge datele cu numele complet dacă doriți.

Câteva lucruri de știut atunci când utilizați textul în coloane pentru a separa numele și prenumele în Excel:

1. Rezultatul este static. Aceasta înseamnă că, în cazul în care aveți date noi sau datele originale au unele modificări, trebuie să faceți acest lucru din nou pentru a împărți numele.
2. Dacă doriți doar prenumele sau numai numele de familie, puteți sări peste coloanele pe care nu le doriți la pasul 3 al casetei de dialog „Text to Column Wizard”. Pentru aceasta, selectați coloana din previzualizarea pe care doriți să o omiteți și apoi selectați opțiunea „Nu importa coloana (săriți)”.
3. În cazul în care nu specificați celula de destinație, Textul în coloană va suprascrie coloana curentă

Opțiunea Text în coloane este cea mai potrivită atunci când aveți date consistente (de exemplu, toate numele au doar prenume sau nume sau toate numele au prenume, mijloc și prenume).

În acest exemplu, v-am arătat cum să separați numele care au spațiu ca delimitator. În cazul în care delimitatorul este o virgulă sau o combinație de virgulă și spațiu, puteți folosi aceiași pași. În acest caz, puteți specifica delimitatorul în Pasul 2 din 3 al vrăjitorului. Există deja o opțiune de a utiliza virgula ca delimitator sau puteți selecta opțiunea „Altele” și puteți specifica și un delimitator personalizat.

În timp ce Textul către coloane este un mod rapid și eficient de a împărți numele, este potrivit numai atunci când doriți ca rezultatul să fie un rezultat static. În cazul în care aveți un set de date care se poate extinde sau modifica, este mai bine să folosiți formule pentru a separa numele.

Separați numele, mijlocul și prenumele folosind formule

Formulele vă permit să tăiați și să tăiați datele textului și să extrageți ceea ce doriți.

În această secțiune, voi împărtăși diverse formule pe care le puteți utiliza pentru a separa datele de nume (pe baza modului în care sunt structurate datele dvs.).

Mai jos sunt cele trei formule pe care le puteți utiliza pentru a separa numele, mijlocul și prenumele (explicate în detaliu mai târziu în secțiunile următoare).

Formula pentru a obține prenumele:

= STÂNGA (A2, CĂUTARE ("", A2) -1)

Formula pentru a obține numele de mijloc:

= MID (A2, SEARCH ("", A2) + 1, SEARCH ("", SUBSTITUT (A2, "", "@", 1)) - SEARCH ("", A2))

Formula pentru a obține numele de familie:

= DREAPTA (A15, LEN (A15) -CĂUTARE ("@", SUBSTITUT (A15, "", "@", LEN (A15) -LEN (SUBSTITUT (A15, "", ""))))))

Obțineți prenumele

Să presupunem că aveți setul de date așa cum se arată mai jos și doriți să separați rapid prenumele într-o celulă și numele de familie într-o singură celulă.

Formula de mai jos vă va da prenumele:

= STÂNGA (A2, CĂUTARE ("", A2) -1)

Formula de mai sus folosește funcția CĂUTARE pentru a obține poziția caracterului spațial între numele și prenumele. Funcția STÂNGA folosește apoi acest număr de poziție spațiu pentru a extrage tot textul dinaintea ei.

Aceasta este o utilizare destul de simplă a extragerii unei părți din valoarea textului. Întrucât tot ce trebuie să facem este să identificăm prima poziție a spațiului de caractere, nu contează dacă numele are un prenume sau nu. Formula de mai sus va funcționa foarte bine.

Acum, să devenim puțin mai avansați cu fiecare exemplu.

Obțineți numele de familie

Să presupunem că aveți același set de date și de data aceasta trebuie să obțineți numele de familie.

Formula de mai jos va extrage numele de familie din setul de date de mai sus:

= DREAPTA (A2, LEN (A2) -CĂUTARE ("", A2))

Din nou, destul de simplu.

De data aceasta, găsim mai întâi poziția caracterului spațial, care este apoi utilizată pentru a afla numărul de caractere rămase după spațiu (care ar fi numele de familie).

Acest lucru se realizează scăzând valoarea poziției caracterului spațiu cu numărul total de caractere din nume.

Acest număr este apoi utilizat în funcția DREAPTA pentru a prelua toate aceste caractere din dreapta numelui.

Deși această formulă funcționează excelent atunci când există doar numele și prenumele, nu ar funcționa în cazul în care aveți și un prenume. Acest lucru se datorează faptului că am reprezentat un singur caracter spațial (între prenume și prenume). A avea un nume de mijloc adaugă mai multe caractere spațiale la nume.

Pentru a prelua numele de familie și când aveți un prenume, utilizați formula de mai jos:

= DREAPTA (A15, LEN (A15) -CĂUTARE ("@", SUBSTITUT (A15, "", "@", LEN (A15) -LEN (SUBSTITUT (A15, "", "")))))

Acum, acest lucru a început să devină un pic complex … nu-i așa?

Permiteți-mi să explic cum funcționează acest lucru.

Formula de mai sus găsește mai întâi numărul total de caractere spațiale din nume. Acest lucru se face obținând lungimea numelui cu și fără caracterul spațiu și apoi scăzând cel fără spațiu din cel cu spațiu. Aceasta oferă numărul total de caractere spațiale.

Funcția SUBSTITUTE este apoi utilizată pentru a înlocui ultimul caracter spațial cu un simbol „@” (puteți utiliza orice simbol - ceva care este puțin probabil să apară ca parte a numelui).

Odată ce simbolul @ a fost înlocuit în locul ultimului caracter spațial, puteți găsi cu ușurință poziția acestui simbol @. Acest lucru se face folosind funcția SEARCH.

Acum tot ce trebuie să faceți este să extrageți toate caracterele din dreapta acestui simbol @. Acest lucru se face folosind funcția DREAPTA.

Obțineți numele de mijloc

Să presupunem că aveți setul de date așa cum se arată mai jos și doriți să extrageți numele de mijloc.

Următoarea formulă va face acest lucru:

= MID (A2, SEARCH ("", A2) + 1, SEARCH ("", SUBSTITUT (A2, "", "@", 1)) - SEARCH ("", A2))

Formula de mai sus folosește funcția MID, care vă permite să specificați o poziție de început și numărul de caractere de extras din acea poziție.

Poziția inițială este ușor de găsit folosind funcția CĂUTARE.

Partea dificilă este să găsiți câte caractere să extrageți după această poziție de început. Pentru a obține acest lucru, trebuie să identificați câte caractere există de la poziția de început până la ultimul caracter spațial.

Acest lucru se poate face folosind funcția SUBSTITUTE și înlocuiți ultimul caracter spațial cu un simbol „@”. Odată ce ați făcut acest lucru, puteți utiliza cu ușurință funcția CĂUTARE pentru a găsi poziția acestui ultim caracter spațial.

Acum că aveți poziția de pornire și poziția ultimului spațiu, puteți prelua cu ușurință numele de mijloc folosind funcția MID.

Unul dintre avantajele utilizării unei formule pentru a separa numele este că rezultatul este dinamic. Deci, în cazul în care setul de date se extinde și se adaugă mai multe nume sau dacă unele nume se schimbă în setul de date original, nu trebuie să vă faceți griji cu privire la datele rezultate.

Separați numele folosind Găsiți și înlocuiți

Îmi place flexibilitatea care vine cu „Găsiți și înlocuiți” - pentru că puteți folosi caractere wild card în ea.

Permiteți-mi să vă explic mai întâi ce este un caracter wild card.

Un caracter wildcard este ceva pe care îl puteți utiliza în locul oricărui text. De exemplu, puteți utiliza un simbol asterisc (*) și va reprezenta orice număr de caractere în Excel. Pentru a vă oferi un exemplu, dacă vreau să găsesc toate numele care încep cu alfabetul A, pot folosi A * în căutare și înlocuire. Aceasta va găsi și selecta toate celulele în care numele începe cu A.

Dacă tot nu ești clar, nu-ți face griji. Continuați să citiți, iar următoarele exemple vă vor arăta clar ce sunt caracterele wildcard și cum să le utilizați pentru a separa rapid numele (sau orice valori de text din Excel).

În toate exemplele acoperite mai jos, asigurați-vă că creați o copie de rezervă a setului de date. Găsiți și înlocuiți modifică datele pe care sunt utilizate. Cel mai bine este să copiați și să inserați datele mai întâi, apoi să utilizați Găsiți și înlocuiți pe setul de date copiat.

Obțineți prenumele

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să obțineți doar prenumele.

Mai jos sunt pașii pentru a face acest lucru:

1. Copiați datele despre nume în coloana A și lipiți-le în coloana B.
2. Cu datele din coloana B selectate, faceți clic pe fila Acasă
3. În grupul Editare, faceți clic pe Găsiți și selectați.
4. Faceți clic pe Înlocuire. Aceasta va deschide caseta de dialog „Găsiți și înlocuiți”.
5. În caseta de dialog „Găsiți și înlocuiți”, introduceți următoarele
   1. Găsiți ce: * (caracter spațial urmat de simbolul asterisc)
   2. Înlocuiți cu: lăsați acest text necompletat
6. Faceți clic pe Înlocuiți toate.

Pașii de mai sus vă vor oferi prenumele și vor elimina totul după prenume.

Acest lucru funcționează chiar dacă aveți nume care au un prenume.

Sfat Pro: Comanda rapidă de la tastatură pentru a deschide caseta de dialog Căutare și înlocuire este Control + H (țineți apăsată tasta de control și apoi apăsați tasta H).

Obțineți numele de familie

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să obțineți doar numele de familie.

Mai jos sunt pașii pentru a face acest lucru:

1. Copiați datele despre nume în coloana A și lipiți-le în coloana B.
2. Cu datele din coloana B selectate, faceți clic pe fila Acasă
3. În grupul Editare, faceți clic pe Găsiți și selectați.
4. Faceți clic pe Înlocuire. Aceasta va deschide caseta de dialog „Găsiți și înlocuiți”.
5. În caseta de dialog „Găsiți și înlocuiți”, introduceți următoarele
   1. Găsiți ce: * (simbol asterisc urmat de un caracter spațial)
   2. Înlocuiți cu: lăsați acest text necompletat
6. Faceți clic pe Înlocuiți toate.

Pașii de mai sus vă vor oferi numele de familie și ar elimina totul înainte de prenume.

Acest lucru funcționează chiar dacă aveți nume care au un prenume.

Eliminați prenumele

În cazul în care doriți doar să scăpați de numele de mijloc și să aveți doar numele și prenumele, puteți face acest lucru folosind Găsiți și înlocuiți.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să eliminați numele de mijloc din acestea.

Mai jos sunt pașii pentru a face acest lucru:

1. Copiați datele despre nume în coloana A și lipiți-le în coloana B.
2. Cu datele din Coloana B selectate, faceți clic pe fila Acasă
3. În grupul Editare, faceți clic pe Găsiți și selectați.
4. Faceți clic pe Înlocuire. Aceasta va deschide caseta de dialog „Găsiți și înlocuiți”.
5. În caseta de dialog „Găsiți și înlocuiți”, introduceți următoarele
   1. Găsiți ce: * (caracter spațial urmat de simbolul asteriscului urmat de caracterul spațial)
   2. Înlocuiți cu: (puneți doar un caracter spațial aici)
6. Faceți clic pe Înlocuiți toate.

Pașii de mai sus ar elimina numele de mijloc dintr-un nume complet. În cazul în care unele nume nu au nici un prenume, acestea nu vor fi modificate.

Separați numele folosind Flash Fill

Completarea Flash a fost introdusă în Excel 2013 și facilitează modificarea sau curățarea unui set de date text.

Și când vine vorba de separarea datelor despre nume, se află chiar pe aleea Flash Fill.

Cel mai important lucru pe care trebuie să-l știți atunci când utilizați Flash Fill este că este nevoie de un model pe care flash-ul să îl poată identifica. Odată ce a identificat modelul, vă va ajuta cu ușurință să împărțiți numele în Excel (veți obține mai multă claritate în acest sens atunci când parcurgeți câteva exemple de mai jos).

Obțineți prenumele sau prenumele din numele complet

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să obțineți doar prenumele.

1. În celula alăturată, tastați manual prenumele din numele complet. În acest exemplu, aș tasta Rick.
2. În a doua celulă, tastați manual prenumele din numele celulei adiacente. În timp ce tastați, Flash Fill vă va afișa automat lista cu prenumele (în gri).
3. Când vedeți numele în gri, aruncați o privire rapidă pentru a vă asigura că afișează numele potrivite. Dacă acestea sunt corecte, apăsați tasta Enter și Flash Fill va umple automat restul celulelor cu prenumele.

Flash Fill are nevoie de dvs. pentru a-i oferi un model pe care îl poate urma atunci când vă oferă datele modificate. În exemplul nostru, atunci când introduceți prenumele în prima celulă, Flash Fill nu își poate da seama de model.

Dar de îndată ce începeți să introduceți prenumele în a doua celulă, Flash Fill înțelege modelul și vă arată o sugestie. Dacă sugestia este corectă, apăsați tasta Enter.

Și dacă nu este corect, puteți încerca să introduceți manual în alte câteva celule și să verificați dacă Flash Fill este capabil să discearnă modelul sau nu.

Uneori, este posibil să nu vedeți modelul în gri (așa cum se arată în pasul 2 de mai sus). Dacă acesta este cazul, urmați pașii de mai jos pentru a obține rezultatul Flash Fill:

1. Introduceți textul manual în două celule.
2. Selectați ambele aceste celule
3. Plasați cursorul în partea din dreapta jos a selecției. Veți observa că cursorul se transformă într-o pictogramă plus
4. Faceți dublu clic pe el (tasta stânga a mouse-ului). Aceasta va umple toate celulele. În acest moment, rezultatele sunt probabil incorecte și nu sunt cele pe care le așteptați.
5. În partea dreaptă jos a datelor rezultate, veți vedea o mică pictogramă de completare automată. Faceți clic pe această pictogramă de completare automată
6. Faceți clic pe Flash Fill

Pașii de mai sus vă vor oferi rezultatul din Flash Fill (pe baza modelului pe care l-a dedus).

De asemenea, puteți utiliza Flash Fill pentru a obține numele de familie sau prenumele. În primele două celule, introduceți numele de familie (sau prenumele) și umplerea cu bliț va putea înțelege modelul

Rearanjați numele folosind Flash Fill

Flash Fill este un instrument inteligent și poate descifra și modelul ușor complex

De exemplu, să presupunem că aveți un set de date așa cum se arată mai jos și doriți să rearanjați numele de la Rick Novak la Novak, Rick (unde numele de familie vine mai întâi urmat de o virgulă și apoi prenumele).

Mai jos sunt pașii pentru a face acest lucru:

1. În celula alăturată, tastați manual Novak, Rick
2. În a doua celulă, tastați manual Connor, Susan. În timp ce tastați, Flash Fill vă va afișa o listă cu numele în același format (în gri).
3. Când vedeți numele în gri, aruncați o privire rapidă pentru a vă asigura că afișează numele potrivite. Dacă acestea sunt corecte, apăsați tasta Enter și Flash Fill va umple automat restul celulelor cu numele în același format.

Eliminați prenumele (sau doar obțineți prenumele)

De asemenea, puteți utiliza Flash Fill pentru a scăpa de numele de mijloc sau pentru a obține doar numele de mijloc.

De exemplu, să presupunem că aveți un set de date așa cum se arată mai jos și doriți să obțineți doar numele și prenumele și nu numele de mijloc.

Mai jos sunt pașii pentru a face acest lucru:

1. În celula alăturată, tastați manual Rick Novak
2. În a doua celulă, tastați manual Susan Connor. În timp ce tastați, Flash Fill vă va afișa o listă cu numele în același format (în gri).
3. Când vedeți numele în gri, aruncați o privire rapidă pentru a vă asigura că afișează numele potrivite. Dacă acestea sunt corecte, apăsați tasta Enter și Flash Fill va umple automat restul de celule cu numele fără numele de mijloc.

În mod similar, dacă doriți doar să obțineți numele de mijloc, tastați numele de mijloc în primele două celule și utilizați Flash Fill pentru a obține numele de mijloc din toate numele rămase.

Exemplele prezentate în acest tutorial utilizează nume în timp ce manipulează datele textului. Puteți utiliza aceleași concepte pentru a lucra și cu alte formate de date (cum ar fi adrese, nume de produse etc.)