Excel are un set de funcții TEXT care pot face minuni. Puteți face tot felul de operații de feliere și zaruri de text folosind aceste funcții.
Una dintre sarcinile obișnuite pentru persoanele care lucrează cu date text este extragerea unui sub șir în Excel (adică, obținerea psrt a textului dintr-o celulă).
Din păcate, nu există nicio funcție de șir în Excel care să poată face acest lucru cu ușurință. Cu toate acestea, acest lucru s-ar putea face în continuare folosind formule de text, precum și alte caracteristici Excel încorporate.
Să vedem mai întâi câteva dintre funcțiile de text pe care le vom folosi în acest tutorial.
Funcții Excel TEXT
Excel are o serie de funcții de text care ar face cu ușurință extragerea unui șir din textul original în Excel. Iată funcțiile Excel Text pe care le vom folosi în acest tutorial:
- Funcția DREAPTA: Extrage numărul specificat de caractere din dreapta șirului de text.
- Funcția STÂNGA: Extrage numărul specificat de caractere din stânga șirului de text.
- Funcția MID: Extrage numărul specificat de caractere din poziția inițială specificată într-un șir de text.
- Funcția FIND: găsește poziția de pornire a textului specificat în șirul de text.
- Funcția LEN: Returnează numărul de caractere din șirul de text.
Extrageți un șir în Excel folosind funcții
Să presupunem că aveți un set de date așa cum se arată mai jos:
Acestea sunt câteva coduri de e-mail aleatorii (dar super-eroi) (cu excepția mea) și, în exemplele de mai jos, vă voi arăta cum să extrageți numele de utilizator și numele de domeniu utilizând funcțiile Text din Excel.
Exemplul 1 - Extragerea numelor de utilizator din ID-urile de e-mail
În timpul utilizării funcțiilor Text, este important să identificați un model (dacă există). Acest lucru face foarte ușor să construiești o formulă. În cazul de mai sus, modelul este semnul @ dintre numele de utilizator și numele domeniului și îl vom folosi ca referință pentru a obține numele de utilizator.
Iată formula pentru a obține numele de utilizator:
= STÂNGA (A2, FIND ("@", A2) -1)
Formula de mai sus folosește funcția STÂNGA pentru a extrage numele de utilizator identificând poziția semnului @ din ID. Acest lucru se face folosind funcția FIND, care returnează poziția @.
De exemplu, în cazul [email protected], FIND („@”, A2) va returna 11, care este poziția sa în șirul de text.
Acum folosim funcția STÂNGA pentru a extrage 10 caractere din stânga șirului (cu una mai mică decât valoarea returnată de funcția STÂNGA).
Exemplul 2 - Extragerea numelui de domeniu din ID-urile de e-mail
Aceeași logică utilizată în exemplul de mai sus poate fi utilizată pentru a obține numele de domeniu. O diferență minoră aici este că trebuie să extragem caracterele din dreapta șirului de text.
Iată formula care va face acest lucru:
= DREAPTA (A2, LEN (A2) -FIND ("@", A2))
În formula de mai sus, folosim aceeași logică, dar o ajustăm pentru a ne asigura că obținem șirul corect.
Să luăm din nou exemplul [email protected]. Funcția FIND returnează poziția semnului @, care este 11 în acest caz. Acum, trebuie să extragem toate caracterele după @. Deci, identificăm lungimea totală a șirului și scădem numărul de caractere până la @. Ne oferă numărul de caractere care acoperă numele de domeniu din dreapta.
Acum putem folosi pur și simplu funcția DREAPTA pentru a obține numele domeniului.
Exemplul 3 - Extragerea numelui de domeniu din ID-urile de e-mail (fără .com)
Pentru a extrage un șir din mijlocul unui șir de text, trebuie să identificați poziția marcatorului chiar înainte și după șir.
De exemplu, în exemplul de mai jos, pentru a obține numele domeniului fără partea .com, marcatorul ar fi @ (care este chiar înainte de numele domeniului) și. (care este imediat după el).
Iată formula care va extrage numai numele domeniului:
= MID (A2, FIND ("@", A2) + 1, FIND (".", A2) -FIND ("@", A2) -1)
Funcția Excel MID extrage numărul specificat de caractere din poziția inițială specificată. În acest exemplu de mai sus, FIND („@”, A2) +1 specifică poziția inițială (care este imediat după @), iar FIND („.”, A2) -FIND („@”, A2) -1 identifică numărul de caractere între „@' si '.‘
Actualizați: Unul dintre cititorii William19 a menționat că formula de mai sus nu ar funcționa în cazul în care există un punct (.) În ID-ul de e-mail (de exemplu, [email protected]). Iată deci formula pentru a rezolva astfel de cazuri:
= MID (A1, FIND ("@", A1) + 1, FIND (".", A1, FIND ("@", A1)) - FIND ("@", A1) -1)
Utilizarea textului în coloane pentru a extrage un șir în Excel
Folosirea funcțiilor pentru extragerea unui sub șir în Excel are avantajul de a fi dinamică. Dacă modificați textul original, formula va actualiza automat rezultatele.
Dacă acest lucru este posibil să nu aveți nevoie, atunci utilizarea caracteristicii Text în coloane ar putea fi o modalitate rapidă și ușoară de a împărți textul în șiruri de caractere pe baza markerilor specificați.
Iată cum puteți face acest lucru:
- Selectați celulele în care aveți textul.
- Accesați Date -> Instrumente de date -> Text în coloane.
- În Asistentul Text către coloană Pasul 1, selectați Delimitat și apăsați Următorul.
- La pasul 2, bifați opțiunea Altele și introduceți @ în caseta din dreptul ei. Acesta va fi delimitatorul nostru pe care Excel l-ar folosi pentru a împărți textul în șiruri de caractere. Puteți vedea previzualizarea datelor mai jos. Faceți clic pe Următorul.
- La pasul 3, setarea generală funcționează bine în acest caz. Cu toate acestea, puteți alege un alt format dacă împărțiți numere / date. În mod implicit, celula de destinație este locul în care aveți datele originale. Dacă doriți să păstrați datele originale intacte, schimbați-le cu o altă celulă.
- Faceți clic pe Finalizare.
Acest lucru vă va oferi instantaneu două seturi de șiruri de caractere pentru fiecare ID de e-mail utilizat în acest exemplu.
Dacă doriți să împărțiți în continuare textul (de exemplu, împărțiți batman.com la batman și com), repetați același proces cu acesta.
Folosind FIND și REPLACE pentru a extrage text dintr-o celulă în Excel
Găsiți și ÎNLOCUIȚI poate fi o tehnică puternică atunci când lucrați cu text în Excel. În exemplele de mai jos, veți afla cum să folosiți GĂSIRE și ÎNLOCUIRE cu caractere wildcard pentru a face lucruri uimitoare în Excel.
Vezi si: Aflați totul despre caracterele wildcard în Excel.
Să luăm aceleași exemple de ID-uri de e-mail.
Exemplul 1 - Extragerea numelor de utilizator din ID-urile de e-mail
Iată pașii pentru extragerea numelor de utilizator din ID-urile de e-mail utilizând funcționalitatea Găsiți și înlocuiți:
- Copiați și lipiți datele originale. Deoarece Găsiți și înlocuiți funcționează și modifică datele pe care este aplicat, cel mai bine este să aveți o copie de rezervă a datelor originale.
- Selectați datele și accesați Acasă -> Editare -> Găsiți și selectați -> Înlocuiți (sau utilizați comanda rapidă de la tastatură Ctrl + H).
- În caseta de dialog Găsiți și înlocuiți, introduceți următoarele:
- Gaseste ce: @*
- Înlocuiți cu: (lăsați acest spațiu necompletat)
- Faceți clic pe Înlocuiți toate.
Aceasta va elimina instantaneu tot textul dinaintea @ din ID-urile de e-mail. Veți obține rezultatul așa cum se arată mai jos:
Cum funcționează asta ?? - În exemplul de mai sus, am folosit o combinație de @ și *. Un asterisc (*) este un caracter wildcard care reprezintă orice număr de caractere. Prin urmare, @ * ar însemna, un șir de text care începe cu @ și poate avea orice număr de caractere după acesta. De exemplu, în [email protected], @ * ar fi @ batman.com. Când înlocuim @ * cu gol, acesta elimină toate caracterele după @ (inclusiv @).
Exemplul 2 - Extragerea numelui de domeniu din ID-urile de e-mail
Folosind aceeași logică, puteți modifica criteriile „Găsiți ce” pentru a obține numele domeniului.
Iată pașii:
- Selectați datele.
- Accesați Acasă -> Editare -> Găsiți și selectați -> Înlocuiți (sau utilizați comanda rapidă de la tastatură Ctrl + H).
- În caseta de dialog Găsiți și înlocuiți, introduceți următoarele:
- Gaseste ce: *@
- Înlocuiți cu: (lăsați acest spațiu necompletat)
- Faceți clic pe Înlocuiți toate.
Aceasta va elimina instantaneu tot textul dinaintea @ din ID-urile de e-mail. Veți obține rezultatul așa cum se arată mai jos:
